Text to columns

[Neel]

Hi,

I am trying to split a column of data into multiple columns but I want
to delimit using a word.

e.g.

Row 1 contains the text string - Name changed from abc motor car co to
ABC Motor Car Co
Row 2 - Name changed from jim ellis hyundai of south georgia to Jim
Ellis Hyundai of South Georgia

Each row has a different amount of characters so we can't really use a
fixed width delimiter in this case. My only true delimiter is the text
string 'to'.

I want to break the text in such a way that everything before the word
"to" is in one column and everything after it is in another column.

Is there any way to do this?

Thanks,
Neel.

 

[Claus Busch]

Hi Neel,

Am Mon, 25 Jun 2012 08:34:20 -0700 (PDT) schrieb Neel:

Row 1 contains the text string - Name changed from abc motor car co to
ABC Motor Car Co
Row 2 - Name changed from jim ellis hyundai of south georgia to Jim
Ellis Hyundai of South Georgia

I want to break the text in such a way that everything before the word
"to" is in one column and everything after it is in another column.

try it with formula. Your string in A1, then in B1:
=LEFT(A1,FIND(" to",A1)) and in C1:
=SUBSTITUTE(A1,B1&"to ",)


Regards
Claus Busch

 

[Neel]

Hi Neel,

Am Mon, 25 Jun 2012 08:34:20 -0700 (PDT) schrieb Neel:


try it with formula. Your string in A1, then in B1:
=LEFT(A1,FIND(" to",A1))    and in C1:
=SUBSTITUTE(A1,B1&"to ",)

Regards
Claus Busch

Claus,

You, sir, are the man! I figured another way to go about it where I
used the LEFT and RIGHT function along with LEN and FIND. But, this is
another one to keep in the back pocket. Thanks a lot!

Regards,
Neel.