Remove Spaces Before a Comma Only

[lucolem]

Hello all,
I'm looking for a formula to remove a space, if it exists, before a comma. For instance, I need "Golden Retriever,Dog" to be "GoldenRetriever,Dog". I need it to only remove the space before the comma because there will be spaces after the comma that need to stay there. And there won't always be a space; don't know if that makes a difference.

Any ideas? Thanks!

 

[VoG]

Try

=SUBSTITUTE(A1," ,",",")

 

[nd1marcello]

If you can't use a formula, press ctrl+f, select replace. In "Find What", type: , (make sure you put a space before the comma). In "Replace With", just type: , (no space before the comma).

 

[anglais428]

Try =SUBSTITUTE(LEFT(A1,FIND(",",A1))," ","")&(RIGHT(A1,LEN(A1)-FIND(",",A1)))

Where A1 is the cell the text is in

 

[lucolem]

Thank you for your quick responses! The longer =substitute formula worked perfectly. Thanks again!

Try =SUBSTITUTE(LEFT(A1,FIND(",",A1))," ","")&(RIGHT(A1,LEN(A1)-FIND(",",A1)))

Where A1 is the cell the text is in

 

[Scott Huish]

Why not substitute just the 1st occurance?

=SUBSTITUTE(A1," ,",",",1)

 

[lucolem]

I'm not sure, but that one didn't seem to work for me.

Why not substitute just the 1st occurance?

=SUBSTITUTE(A1," ,",",",1)

 

[Rick Rothstein]

I'm not sure, but that one didn't seem to work for me.

I am curious... in what way did it not seem to work for you. Given your description of your data, the formula works fine for me in my tests... and it is a heck of a lot more efficient to use a formula with only one function call than it is to use one with five function calls.

 

[lucolem]

Well, I'm guessing if all the spaces were right before the comma, it would have worked. But I used the Find & Replace for those and was looking only for a formula to remove spaces from the double names before the comma. In my data, the space wasn't beside the comma - I needed "Golden Retriever,Dog" to be "GoldenRetriever,Dog" so the GoldenRetriever was one word, not 2. I'm sorry if I didn't express what I needed correctly!

I am curious... in what way did it not seem to work for you. Given your description of your data, the formula works fine for me in my tests... and it is a heck of a lot more efficient to use a formula with only one function call than it is to use one with five function calls.

 

[Rick Rothstein]

Well, I'm guessing if all the spaces were right before the comma, it would have worked. But I used the Find & Replace for those and was looking only for a formula to remove spaces from the double names before the comma. In my data, the space wasn't beside the comma - I needed "Golden Retriever,Dog" to be "GoldenRetriever,Dog" so the GoldenRetriever was one word, not 2. I'm sorry if I didn't express what I needed correctly!

Oh, the space before the comma, but not next to the comma. Then try just changing the thing being substituted from a space-comma to just a space and replace it with the empty string instead of a comma in HOTPEPPER's formula...

=SUBSTITUTE(A1," ","")