• Status: Solved
• Priority: Medium
• Security: Public
• Views: 233

# Excel Remove Comma

Hi,

Some cells have a one name artist such as Journey, where others have Clapton, Eric (Row A)

How can I do an =(A1code) that I can run on Row B (even single named artists) to make it

Journey              Journey
Clapton, Eric     Eric Clapton
0
Computer Guy
1 Solution

Commented:
Hi

you could try this formula

``````=IF(ISERROR(SEARCH(",",A1)),A1,MID(A1,SEARCH(",",A1)+2,LEN(A1))&" "&LEFT(A1,SEARCH(",",A1)-1))
``````

or XL 2007 and further
``````=IFERROR(MID(A1,SEARCH(",",A1)+2,LEN(A1))&" "&LEFT(A1;SEARCH(",",A1)-1),A1)
``````

0

You can use the FIND method wrapped in an ISERROR method, something like:
=IFERROR(LEFT(A1, FIND(",", A1)), A1)
0

Commented:
=SUBSTITUTE(A1,",","")
And drag down as far as necessary.
0

Author Commented:
I like this: =SUBSTITUTE(A1,",","")

But how do I make Morrison, Van into Van Morrison
0

Commented:
Sorry missed that part.
=SUBSTITUTE(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)),",","")
0

Author Commented:
If it is a one word band like Journey, it shows up with #VALUE!

If it is a 3 word band like Black Eyed Peas, it shows up as Eyed Peas Black

Is there a way to ignore those kinds?
0

Commented:
Go with Rgonzo1971's solution.

If you're interested I can give you a VBA solution.
0

Author Commented:
Sure.
0

Commented:
If you're interested if a vba solution, try this. Copy the code into a standard module.

``````Function ReverseString(rngString As Range, Optional strDelim As String = ",") As String
Dim vStrings As Variant, lngString As Long
vStrings = Split(rngString, strDelim)
For lngString = UBound(vStrings) To LBound(vStrings) Step -1
ReverseString = ReverseString & strDelim & vStrings(lngString)
Next lngString
ReverseString = Replace(Replace(ReverseString, strDelim, " ", 1, 1), ",", "")
End Function
``````

And use it like this:
``````=ReverseString(A1)
``````
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.