Solved

# Excel Remove Comma

Posted on 2014-01-13
200 Views
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
Question by:Computer Guy

LVL 48

Expert Comment

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

LVL 25

Expert Comment

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

LVL 26

Expert Comment

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

LVL 3

Author Comment

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

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

LVL 26

Expert Comment

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

LVL 3

Author Comment

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

LVL 26

Expert Comment

Go with Rgonzo1971's solution.

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

LVL 3

Author Comment

Sure.
0

LVL 26

Accepted Solution

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

## Featured Post

### Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…