Splitting Text from single cell in column across multiple Columns

Text To column wont work because I have name and address info in a single
cell in this format. Each cell has different data representing different
addresses.

Name
St # St Name
Phone Number
City, State zip

I want a formula or something to take first line and put in one column, the
2nd line in another column and the 3rd line spread across 3 columns. Although
if you could just find a way to split each line into a column, that would
work.

Once again, Text to colun won't work, I have multiple cells with diffeent
addresses, they are not all the same and there are no delimiters.

Harold-

it may take a few steps, put you can parse your "big" string using left()
and right()

A1 = your cell contents ("big" string)
The linewrap in the cell is most likely char(10), in which case you can use
B1=left(A1,find(char(10),A1)-1)
then you want to shorten your "big" string to only what is left, so you can
do it again:
C1 = right(A1,(len(A1)-Len(B1))-1)
rinse and repeat for your next 3 columns

If you want to split your phone number out into 3 columns, do you have a
standard format? If so, use that standard format to determine how many digits
to split out (you won't need the count function). For example, if it is (123)
456-7890 [spaces intentional] then you might use:
W1 = your number
X1 = mid(W1,2,3)
Y1 = mid(W1,7,3)
Z1 = mid(W1,11,4)

HTH,
Keith

"Harold" wrote:

Text To column wont work because I have name and address info in a single
cell in this format. Each cell has different data representing different
addresses.

Name
St # St Name
Phone Number
City, State zip

I want a formula or something to take first line and put in one column, the
2nd line in another column and the 3rd line spread across 3 columns. Although
if you could just find a way to split each line into a column, that would
work.

Once again, Text to colun won't work, I have multiple cells with diffeent
addresses, they are not all the same and there are no delimiters.

You can use Text To Columns to split the separate lines of text out to
individual columns... just uncheck any checked CheckBoxes and then select
the CheckBox labeled "Other", click into its empty field and key-in Ctrl+J
(which this the key-in for a New Line character)... you won't see anything
happen in the field you key it into to, but look down at the chart and you
will see the lines of text were split apart.

--
Rick (MVP - Excel)


"Harold" wrote in message
...
Text To column wont work because I have name and address info in a single
cell in this format. Each cell has different data representing different
addresses.

Name
St # St Name
Phone Number
City, State zip

I want a formula or something to take first line and put in one column,
the
2nd line in another column and the 3rd line spread across 3 columns.
Although
if you could just find a way to split each line into a column, that would
work.

Once again, Text to colun won't work, I have multiple cells with diffeent
addresses, they are not all the same and there are no delimiters.

Hi,

Once can also input Alt+010 (on the numeric keypad) instead of Ctrl+J

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Rick Rothstein" wrote in message
...
You can use Text To Columns to split the separate lines of text out to
individual columns... just uncheck any checked CheckBoxes and then select
the CheckBox labeled "Other", click into its empty field and key-in Ctrl+J
(which this the key-in for a New Line character)... you won't see anything
happen in the field you key it into to, but look down at the chart and you
will see the lines of text were split apart.

--
Rick (MVP - Excel)


"Harold" wrote in message
...
Text To column wont work because I have name and address info in a single
cell in this format. Each cell has different data representing different
addresses.

Name
St # St Name
Phone Number
City, State zip

I want a formula or something to take first line and put in one column,
the
2nd line in another column and the 3rd line spread across 3 columns.
Although
if you could just find a way to split each line into a column, that would
work.

Once again, Text to colun won't work, I have multiple cells with diffeent
addresses, they are not all the same and there are no delimiters.