If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Splitting Text from single cell in column across multiple Columns
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. |
#3
|
|||
|
|||
Splitting Text from single cell in column across multiple Columns
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. |
#4
|
|||
|
|||
Splitting Text from single cell in column across multiple Columns
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. |
Thread Tools | |
Display Modes | |
|
|