2017-12-16

Excel - Trim formula not working - Solution

Excel - Trim formula is not working.  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:

=Trim(Substitute(Clean(A1),CHAR(160),""))

where:
A1 is the cell that needs to be trimmed.

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

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


Diagnostics:

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

=CODE(MID(A1,1,1))   Show leading spaces - code
=CODE(RIGHT(A1,1))   Show trailing spaces - code


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



Comments:
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. (As an aside, not a single spam has been allowed through; why bother?)