Excel - inserting a space after a comma in string

[smooveb]

Hi,

you guys really helped me with a post I wrote up this morning, this one works on the same problem, which basically comes down to comparing similar names in different types of strings

So I have two strings for a given name, one without a space after the comma, the other with a space. I need them to be identical for sorting purposes.

SRINI, BARNEY
SRINI,BARNEY

Any thoughts on the easiest action, whether space insertion in one string, or removal in the other? Thanks!

Barrett

 

[SkipVought]

Hi,
The easiest to find and fix would be ", ".
Replace every ", " with ",".

I would run that several times to make sure that every comma/space was replaced by comma, leaving no space.

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[DaleWatson123321]

Barrett,

My 2 cents worth...

1) Highlight the range of data

2) Use &quot;Search & Replace&quot; - hold down <Control> and hit H

3) Under &quot;Find what&quot;, type &quot;, &quot;

4) Under &quot;Replace with&quot;, type &quot;,&quot;

5) Choose &quot;Replace All&quot;

6) Repeat steps 1) and 2)

7) Under &quot;Find what&quot;, type &quot;,&quot;

8) Under &quot;Replace with&quot;, type &quot;, &quot;

9) Choose &quot;Replace All&quot;

I believe this option is preferable, because it will leave you with a space after the comma - which is normally considered a better means of displaying names.

Plus, if you might somehow end up using a variation of the formula which I contributed in your earlier posting, having the space will be important as my formula looks for the &quot;space&quot; character.

By the way, which solution DID you use for your earlier posting. It is always a good practice to &quot;update&quot; all postings with an explanation of your &quot;chosen&quot; solution, as the contributors are curious to know which one &quot;came in first&quot;.

Another suggestion: take note of the &quot;Click here to mark this post as a helpful or expert post! located at the bottom-left-corner of the contributor's screen. This is a useful way of &quot;recognizing&quot; and properly thanking a contributor. It also serves to advise others (at a glance) that a solution has been provided. And, it serves as a &quot;beacon&quot; to Tek-Tips &quot;browsers&quot; who are &quot;on the lookout&quot; for useful tips and solutions.


Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[ETID]

OR YET ANOTHER WAY...

IF YOUR NAME IS IN CELL a1 THEN IN B1 TYPE:

=SUBSTITUTE(A1,&quot;, &quot;,&quot;,&quot

 

[ETID]

OR...IF YOU NEED TO ADD A SPACE


IF YOUR NAME IS IN CELL a1 THEN IN B1 TYPE:

=SUBSTITUTE(A1,&quot;,&quot;,&quot;, &quot