Help requested with text parsing

[Paul Hyett]

I have example data as follows (the current top 3 singles on iTunes, as
it happens) :

1. Dirtee Disco (Radio Edit) - Dizzee Rascal
2. Nothin' On You - B.o.B
3. Solo - Iyaz

What I want to do is split it across three columns, dividing it after
the chart position, and the song title.

I know I could do it via text-to-columns, but I'm hoping for something a
little more automated if possible.

TIA.

 

[Pete_UK]

With your data in column A starting at A1, you can put these formulae
in the cells stated:

B1: =LEFT(A1,FIND(".",A1)-1)

C1: =MID(A1,FIND(".",A1)+2,FIND("-",A1)-FIND(".",A1)-3)

D1: =RIGHT(A1,LEN(A1)-FIND("-",A1)-1)

Then copy down as required to give you this:

1 Dirtee Disco (Radio Edit) Dizzee Rascal
2 Nothin' On You B.o.B
3 Solo Iyaz

(the columns might not be quite aligned here)

Hope this helps.

Pete

 

[Paul Hyett]

With your data in column A starting at A1, you can put these formulae
in the cells stated:

B1: =LEFT(A1,FIND(".",A1)-1)

C1: =MID(A1,FIND(".",A1)+2,FIND("-",A1)-FIND(".",A1)-3)

D1: =RIGHT(A1,LEN(A1)-FIND("-",A1)-1)

Then copy down as required to give you this:

1 Dirtee Disco (Radio Edit) Dizzee Rascal
2 Nothin' On You B.o.B
3 Solo Iyaz

(the columns might not be quite aligned here)

Hope this helps.

Thank you - that's almost perfect for what I wanted!

The only time it fails is if there's a "-" in the song title before the
" - " separating song & artist.

 

[Ron Rosenfeld]

The only time it fails is if there's a "-" in the song title before the
" - " separating song & artist.

What rule would you use to differentiate a dash in the song title from a dash
separator or a dash in the artist's name?

It seems to me that a dash in the artist's name would not have spaces around
it, so we could use a dash surrounded by spaces as the key, and test that this
is the *last* " - " pattern in the string for the separator.

That being the case, try these formulas:

B1: =LEFT(A1,FIND(".",A1)-1)
C1:
=MID(A1,FIND(".",A1)+1,FIND(CHAR(1),SUBSTITUTE(
A1," - ",CHAR(1),(LEN(A1)-LEN(SUBSTITUTE(
A1," - ","")))/LEN(" - ")))-FIND(".",A1)-1)

D1: =TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",99)),99))

--ron

 

[Paul Hyett]

What rule would you use to differentiate a dash in the song title from a dash
separator or a dash in the artist's name?

It seems to me that a dash in the artist's name would not have spaces around
it, so we could use a dash surrounded by spaces as the key, and test that this
is the *last* " - " pattern in the string for the separator.

That's what I had in mind.

That being the case, try these formulas:

B1: =LEFT(A1,FIND(".",A1)-1)
C1:
=MID(A1,FIND(".",A1)+1,FIND(CHAR(1),SUBSTITUTE(
A1," - ",CHAR(1),(LEN(A1)-LEN(SUBSTITUTE(
A1," - ","")))/LEN(" - ")))-FIND(".",A1)-1)

D1: =TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",99)),99))

That's absolutely brilliant - seems to work perfectly (though I can't
begin to pretend I know how) - thank you!!

 

[Ron Rosenfeld]

That's absolutely brilliant - seems to work perfectly (though I can't
begin to pretend I know how) - thank you!!

You're welcome. Glad to help. Thanks for the feedback.

The formula evaluator tool can often help in figuring out how these complicated
formulas work.

But the method for formula 2 is to look for the last instance of our
<space-hyphen-space> separator and replace that with something unique (I use
CHAR(1)) that the FIND function can then look for.

We then use that to determine end point, or starting point of the strings
around it.
--ron

 

[Paul Hyett]

You're welcome. Glad to help. Thanks for the feedback.

The formula evaluator tool can often help in figuring out how these complicated
formulas work.

But the method for formula 2 is to look for the last instance of our
<space-hyphen-space> separator and replace that with something unique (I use
CHAR(1)) that the FIND function can then look for.

We then use that to determine end point, or starting point of the strings
around it.

Ah, thanks.