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?
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.
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.
I think I tried that (ALT + 013) without success. I’ll try it (again?) mañana.
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?
How do you do that?
What version of Excel?
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.)
Apparently, they call the function CODE() in Excel, instead of ASCII().
In any cell, type in:
=CODE("x")
replacing the “x” with your symbol inside of the quotes. This will give you the ASCII value of that symbol.
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.
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.
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.
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 would probably do the trick.
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! 
It must be something else.
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.
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.
You’re not going to like this a lot, but you can. 
Glad to have helped.
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.
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.