Splitting out a wrapped text entry

  • Thread starter Thread starter LukeLibrarian
  • Start date Start date
L

LukeLibrarian

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!
 
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
 
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
 
Hi Luke

Two ways to approach this..

--Using menu function..Select the range/column needs to be changed. From
menu Data>Text 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)),COLUMNS($B$1:B$1)*255,255))
 
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!!!
 
Perhaps this formula, which assumes your sample entry is in cell A1:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(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.
 
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.
 
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
 
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.
 
Back
Top