Text to the left and right of a character
 

I have a list of file names (Music), the format is "Beatles - Yellow
Submarine" in cells a1 to a10000 (for arguments sake). I want to have
in B1 - Beatles and in C1 - Yellow Submarine.

All files are the same format (Beatles - Yellow Submarine.mp3)

What formulas would I use to get the text to the left of "-" and text
to the right of "-"? Any helpers greatly appreciated

Esra

Text to the left and right of a character
 

Did you mention the filenames with the mp3 extension for a reason? I'm
assuming the first part of your post (no .mp3) is what is in Column A. See
if these formula do what you want...

B1: =TRIM(LEFT(A1, FIND("-",A1)-1))
C1: =TRIM(MID(A1,FIND("-",A1)+1,255))

Rick


"Esra" wrote in message ...
I have a list of file names (Music), the format is "Beatles - Yellow
Submarine" in cells a1 to a10000 (for arguments sake). I want to have
in B1 - Beatles and in C1 - Yellow Submarine.

All files are the same format (Beatles - Yellow Submarine.mp3)

What formulas would I use to get the text to the left of "-" and text
to the right of "-"? Any helpers greatly appreciated

Esra

Text to the left and right of a character
 

=LEFT(A2,FIND(" - ",A2)-1)
=RIGHT(A2,LEN(A2)-FIND(" - ",A2)-2)
--
David Biddulph

<Esra wrote in message ...
I have a list of file names (Music), the format is "Beatles - Yellow
Submarine" in cells a1 to a10000 (for arguments sake). I want to have
in B1 - Beatles and in C1 - Yellow Submarine.

All files are the same format (Beatles - Yellow Submarine.mp3)

What formulas would I use to get the text to the left of "-" and text
to the right of "-"? Any helpers greatly appreciated

Esra

Text to the left and right of a character
 

=LEFT(A1,FIND("-",A1)-2) gets you Beatles
=MID(A1,FIND("-",A1)+2,256) get Yellow Submarine.mp3
=MID(A2,FIND("-",A1)+2,LEN(A1)-FIND("-",A1)-5) gets Yellow Submarine

But have you looked into Data | Text to Columns ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

<Esra wrote in message ...
I have a list of file names (Music), the format is "Beatles - Yellow
Submarine" in cells a1 to a10000 (for arguments sake). I want to have
in B1 - Beatles and in C1 - Yellow Submarine.

All files are the same format (Beatles - Yellow Submarine.mp3)

What formulas would I use to get the text to the left of "-" and text
to the right of "-"? Any helpers greatly appreciated

Esra

Text to the left and right of a character
 

On Jan 29, 11:26*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Did you mention the filenames with the mp3 extension for a reason? I'm
assuming the first part of your post (no .mp3) is what is in Column A. See
if these formula do what you want...

B1: * *=TRIM(LEFT(A1, FIND("-",A1)-1))
C1: * *=TRIM(MID(A1,FIND("-",A1)+1,255))

Rick



"Esra" wrote in messagenews:4gksp3tpo0f4hsdb836fnm6t8opilrnrbd@4ax .com...
I have a list of file names (Music), the format is "Beatles - Yellow
Submarine" in cells a1 to a10000 (for arguments sake). *I want to have
in B1 - Beatles and in C1 - Yellow Submarine.

All files are the same format (Beatles - Yellow Submarine.mp3)

What formulas would I use to get the text to the left of "-" and text
to the right of "-"? *Any helpers greatly appreciated

Esra- Hide quoted text -

- Show quoted text -

Works just fine thank you so much, and thanks to all who answered.
I mentioned mp3 not for any specific reason, I just have to
delete .mp3 before I can get the correct value, easy enough to do.

Again thank you