Multi line Text-to-column - A CHALLENGE FOR YOU!
January 24, 2008 3:01 PM Subscribe
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
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
Oh bollocks... scratch that. *slinks off to test more...*
posted by pompomtom at 3:18 PM on January 24, 2008
posted by pompomtom at 3:18 PM on January 24, 2008
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, 2008 [1 favorite]
(If ^l doesn't work in step 2, try ^p)
posted by DevilsAdvocate at 3:20 PM on January 24, 2008 [1 favorite]
Best answer: 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, 2008 [1 favorite]
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, 2008 [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, 2008
posted by notyou at 3:28 PM on January 24, 2008
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, 2008 [1 favorite]
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, 2008 [1 favorite]
Response by poster: Many big big hugs to the most lovable Hive Mind! Yall are the best!
posted by keasby at 6:48 AM on January 25, 2008
posted by keasby at 6:48 AM on January 25, 2008
This thread is closed to new comments.
=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, 2008