Multi line Text-to-column - A CHALLENGE FOR YOU!

January 24, 2008 3:01 PM

How to "text to column" a cell with multiple lines in Excel?

I need to extract a cell that contains multiple lines (name, address, etc) into a column for each line. IE.

keasby inc
212 Fifth Avenue
NY NY 10001
203-417-5555

There are no useful delimiters like ; or anything.

I tried using "other" in the delimiter option and hitting alt-enter but it doesn't recognize. Further, it doesn't even pick up the second line of data in the display field.

Someone must have a macro out there for this! Help!

Much obliged,
Keasby

posted by keasby to computers & internet (7 comments total)

First, go to an empty cell, and enter:

=CHAR(32)

then copy that cell, and do a global search and replace replacing the character you've just copied with, say, ~, or some other useful delimiter.

Now text-to-columns using that as your delimiter.
posted by pompomtom at 3:14 PM on January 24

Oh bollocks... scratch that. *slinks off to test more...*
posted by pompomtom at 3:18 PM on January 24

I don't know if this is the easiest way to do it, but I'd 1) copy the table from Excel into Word; 2) use Word to replace the line breaks with some other, preferred delimiter (Edit / Replace... use ^l in the "Find what" box to find line breaks, and replace them with ; (assuming there's no semicolons in your original data; if there are, use a different character which isn't present); 3) copy the table back to Excel 4) use text to column now that you've got an actual delimiter you can use.

(If ^l doesn't work in step 2, try ^p)
posted by DevilsAdvocate at 3:20 PM on January 24 [1 favorite]

OK, take 2:

The following macro will do the search/replace:


sub replace_alt_enter_with_tilde()

Cells.Replace What:=Chr(10), Replacement:="~", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

end sub




(the underscore should be the last character on its line, just in case AskMe mucks up the format...)
posted by pompomtom at 3:23 PM on January 24 [1 favorite]

I've done this using DevilsAdvocate's method. pompomtom's Take 2 method looks more elegant, but the question is: are you daring enough to open the macro editor?
posted by notyou at 3:28 PM on January 24

Opening the macro editor is easy.

Alt+F11
Insert > Module
paste the text into the module window
Go back to your worksheet and select the cells you want
Tools > Macro > Macros
Then select the macro, in this case replace_alt_enter_with_tilde.
posted by jasper411 at 6:11 PM on January 24 [1 favorite]

Many big big hugs to the most lovable Hive Mind! Yall are the best!
posted by keasby at 6:48 AM on January 25