Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Hello,
I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Luke
if every address is in 5 lines than you can do the following: A B C D E F 1 1 Luke Road =B3 =B4 =B5 =B6 2 Luke Town 3 Luke City 4 Lukeshire 5 LU1 1KE than select C1:F5, drag (by the little cross hair + that form at the bottom right corner of the last selected cell) till the end of your data. now C to F will have data opposite the first line of the address. paste special and then you can filter out the blank cells (C to F) and delete the rows. you have your data "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
I placed your data as listed in cell B3
In row c3 I entered =find("alt-ent",B3) with the quotes but using alt enter so visually the formual looks like a carriage return between quotaion marks.... there's probably some way to use either code() or char()... I didn't have that though... C3 gave me a value of 12, in D3 I entered =find("alt-ent",$B3,1+C3) and copied that to cells E3 and F3. (This gave values of 22, 31, and 41 in d3-f3) then in G3: =LEFT(B3,C3-1) in H3: =MID($B3,C3+1,D3-C3-1) copied that right to populate I3 and J3 then finished with this formula in K3 =RIGHT(B3,LEN(B3)-F3) On my machine i ended up with street address in column G, Town in column H, City in column I, "shire" in column J, and postal code in column K. Hope that helps |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Hi Luke
Two ways to approach this.. --Using menu function..Select the range/column needs to be changed. From menu DataText to Columns will populate the 'Convert Text to Columns Wizard'. By default the selection is 'Delimited'. Keep the selection and hit 'Next'. From the Step2 of the Wizard from the options Tab,Semicolon,Comma,Space,Other Select 'Other' and within the box enter Ctrl+j and hit Next. Hit Finish --Using formulas..With address in cell A1 try the below formula in cell B1 and copy across/to the right as required.. =TRIM(MID(SUBSTITUTE(CHAR(10) & $A$1& REPT(CHAR(10),6),CHAR(10),REPT(CHAR(32),255)),COLU MNS($B$1:B$1)*255,255)) -- Jacob "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Thanks E.Q., you are officially my favourite person of the year so far - and
you're going to be hard to beat, that was absolute genius!!! -- Thanks Luke "E.Q." wrote: I placed your data as listed in cell B3 In row c3 I entered =find("alt-ent",B3) with the quotes but using alt enter so visually the formual looks like a carriage return between quotaion marks.... there's probably some way to use either code() or char()... I didn't have that though... C3 gave me a value of 12, in D3 I entered =find("alt-ent",$B3,1+C3) and copied that to cells E3 and F3. (This gave values of 22, 31, and 41 in d3-f3) then in G3: =LEFT(B3,C3-1) in H3: =MID($B3,C3+1,D3-C3-1) copied that right to populate I3 and J3 then finished with this formula in K3 =RIGHT(B3,LEN(B3)-F3) On my machine i ended up with street address in column G, Town in column H, City in column I, "shire" in column J, and postal code in column K. Hope that helps |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Another option will be using a Macro - as shown...
http://img251.imageshack.us/img251/792/noname.png-- Micky "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Just a little amendment the formula should be
A B C D E F 1 1 Luke Road =B2 =B3 =B4 =B5 "walrus" wrote: Luke if every address is in 5 lines than you can do the following: A B C D E F 1 1 Luke Road =B3 =B4 =B5 =B6 2 Luke Town 3 Luke City 4 Lukeshire 5 LU1 1KE than select C1:F5, drag (by the little cross hair + that form at the bottom right corner of the last selected cell) till the end of your data. now C to F will have data opposite the first line of the address. paste special and then you can filter out the blank cells (C to F) and delete the rows. you have your data "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Hi,
Highlight the column, Text to columns, Fixed with, next, finish "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Perhaps this formula, which assumes your sample entry is in cell A1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CH AR(10),",",1),CHAR(10),",",1),CHAR(10),",",1),CHAR (10),",",1) I believe you'll find that [Alt]+[Enter] is ASCII code 10, that's why the CHAR(10) in the formula. I ended up with: 1 Luke Road,Luke Town,Luke City,Lukeshire,LU1 1KE in cell B1 (where I had placed the formula). From that point I can use Edit-- Copy on all cells with similar formulas in them followed by Edit -- Paste Special with the "Values" option chosen to convert the formulas to actual text that can then be moved to columns using Text To Column. "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Use Text to Columns with a tiny trick to specify the ALT-ENTER character:
1. click on the column 2. Data Text to columns... delimited Next Other and click in the character entry box 3. while holding down the ALT key, touch 010 on the numeric keypad. 4. Finish ALT-010 allows you to specify the hard-return as the separator. -- Gary''s Student - gsnu200909 "LukeLibrarian" wrote: Hello, I have a column of over 600 addresses, and they are entered as follows: 1 Luke Road Luke Town Luke City Lukeshire LU1 1KE When unwrapped, it obviously just looks like one long sentence with squares where the (ALT+Enter)'s were. My issue is that I would like to split each line of the address out into adjacent columns. Any help would be gratefully received, and save me a few hours of manually typing in commas so that I can TTC it! -- Thanks Luke |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Another trick if you want the data split vertically into cells.
You have 5 lines of text in a cell with 4 alt + enter linefeeds. Insert 5 cells below that cell. Select the cell and select all text in formula bar and Copy. Select the 5 cells below and Paste. Gord Dibben MS Excel MVP On Tue, 12 Jan 2010 08:28:01 -0800, Gary''s Student wrote: Use Text to Columns with a tiny trick to specify the ALT-ENTER character: 1. click on the column 2. Data Text to columns... delimited Next Other and click in the character entry box 3. while holding down the ALT key, touch 010 on the numeric keypad. 4. Finish ALT-010 allows you to specify the hard-return as the separator. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Splitting out a wrapped text entry
Glad you liked it... but I think some of the other suggestions look more elegant than my answer; they suggest using features I didn't know aobut. I appreciate the MVP's for responding. I plan on looking into their responses a bit closer when I get to work. This site has proven a great place to pick up Excel "tricks". My method worked, but maybe I'll be able to use one of the other suggested techniques next time I see a similar puzzle. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing wrapped text | Excel Discussion (Misc queries) | |||
Wrapped text is not visible, how do I fix this? | Excel Worksheet Functions | |||
Wrapped text | Excel Discussion (Misc queries) | |||
Limit to Wrapped Text? | Excel Discussion (Misc queries) | |||
wrapped text does not display | Excel Discussion (Misc queries) |