Parsing help requested

[Vidcapper]

I have some music data in the form :

Cell A1 : 'The Vamps - Wild Heart - EP'
Cell A2 : 'Eminem - The Marshall Mathers LP2 (Deluxe)'

Which I'd like split as :

'The Vamps', 'Wild Heart - EP'
'Eminem' , 'The Marshall Mathers LP2 (Deluxe)'

You Me At Six

In B1 I have :

=LEFT(a1,FIND(" - ",a1)-1)

& in C1 :

=TRIM(RIGHT(SUBSTITUTE(a1," - ",REPT(" ",99)),99))

This produces the desired result for the 'Eminem' entry, but for 'The
Vamps' entry I just get

'The Vamps', 'EP' omitting the 'Wild Heart' part. Is there a way of
parsing it which produces the desired result?

TIA

 

[Claus Busch]

Hi,

Am Sun, 26 Jan 2014 09:20:06 +0000 schrieb Vidcapper:

I have some music data in the form :

Cell A1 : 'The Vamps - Wild Heart - EP'
Cell A2 : 'Eminem - The Marshall Mathers LP2 (Deluxe)'

Which I'd like split as :

'The Vamps', 'Wild Heart - EP'
'Eminem' , 'The Marshall Mathers LP2 (Deluxe)'

in B1 try:
=LEFT(A1,FIND("#",SUBSTITUTE(A1," - ","#"))-1)
in C1 try:
=SUBSTITUTE(A1,B1&" - ",)


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Sun, 26 Jan 2014 10:32:43 +0100 schrieb Claus Busch:

=LEFT(A1,FIND("#",SUBSTITUTE(A1," - ","#"))-1)
in C1 try:
=SUBSTITUTE(A1,B1&" - ",)

try:
=LEFT(A1,FIND(" - ",A1)-1)

=SUBSTITUTE(A1,B1&" - ",)



Regards
Claus B.

 

[Vidcapper]

Hi again,

Am Sun, 26 Jan 2014 10:32:43 +0100 schrieb Claus Busch:


try:
=LEFT(A1,FIND(" - ",A1)-1)

=SUBSTITUTE(A1,B1&" - ",)

Thank you - that did the trick, plus solved another glitch in my
spreadsheet that I hadn't even noticed before!