Computer Guy
asked on
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
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
You can use the FIND method wrapped in an ISERROR method, something like:
=IFERROR(LEFT(A1, FIND(",", A1)), A1)
=IFERROR(LEFT(A1, FIND(",", A1)), A1)
=SUBSTITUTE(A1,",","")
And drag down as far as necessary.
And drag down as far as necessary.
ASKER
I like this: =SUBSTITUTE(A1,",","")
But how do I make Morrison, Van into Van Morrison
But how do I make Morrison, Van into Van Morrison
Sorry missed that part.
=SUBSTITUTE(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)),",","")
=SUBSTITUTE(MID(A1&" "&A1,FIND(" ",A1)+1,LEN(A1)),",","")
ASKER
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?
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?
Go with Rgonzo1971's solution.
If you're interested I can give you a VBA solution.
If you're interested I can give you a VBA solution.
ASKER
Sure.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could try this formula
Open in new window
or XL 2007 and further
Open in new window
Regads