Trim function not working on trailing space

[WxShady13]

I have a list of Employee numbers (varying from 4-6 digits) and they have a trailing space on all of them. I have tried the Trim function, and find/replace but nothing will remove the space. I have tried formatting them as General, Numbers and Text but I cannot get the space removed.

 

[Joe4]

Where did this data come from? The Web or another program?
Many times such data has "special characters" which are not visible which are not regular spaces (could be things like non-breaking spaces, carriage returns, etc).

You can find out what exactly the last character in any blank cell is by entering this formula in any cell and seeing what it returns:

=CODE(RIGHT(A1,1))

(this example is checking cell A1, adjust according).

The formula will return the ASCII code for that last character. What number does it return for you?

 

[WxShady13]

Where did this data come from? The Web or another program?
Many times such data has "special characters" which are not visible which are not regular spaces (could be things like non-breaking spaces, carriage returns, etc).

You can find out what exactly the last character in any blank cell is by entering this formula in any cell and seeing what it returns:

=CODE(RIGHT(A1,1))

(this example is checking cell A1, adjust according).

The formula will return the ASCII code for that last character. What number does it return for you?

The data comes from our Oracle DB. When I pull a different type of report it doesn't have the trailing space on the Emp ID so that is why I am confused as to why it is there now. When I use that code it returns 160

 

[Joe4]

Yep! Character 160 is a "non-breaking space".
See here: How to Remove Non-breaking Spaces in Excel Data - Get Rid of ALL Stubborn Spaces

 

[WxShady13]

Yep! Character 160 is a "non-breaking space".
See here: How to Remove Non-breaking Spaces in Excel Data - Get Rid of ALL Stubborn Spaces

YOU ARE A GENIUS AND A GODSEND!!!!

 

[Joe4]

Glad I was able to help!