Prev: I can still select & manipulate locked & protected cells in '07
Next: On Time Calculation
From: Harold on 12 Mar 2010 16:53
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.
From: ker_01 on 12 Mar 2010 19:11
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.
From: Rick Rothstein on 13 Mar 2010 13:51
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" <Harold(a)discussions.microsoft.com> wrote in message
news:3541D386-66DE-436D-BCA0-F1E9FCF5E078(a)microsoft.com...
> 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.

From: Ashish Mathur on 14 Mar 2010 07:06
Hi,

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

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Rick Rothstein" <rick.newsNO.SPAM(a)NO.SPAMverizon.net> wrote in message
news:etnTG5twKHA.5812(a)TK2MSFTNGP02.phx.gbl...
> 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" <Harold(a)discussions.microsoft.com> wrote in message
> news:3541D386-66DE-436D-BCA0-F1E9FCF5E078(a)microsoft.com...
>> 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.
>
 | 
Pages: 1
Prev: I can still select & manipulate locked & protected cells in '07
Next: On Time Calculation