In Cell Formula to mimic text to columns.
 

Hello,

My data in Column A is delimited like this "AAA; BBB; CCC; DDD; EEE" - up
to a maximum of 7 items (6 delimiters)

Using a formula I want to place
"AAA" in Column B
"BBB" in Column C
"DDD" in Column E etc

The text strings are not of uniform length though, each delimited value
could be between 1 and 30 characters.

If there is no data for the n'th delimiter, then the formula should just
return ""

I can't use the Text to Columns function, as no manual input is allowed, the
sheet has to handle all of the data separation automatically, and no VBA can
be used either.

My Formula in B1 =IF(ISBLANK(A1),"",LEFT(A1,FIND("; ",A1,1)-1))
correctly returns "AAA"

In C1, I'm having a devil of a time trying to get it to know that they need
to start their TRIM or LEFT/RIGHT text removal from the second instance of ";
" and the number of characters to return until the next delimiter.

Is this possible?

Many thanks for any help.
KeLee

In Cell Formula to mimic text to columns.
 

Try this single formula ..With data in cell A1 try the below formula in B1
and copy across to H.

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(";"&$A $1&REPT(";",6),";",REPT(CHAR(32),LEN($A$1)+6),COLU MN(B1)),LEN($A$1)+6)),";",REPT(CHAR(32),LEN($A$1)) ),LEN($A$1)))

If this post helps click Yes
---------------
Jacob Skaria


"InsomniacFolder" wrote:

Hello,

My data in Column A is delimited like this "AAA; BBB; CCC; DDD; EEE" - up
to a maximum of 7 items (6 delimiters)

Using a formula I want to place
"AAA" in Column B
"BBB" in Column C
"DDD" in Column E etc

The text strings are not of uniform length though, each delimited value
could be between 1 and 30 characters.

If there is no data for the n'th delimiter, then the formula should just
return ""

I can't use the Text to Columns function, as no manual input is allowed, the
sheet has to handle all of the data separation automatically, and no VBA can
be used either.

My Formula in B1 =IF(ISBLANK(A1),"",LEFT(A1,FIND("; ",A1,1)-1))
correctly returns "AAA"

In C1, I'm having a devil of a time trying to get it to know that they need
to start their TRIM or LEFT/RIGHT text removal from the second instance of ";
" and the number of characters to return until the next delimiter.

Is this possible?

Many thanks for any help.
KeLee

In Cell Formula to mimic text to columns.
 

This worked perfectly.
I've used the single formula version from Jacob Skaria.
Thank you both for a speedy and perfect reply.

Regards,
KeLee

"InsomniacFolder" wrote:

Hello,

My data in Column A is delimited like this "AAA; BBB; CCC; DDD; EEE" - up
to a maximum of 7 items (6 delimiters)

Using a formula I want to place
"AAA" in Column B
"BBB" in Column C
"DDD" in Column E etc

The text strings are not of uniform length though, each delimited value
could be between 1 and 30 characters.

If there is no data for the n'th delimiter, then the formula should just
return ""

I can't use the Text to Columns function, as no manual input is allowed, the
sheet has to handle all of the data separation automatically, and no VBA can
be used either.

My Formula in B1 =IF(ISBLANK(A1),"",LEFT(A1,FIND("; ",A1,1)-1))
correctly returns "AAA"

In C1, I'm having a devil of a time trying to get it to know that they need
to start their TRIM or LEFT/RIGHT text removal from the second instance of ";
" and the number of characters to return until the next delimiter.

Is this possible?

Many thanks for any help.
KeLee