# Excel question **URL:** \ **Category:** Factual Questions\ **Created:** [November 6, 2007, 7:40pm UTC](https://boards.straightdope.com/t/excel-question/425451 "2007-11-06T19:40:24Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 6, 2007, 7:40pm UTC](https://boards.straightdope.com/t/excel-question/425451/1 "2007-11-06T19:40:24Z")
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? ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [November 6, 2007, 7:49pm UTC](https://boards.straightdope.com/t/excel-question/425451/2 "2007-11-06T19:49:51Z")
Have you hit Alt-Enter to get the carriage return or is it simply wrapping nicely because of the column width? ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 6, 2007, 7:55pm UTC](https://boards.straightdope.com/t/excel-question/425451/3 "2007-11-06T19:55:12Z")
[QUOTE=sandra\_nz] Have you hit Alt-Enter to get the carriage return or is it simply wrapping nicely because of the column width? [/QUOTE] 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. ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [November 6, 2007, 7:55pm UTC](https://boards.straightdope.com/t/excel-question/425451/4 "2007-11-06T19:55:15Z")
Could you do a find-replace the carriage returns with something like ‘|’ and then use that to seperate the values in text-to-colums. ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [November 6, 2007, 7:58pm UTC](https://boards.straightdope.com/t/excel-question/425451/5 "2007-11-06T19:58:11Z")
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… ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 6, 2007, 7:58pm UTC](https://boards.straightdope.com/t/excel-question/425451/6 "2007-11-06T19:58:13Z")
[QUOTE=Lobsang] Could you do a find-replace the carriage returns with something like ‘|’ and then use that to seperate the values in text-to-colums. [/QUOTE] 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. ---
**Author:** ![sandra\_nz](https://avatars.discourse-cdn.com/v4/letter/s/eada6e/32.png) [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)\ **Post date:** [November 6, 2007, 8:00pm UTC](https://boards.straightdope.com/t/excel-question/425451/7 "2007-11-06T20:00:35Z")
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! ---
**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\ **Post date:** [November 6, 2007, 8:02pm UTC](https://boards.straightdope.com/t/excel-question/425451/8 "2007-11-06T20:02:11Z")
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. ---
**Author:** ![Sal\_Ammoniac](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@Sal\_Ammoniac](https://boards.straightdope.com/u/Sal_Ammoniac)\ **Post date:** [November 6, 2007, 8:04pm UTC](https://boards.straightdope.com/t/excel-question/425451/9 "2007-11-06T20:04:12Z")
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. ---
**Author:** ![Sal\_Ammoniac](https://avatars.discourse-cdn.com/v4/letter/s/8dc957/32.png) [@Sal\_Ammoniac](https://boards.straightdope.com/u/Sal_Ammoniac)\ **Post date:** [November 6, 2007, 8:15pm UTC](https://boards.straightdope.com/t/excel-question/425451/10 "2007-11-06T20:15:11Z")
Belay that. **Sandra\_nz** ’s method works perfectly. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [November 6, 2007, 10:39pm UTC](https://boards.straightdope.com/t/excel-question/425451/11 "2007-11-06T22:39:27Z")
[QUOTE=sandra\_nz] 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! [/QUOTE] It worked! Many thanks! 🙂 ---
**Author:** ![cornflakes](https://avatars.discourse-cdn.com/v4/letter/c/9d8465/32.png) [@cornflakes](https://boards.straightdope.com/u/cornflakes)\ **Post date:** [November 7, 2007, 4:21am UTC](https://boards.straightdope.com/t/excel-question/425451/12 "2007-11-07T04:21:59Z")
Glad it did. You could also paste special as text, then do text to columns.