How To Extract Text Before Or After Dash From Cells?

[fidelito101]

Hello everybody!
I need some help from you, I have a list of (Song - Artist - Album) in A Column and I would like to put the artist in Column B, the song title in column C and the album title in D.

Thanks in advance

A	B	C	D
The Coasters - Charlie Brown - Poison Ivy	The Coasters	Charlie Brown	Poison Ivy
The Impalas - Sorry (I Ran All the Way Home) - Brand New Cadillac: Rock & Roll	The Impalas	Sorry (I Ran All the Way Home)	Brand New Cadillac: Rock & Roll

 

[mikerickson]

Use TextToColumns. On the last screen of the dialog has a WriteToLocation entry, so you don't have to overwrite the original data.

(BTW, do you file it as Howlin' Wolf or Wolf, Howlin" )

 

[fidelito101]

Use TextToColumns. On the last screen of the dialog has a WriteToLocation entry, so you don't have to overwrite the original data.

(BTW, do you file it as Howlin' Wolf or Wolf, Howlin" )

Thank you very much! I had tried with formulas, but does not work

 

[fidelito101]

SOLVED

 

[sandy666]

with Power Query

Raw		Raw.1	Raw.2	Raw.3
The Coasters - Charlie Brown - Poison Ivy		The Coasters	Charlie Brown	Poison Ivy
The Impalas - Sorry (I Ran All the Way Home) - Brand New Cadillac: Rock & Roll		The Impalas	Sorry (I Ran All the Way Home)	Brand New Cadillac: Rock & Roll

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Raw", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Raw.1", "Raw.2", "Raw.3"}),
    Trim = Table.TransformColumns(Split,{{"Raw.1", Text.Trim, type text}, {"Raw.2", Text.Trim, type text}, {"Raw.3", Text.Trim, type text}})
in
    Trim

 

[mikerickson]

If you want formulas

=TRIM(LEFT(SUBSTITUTE(A1,"-",REPT(" ",255)), 255))

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",255)), 255, 255))

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)), 255))

 

[Rick Rothstein]

Or, alternately, if you want a formula, put this in cell B1 (where I have assumed your first data value is in cell A1) and copy it across to cell D1, then copy cells B1, C1, D1 down to the bottom of your data...

=TRIM(MID(SUBSTITUTE("-"&$A1,"-",REPT(" ",300)),COLUMNS($B:B)*300,300))

 

[fidelito101]

with Power Query

Raw		Raw.1	Raw.2	Raw.3
The Coasters - Charlie Brown - Poison Ivy		The Coasters	Charlie Brown	Poison Ivy
The Impalas - Sorry (I Ran All the Way Home) - Brand New Cadillac: Rock & Roll		The Impalas	Sorry (I Ran All the Way Home)	Brand New Cadillac: Rock & Roll

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Raw", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"Raw.1", "Raw.2", "Raw.3"}),
    Trim = Table.TransformColumns(Split,{{"Raw.1", Text.Trim, type text}, {"Raw.2", Text.Trim, type text}, {"Raw.3", Text.Trim, type text}})
in
    Trim

Thank you very much for your help

 

[fidelito101]

If you want formulas

=TRIM(LEFT(SUBSTITUTE(A1,"-",REPT(" ",255)), 255))

=TRIM(MID(SUBSTITUTE(A1,"-",REPT(" ",255)), 255, 255))

=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",255)), 255))

Thanks a lot, this method works too

 

[sandy666]

Thank you

You are welcome & thanks for the feedback