How can I separate cell contents at a character?

[JOSHSKORN]

I have a song list that I copied and pasted from a website that looks like this:
Song Title - Artist
So, there's a dash that separates the two. Lets say I have this in Cell A2 (headings in row 1) and in Cells B2 and C2, I want "Song Title" and "Artists", respectively. How can I achieve this?

I would be comfortable using VBA if I have to.

I know I'd have to find the position of the Dash, read before, then after it, then trim the space before and after the dash. I'm just not sure how to do that.

 

[RoryA]

You could use:

=TRIM(LEFT(A2,FIND("-",A2)-1))

in B2 and

=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

in C2

 

[JOSHSKORN]

You could use:

=TRIM(LEFT(A2,FIND("-",A2)-1))

in B2 and

=TRIM(MID(A2,FIND("-",A2)+1,LEN(A2)))

in C2

Both of these return #Value in B2/C2. In A2, I have: (Don’t Fear) The Reaper – Blue Oyster Cult

Then I noticed the longer dash in A2 than what's in the formulas provided, I tried swapping the short for the longer dash, no luck.

 

[Alex Blakenburg]

You said you tried this but just in case there was a typo try what I have here in B & C.
If that doesn't work tell us what the formulas in Columns D & E return (these will only work on that exact phrase though since they are looking for Blue.

Book3
	A	B	C	D	E
1	Description	Part 1	Part 2	ASCII	Unicode
2	(Don’t Fear) The Reaper – Blue Oyster Cult	(Don’t Fear) The Reaper	Blue Oyster Cult	150	8211
Sheet1

Cell Formulas
Range	Formula
B2	B2	=TRIM(LEFT(A2,FIND("–",A2)-1))
C2	C2	=TRIM(MID(A2,FIND("–",A2)+1,LEN(A2)))
D2	D2	=CODE(MID(A2,FIND(" Blue",A2)-1,1))
E2	E2	=UNICODE(MID(A2,FIND(" Blue",A2)-1,1))

 

[Cubist]

The hyphen in the formula provided by @RoryA is CHAR(45) whereas the OP's is CHAR(208). Try replacing that in the formula and see if that works.

=TRIM(LEFT(A2,FIND(CHAR(208),A2)-1))

=TRIM(MID(A2,FIND(CHAR(208),A2)+1,LEN(A2)))

 

[Alex Blakenburg]

Hi @Cubist, that seems unlikely

ASCII Code 208

ASCII code 208 is included in the extended ASCII codes which means that it is included in many different character sets.

www.ascii-code.com

My XL2BB shows how I arrived at the code for it after copying in the text provided.

PS: ascii 150 is En-Dash which is a fairly well known dash in publishing and En is a useful word when playing scrabble

 

[Cubist]

@Alex Blakenburg I copied your XL2BB and got a different result. I learned from another post the first 128 characters of CODE() and UNICODE() use the same encoding, but the rest of the ASCII character set uses a different encoding. UNICODE is prevalent these days.

Book2
	A	B	C	D	E
1	Description	Part 1	Part 2	ASCII	Unicode
2	(Don’t Fear) The Reaper – Blue Oyster Cult	(Don’t Fear) The Reaper	Blue Oyster Cult	208	8211
Sheet10

Cell Formulas
Range	Formula
B2	B2	=TRIM(LEFT(A2,FIND("–",A2)-1))
C2	C2	=TRIM(MID(A2,FIND("–",A2)+1,LEN(A2)))
D2	D2	=CODE(MID(A2,FIND(" Blue",A2)-1,1))
E2	E2	=UNICODE(MID(A2,FIND(" Blue",A2)-1,1))

 

[Alex Blakenburg]

I knew I left the Unicode there for a reason .
We agree on the Unicode, and 208 seems to be vary based on the character set you are using so I would go with the Unicode version. Mind you just copying in the dash seems to have worked on both your and my machine.
(What language / character set are you using ?)

 

[Cubist]

I pasted in only the dash and the same result for me. I'm using US-ASCII.

Book2
	A	B	C	D	E
1	Description	Part 1	Part 2	ASCII	Unicode
2	–			208	8211
Sheet10

Cell Formulas
Range	Formula
D2	D2	=CODE(A2)
E2	E2	=UNICODE(A2)

 

[Cubist]

@Alex Blakenburg I realized I was on my Mac virtual machine instead of Windows, which explains the discrepancy between the CODE().