Text to columns

N

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.
 
C

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
 
N

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top