kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

The Trim function will get rid of trailing spaces without affecting internal
spaces.

--
Regards,
Fred


"JoeSAT" wrote in message
...
kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

If you create a 'helper column', you can use the trim function to remove any
leading/trailing spaces. Ex: if "John Smith " is in a1, then =trim(a1)
returns "John Smith".

"JoeSAT" wrote:

kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

You can use TRIM() to remove leading and trailing spaces (and multiple
spaces between words. To do it in place, use David McRitchie's TrimALL
macro:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

In article ,
JoeSAT wrote:

kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

Works great! Many thanks!

"bpeltzer" wrote:

If you create a 'helper column', you can use the trim function to remove any
leading/trailing spaces. Ex: if "John Smith " is in a1, then =trim(a1)
returns "John Smith".

"JoeSAT" wrote:

kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.

Works fine! Thanks a lot!

"Fred Smith" wrote:

The Trim function will get rid of trailing spaces without affecting internal
spaces.

--
Regards,
Fred


"JoeSAT" wrote in message
...
kecause of data entry irregularities, my records look like this:
John Smith 10
John Smithx 12
John Smith 8

When I use PivotTable to sum sales, I get two sums for John Smith. How can
I find and replace the blank space x without removing the space between John
and Smith? Thank you.