# Non-display ASCII text (again) **URL:** \ **Category:** Factual Questions\ **Created:** [March 25, 2008, 11:04pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740 "2008-03-25T23:04:58Z")\ **Posts on this page:** 20\ **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:** [March 25, 2008, 11:04pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/1 "2008-03-25T23:04:58Z")
A while ago someone helped me Replace All ALT+010 in Excel so that I could separate elements in a cell using Text-to-columns by the simple expedient of telling my what the ALT code for the non-display character is. (I had assumed it was CRLF.) Worked like a charm. Only now I have a file with two\* adjacent non-display characters. I went to change the ALT+010s to pipes, but only one set changed. The other still displays in Excel as a little box. Anyone know the ALT code? ---
**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:** [March 25, 2008, 11:25pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/2 "2008-03-25T23:25:57Z")
Additional: If I continue with the Text-to-columns (with only one of the characters changed) the first part of the field is kept. The rest just goes away, never to bee seen again. It should go into the next column, but instead it just disappears. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 25, 2008, 11:33pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/3 "2008-03-25T23:33:12Z")
My guess is that the other one has a value of 13 (CR), instead of 10 (LF), as those two in concert make up a CRLF. Since you replaced the 10 with a pipe symbol, try replacing 13 with nothing. ---
**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:** [March 26, 2008, 2:24am UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/4 "2008-03-26T02:24:09Z")
I think I tried that (ALT + 013) without success. I’ll try it (again?) mañana. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 3:46am UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/5 "2008-03-26T03:46:11Z")
[QUOTE=Johnny L.A.] I think I tried that (ALT + 013) without success. I’ll try it (again?) mañana. [/QUOTE] Since you have Excel, what about just using the built in VB to do a “ASCII()” (or ASC(), I don’t remember which it is in VB) on the non-printable character? ---
**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:** [March 26, 2008, 12:31pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/6 "2008-03-26T12:31:27Z")
How do you do that? ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 2:05pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/7 "2008-03-26T14:05:54Z")
What version of Excel? ---
**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:** [March 26, 2008, 3:24pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/8 "2008-03-26T15:24:13Z")
1. I tried Replace all using ALT+013. None were found. So the non-display character must be something else. Once the non-displays are replaced, there is still the problem where when I do Text-to-columns everything after the delimiter disappears. (Of course this might be resolved after the mystery non-display character is fixed.) ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 4:59pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/9 "2008-03-26T16:59:39Z")
Apparently, they call the function CODE() in Excel, instead of ASCII(). In any cell, type in: ```auto =CODE("x") ``` replacing the “x” with your symbol inside of the quotes. This will give you the ASCII value of that symbol. ---
**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:** [March 26, 2008, 5:16pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/10 "2008-03-26T17:16:58Z")
OK, this is what I did: [ul][li]Typed in **=code("**, went to the other side of the little square, and typed another double-quote and the close-paren. Kind of like this: **=code("[]")**[/li][li]Copied that and pasted it into another cell. It looks like this: **=code"** , then another double-quote on the next line.[/ul][/li]So I’m going to paste what I have on my clipboard:\*\* =code(" ") \*\*The non-display character moved the close-quote and close-paren to the next line. I assume the mystery character is CR, but if CR is ALT+013, then that’s not what it is because Find all can’t find anything when I search on ALT+013. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 6:11pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/11 "2008-03-26T18:11:19Z")
Yeah, the nature of control characters can often cause these kinds of issues. The menu-driven Search/Replace functionality doesn’t seem to like functions or regular expressions, but I have another thing worth trying. I’m assuming they are always in pairs in this worksheet, as most sources use either LF or CRLF, but whichever they use they use universally. If so, put **?x** (replacing the “x” with the Alt-0010 LF symbol) in the “Find what” box, and the pipe symbol in the “Replace with” box, and see if that works.The question mark acts as a single character wildcard. ---
**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:** [March 26, 2008, 6:32pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/12 "2008-03-26T18:32:31Z")
So you want me to put ?ALT+010 in the find box? It doesn’t find it. It does find ALT+010. The problem is the other character. Let me give you an example: **"123 - FAKE AVENUE #100 Seattle, WA 98104 USA United States of America"** That’s how it looks in the Formula box. (I assume you’re seeing it as three lines. The double-quotes only appear when I cut-and-paste the address here. They don’t appear in the cell or the Formula box.) This is how it looks in the cell (with the non-display squares represented by square brackets): **123 - FAKE AVENUE #100[][]Seattle, WA 98104[]USA United States of America** Now. When I Replace all ALT+010 with pipes I get this: **123 - FAKE AVENUE #100[]|Seattle, WA 98104[]|USA United States of America** As you can see, the second ‘square’ was indeed changed to a pipe. But the first one wasn’t. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 7:35pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/13 "2008-03-26T19:35:07Z")
[QUOTE=Johnny L.A.] As you can see, the second ‘square’ was indeed changed to a pipe. But the first one wasn’t. [/QUOTE] Yeah, because the first square has an ASCII value of 13, the CR. Since it doesn’t seem to handle inputting Alt-0013 into the “Find what” box, probably because it just thinks you hit the Enter key, you might have to resort to code. To go down that path, there are many options, but something like [this](http://answers.yahoo.com/question/index?qid=1005120800381) would probably do the trick. ---
**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:** [March 26, 2008, 8:05pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/14 "2008-03-26T20:05:21Z")
[QUOTE=DMC] like [this](http://answers.yahoo.com/question/index?qid=1005120800381) would probably do the trick. [/QUOTE] YES! I don’t know anything about macros. (ISTR writing some for something or other over a decade ago, but ‘use it or lose it’.) So I just followed the instructions on your link and replaced the “[space]” with “|” and ran it against the file. Thanks! 🙂 ---
**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:** [March 26, 2008, 8:09pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/15 "2008-03-26T20:09:19Z")
[QUOTE=DMC] Yeah, because the first square has an ASCII value of 13, the CR. [/QUOTE] It must be something else. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 9:27pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/16 "2008-03-26T21:27:36Z")
[QUOTE=Johnny L.A.] It must be something else. [/QUOTE] I still suspect it’s a 13, just that the nature of that particular control character is causing the weird behavior. The best way to know for sure is to load it up into a hex editor. ---
**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:** [March 26, 2008, 9:46pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/17 "2008-03-26T21:46:54Z")
Heh. I was thinking that if only I had this data on a mainframe I could to HEX ON and see what it is. Anyway, the macro worked. I’m going through the file and cleaning it up. (Putting primary addresses in one column, secondary address – if any – in the next column, city, state and ZIP in their own columns… To bad you can’t do THAT in Excel!) Then it’s just a matter of saving the file as .csv, importing it into Access, exporting it as .txt, doing the same with the second file, writing and running an Easytrieve to match records, and saving the output. But the hard part was separating those bloody addresses! Thanks again. ---
**Author:** ![DMC](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dmc/32/18049_2.png) [@DMC](https://boards.straightdope.com/u/DMC)\ **Post date:** [March 26, 2008, 10:50pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/18 "2008-03-26T22:50:01Z")
[QUOTE=Johnny L.A.] To bad you can’t do THAT in Excel! [/QUOTE] You’re not going to like this a lot, but you can. 🙂 > [@](#): > > Thanks again. Glad to have helped. ---
**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:** [March 26, 2008, 11:37pm UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/19 "2008-03-26T23:37:04Z")
[QUOTE=DMC] You’re not going to like this a lot, but you can. 🙂 [/QUOTE] Acme Novelties\_\_\_C/O Zachary Smith\_\_\_123 Fake St\_\_\_PMB 999 Taxi Loco\_\_\_C/O Amalgamated Transport LLC\_\_\_ 789 Pseudo Rd These should be changed to: Acme Novelties\_\_\_123 Fake St\_\_\_PMB 999 Amalgamated Transport LLC\_\_\_ 789 Pseudo Rd In the first case, ‘Zachary Smith’ is the contact, so the primary address is moved to column B and the secondary address is moved to column C. In the second case the ‘LLC’ company is the actual business (as opposed to a DBA), so it replaces Taxi Loco in column A. The street address is moved to column B. How would Excel distinguish between a contact name and a business name? (And there are other things like addresses being swapped, too-long addresses, etc.) Not critical at this point. That’s easy enough to do by hand in Excel. I just needed to get rid of those non-displays so that I could make a ‘flat ASCII’ file for Easytrieve. ---
**Author:** ![tomndebb](https://avatars.discourse-cdn.com/v4/letter/t/b9e5f3/32.png) [@tomndebb](https://boards.straightdope.com/u/tomndebb)\ **Post date:** [March 27, 2008, 4:38am UTC](https://boards.straightdope.com/t/non-display-ascii-text-again/442740/20 "2008-03-27T04:38:57Z")
[QUOTE=Johnny L.A.] Heh. I was thinking that if only I had this data on a mainframe I could to HEX ON and see what it is. [/QUOTE] See if you can talk your boss into buying a copy of SPF/2. Even one seat in a shop can be pretty useful when you’re up against it. All the ISPF functions are available. (Macros are now written in C or C# rather than REXX.) I once saw a guy go to the SPF/2 web site and download a trial copy (did not permit edit, just browse) to identify a problerm similar to yours. I do not know whether they still allow folks to do that. PC and UNIX editors always seem to be twenty or thirty years behind ISPF. [Next page](https://boards.straightdope.com/t/non-display-ascii-text-again/442740.md?page=2)