Hamsters ate my post!
So I’ll try again.
In an Excel file I have Street address, Unit, City-State-Zip, and Country. They are all in the same cell instead of different cells. Format is ‘wrap text’. If I turn off ‘wrap text’ the entire address is in a single line within the cell, with each element separated by a non-display character (presumably carriage return/line feed). I tried using Text-to-columns, but it just deletes everything after the first non-display character.
How do I put each ‘line’ of wrapped text (or each element of the address that is separated by the non-display character in unwrapped text) into its own cell on the record?
Have you hit Alt-Enter to get the carriage return or is it simply wrapping nicely because of the column width?
The file came from outside, so I don’t know how he did it. But the non-display characters indicate the end of the line. So I assume it’s Alt-Enter.
Could you do a find-replace the carriage returns with something like ‘|’ and then use that to seperate the values in text-to-colums.
That’s what I was thinking, Lobsang.
I think the carriage return is char(10), isn’t it? I’m not sure how you can tell Excel to find and replace it though…
OK, I tried that. When I Alt+Enter in the find box and put a pipe in the replace box, it says it can’t find any data to replace.
Off another website:
Try edit>replace, in the “find what” field, hold down alt and type 010, release alt key, then enter whatever character in the “replace with” field.
Haven’t tried it myself though!
Try copying your data into MS word.
Find ‘^p’ and replace with something else.
then copy back to excel and do the text to columns.
The crude way to do this is to save your entire entire Excel worksheet as unicode text (.txt). Then in Notepad or whatever, open the file, then use Control-C to copy the non-printing line-return character (the little box). Then you can do a search and replace, where you replace the line-return character with some other character combination (let’s say, three dollar signs). Then you can reimport the text file into Excel, then use the three dollar signs to divide your columns when you do Text to Columns.
There has to be a better way in Excel proper, but this method will at least let you get the job done.
Belay that. Sandra_nz’s method works perfectly.
Glad it did. You could also paste special as text, then do text to columns.
