Help requested with text parsing

  • Thread starter Thread starter Paul Hyett
  • Start date Start date
P

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.
 
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
 
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.
Click to expand...

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.
 
The only time it fails is if there's a "-" in the song title before the
" - " separating song & artist.
Click to expand...

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
 
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.
Click to expand...

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))
Click to expand...

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

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
 
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.
Click to expand...

Ah, thanks.
 
Back
Top