Oh Excel Why Must You Vex Me So

Excel is fabulous. It’s wonderful. It’s without doubt the most useful software tool I’ve ever used since I started using computers. But oh my can it be annoying. Right now I am trying use the TRIM() function to remove the leading spaces from some text strings. And it doesn’t work. Why doesn’t it work? Well because the strings I am trying to trim seem to start with ASCII character 160 rather than the regular ASCII 32 space character. I confirmed this with the Excel formula CODE(LEFT(A1,1)) where cell A1 contained the vexatious string in question.

ASCII 160 is the breaking space character and for whatever reason Excel displays it as a space but doesn’t trim it with the TRIM() function. Stupid. Stupid. Stupid Excel. So to trim the Excel string I need to do this:

TRIM(SUBSTITUTE(A1,CHAR(160),""))

Did I mention Excel was stupid?

This entry was posted in Software on by .

About markn

Mark is the owner and founder of Timesheets MTS Software, an mISV that develops and markets employee timesheet and time clock software. He's also a mechanical engineer, father of four, and a lifelong lover of gadgets.