Space at end of Text character

[dsforbes]

I have a column of ticker symbols that are three, four or five characters long, exported from third party source. The export places a hard coded space at the end of each symbol. TRIM and find and replace do not remove the space. Any other ideas.
I don't know how to do a nested if statement based on the length, using the right command. Currently I use a helper column and then do three separate formulas and I need to do this daily. Can anyone help. THANK YOU.

ABC
AMT
CCK
CREE
DLR
LSTZA

 

[jeffreybrown]

Try this...

=TRIM(CLEAN(SUBSTITUTE(A7,CHAR(160)," ")))

http://www.rondebruin.nl/clean.htm

 

[dsforbes]

genius, it worked. Not sure I even need to understand why. THANK YOU SO MUCH

 

[jeffreybrown]

Glad it worked for you