How to find and replace blank space (x) in John Smithx in Excel?
 

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.

How to find and replace blank space (x) in John Smithx in Excel?
 

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.

How to find and replace blank space (x) in John Smithx in Excel?
 

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.

How to find and replace blank space (x) in John Smithx in Excel?
 

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.

How to find and replace blank space (x) in John Smithx in Exce
 

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.

How to find and replace blank space (x) in John Smithx in Exce
 

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.