Saturday, December 16, 2017

Excel - Trim formula not working - Solution

Excel - Trim formula is not working.  Solution for leading or trailing spaces not being removed from Trim.  Other formulas are working correctly.

For example, =Trim(A1) was not working, or was not working as expected.  Leading or trailing spaces remained.

Likely Issue:
The "space" character is not a true (ascii 32) space.  It is a non-breaking-space, probably from MSWord.

Likely Solution:


A1 is the cell that needs to be trimmed.

CHAR(160) is a "non-breaking space"
In unicode:  U+00A0
Also may be displayed as  a acute (a')

Also known as hex 20 A0, \u00A0,  &#160


The code can be discovered with these formulas, the first for leading spaces, the second for trailing:


"Mid-string, starting at position 1, for a length of 1"

For a while, I thought this was a problem with the new version of Excel that was recently installed.

Some browsers detect this as white-space and others do not.  This may be dated information, but IE 7 and 8, and Safari 3.2 do not treat this as a white-space-character.  Source.

Vaguely related articles:
Excel - Formatting Phone Numbers
Excel - Dates showing as 1905

Excel - Parsing First and Last Names
Excel - Parsing City-State-Zips
Excel - Return First / Last Word  - SuperTrim

No comments:

Post a Comment

Comments are moderated and published upon review.