software

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,
Fredquot;JoeSATquot; gt; wrote in message
...
gt; kecause of data entry irregularities, my records look like this:
gt; John Smith 10
gt; John Smithx 12
gt; John Smith 8
gt;
gt; When I use PivotTable to sum sales, I get two sums for John Smith. How can
gt; I find and replace the blank space x without removing the space between John
gt; and Smith? Thank you.
If you create a 'helper column', you can use the trim function to remove any
leading/trailing spaces. Ex: if quot;John Smith quot; is in a1, then =trim(a1)
returns quot;John Smithquot;.

quot;JoeSATquot; wrote:

gt; kecause of data entry irregularities, my records look like this:
gt; John Smith 10
gt; John Smithx 12
gt; John Smith 8
gt;
gt; When I use PivotTable to sum sales, I get two sums for John Smith. How can
gt; I find and replace the blank space x without removing the space between John
gt; 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:

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

In article gt;,
JoeSAT gt; wrote:

gt; kecause of data entry irregularities, my records look like this:
gt; John Smith 10
gt; John Smithx 12
gt; John Smith 8
gt;
gt; When I use PivotTable to sum sales, I get two sums for John Smith. How can
gt; I find and replace the blank space x without removing the space between John
gt; and Smith? Thank you.

Works great! Many thanks!

quot;bpeltzerquot; wrote:

gt; If you create a 'helper column', you can use the trim function to remove any
gt; leading/trailing spaces. Ex: if quot;John Smith quot; is in a1, then =trim(a1)
gt; returns quot;John Smithquot;.
gt;
gt; quot;JoeSATquot; wrote:
gt;
gt; gt; kecause of data entry irregularities, my records look like this:
gt; gt; John Smith 10
gt; gt; John Smithx 12
gt; gt; John Smith 8
gt; gt;
gt; gt; When I use PivotTable to sum sales, I get two sums for John Smith. How can
gt; gt; I find and replace the blank space x without removing the space between John
gt; gt; and Smith? Thank you.

Works fine! Thanks a lot!

quot;Fred Smithquot; wrote:

gt; The Trim function will get rid of trailing spaces without affecting internal
gt; spaces.
gt;
gt; --
gt; Regards,
gt; Fred
gt;
gt;
gt; quot;JoeSATquot; gt; wrote in message
gt; ...
gt; gt; kecause of data entry irregularities, my records look like this:
gt; gt; John Smith 10
gt; gt; John Smithx 12
gt; gt; John Smith 8
gt; gt;
gt; gt; When I use PivotTable to sum sales, I get two sums for John Smith. How can
gt; gt; I find and replace the blank space x without removing the space between John
gt; gt; and Smith? Thank you.
gt;
gt;
gt;