Text to columns

  • Thread starter Thread starter Neel
  • Start date Start date
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.
 
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
Click to expand...
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.
Click to expand...

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
 
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
Click to expand...

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.
 
Back
Top