how to remove spaces before commas

[bulletproof]

hi guys.

sorry for being a newb but would any of you
would be kind enough to show me a formula for:

what I have:

last name , first name
last name, first name
last name , first name

what i need to do:

last name, first name
last name, first name
last name, first name


as you can see, some of those last names have spaces before the commas. I need to get those spaces out.
I understand that the TRIM function only deals with the spaces before
and after and the trailing ones.

I tried to search for it, though not quite successful in my chosen keywords.

I'm just an old dog trying to learn new tricks from excel.

thanks much.

 

[VoG]

Try

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

 

[Andrew Poulsom]

You can use:

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

or Find spacecomma and Replace with comma.

 

[Richard Schollar]

In case there may be more than one space preceding the comma you could use the following:

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

 

[bulletproof]

In case there may be more than one space preceding the comma you could use the following:

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

I tried this and it works! HAHA!!!

thanks Richard, Vog and Andrew!

you guys are lifesavers!


additional question though:

how should the function look like if i need to
put in a space after the comma, like:

last name,first name to last name, first name?

 

[Andrew Poulsom]

That would be:

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

 

[bulletproof]

That would be:

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

HMMM...

it didn't quite do what I was expecting.
almost same result with:
=SUBSTITUTE(TRIM(A1)," ,",",")

here's my dilemma:

i have data coming from 2 sources with no common identifier
to begin with.

i have to rely on the names since both sources uses last name-first name
arrangement.
one source i cannot/i'm not allowed to edit/change.
the other, i can edit.

the data source i can edit is cumbersome.
some layout of the names are:

last name , first name
last name,first name
lastnamefirstname
last name ,first name
last name ,first name
last name, first name

when all i need them to be is:

last name, first name

is this possible?
or can one or combined functions do this?

thanks again for all your help, andrew ray:

sorry for not explaining clearly on my first post.
I should have explained it.
I was thinking I could do the rest without bothering you guys here
but it's taking off a chunk of my time already, really must be getting old...

 

[Andrew Poulsom]

This works:

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

for all but lastnamefirstname.

 

[bulletproof]

^

NICE!

thanks Andrew.

any thoughts on how to fix that

last namefirst name
to
last name, first name

even if i use a function just to do it on a separate column?

thanks again ray:

 

[VoG]

"dale,paul"

How does Excel work out which is the first and second name?