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

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
Asked:
Computer Guy
1 Solution
 
Rgonzo1971Commented:
Hi

you could try this formula

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

Open in new window


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

Open in new window


Regads
0
 
Shaun KlineLead Software EngineerCommented:
You can use the FIND method wrapped in an ISERROR method, something like:
=IFERROR(LEFT(A1, FIND(",", A1)), A1)
0
 
MacroShadowCommented:
=SUBSTITUTE(A1,",","")
And drag down as far as necessary.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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

But how do I make Morrison, Van into Van Morrison
0
 
MacroShadowCommented:
Sorry missed that part.
=SUBSTITUTE(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)),",","")
0
 
Computer GuyAuthor 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
 
MacroShadowCommented:
Go with Rgonzo1971's solution.

If you're interested I can give you a VBA solution.
0
 
Computer GuyAuthor Commented:
Sure.
0
 
MacroShadowCommented:
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

Open in new window


And use it like this:
=ReverseString(A1)

Open in new window

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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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