Special characters: 160 versus 32

I ran across a new variation on hidden characters. In the past, I have written about invisible characters such as an “insert line” in this post. I wrote about how to use the function “code” to get at the hidden character and how to use function “char” to create that hidden character. I also may have done a post (I just can’t find; I might have dreamt of writing it) on using functions “trim” and “clean” to get rid of hidden characters, namely blanks.

Just as an aside, you don’t have to use “code” to search for hidden characters. You can use it to find a code for a particular character you see on your download but it is not found on your keyboard. Why would I want to know the code for a strange character? Well, you might want to get rid of it if it is part of a string of characters that you want to divide up and that strange character is sitting in the middle. For example, once a month I receive a spreadsheet where one column contains a list of GL codes and GL descriptions combined into one column. I really want the GL code in one column and the description in another. I can generally use “left” and “right” functions but there is this peculiar little dot in the middle separating the GL account and the description. Through a combination of  “code”, “char”, “search”, “left” and “right” functions, I end up separating the GL account and the description while getting rid of that strange character.

This week I ran into a new kind of hidden character: a variation on the blank character. This oddity came from a “copy and paste” from the government site. I could tell that there was a blank in front of the state name. I wanted to do a “vlookup” on the government information to pull in data into my database of information on states. So, in my spreadsheet, I did a formula to catenate the blank in front of the state name in the database so that I could use a “vlookup” to pull in the government information.

[divider]One problem: my “blank” plus state did not equal the government’s “blank” plus state.[/divider]
[divider] [/divider]

I did a test where my “blank” plus state = government’s “blank” plus state turned up false. My “blank” which was a spacebar blank was not the same as the government’s “blank”. After using the function “code”, I learned that my spacebar blank gives a code of 31 whereas the government’s was 160. Also, using functions “trim” or “clean” on the government’s data did not get rid of the “blank”. So, in order to use “vlookup” on this particular government’s data, I had to use “char(160)” to create the blank in front of the state’s name.

There is always something new to learn, although this is rather archaic and unusual.