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

[JoeSAT]

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.

 

[Fred Smith]

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

 

[bpeltzer]

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".

 

[JE McGimpsey]

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

 

[JoeSAT]

Works great! Many thanks!

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]

Works fine! Thanks a lot!

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