A missing reference, maybe?

[Guest]

I'm looking for a better way to get rid of the spaces directly before and
after the values in my cells. When I double click the values, I see the
cursor blinking one space to the right of the data, like this: 'Sales . The
period is where the cursor sits blinking. Without double clicking the cell,
it says: Sales No cursor appears on the unactivated cell (obviously). I
know about the "TRIM" function in order to get rid of before and after
spaces, but I don't want to have to move the data around in order to do a
vlookup. I tried using Find & Replace, but that only helps me find values
that are not formula. I believe I might have a missing reference. Does
anyone know which reference would make Find and Replace work for formulas?
Is there a way to replace the spaces on the outside?

Any push in the right direction would be very appreciated.

 

[Ken Wright]

Try here:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

It's not pretty but it works (might be a start...?):

TRIMLEFT: =RIGHT(H4,LEN(H4)-MIN(IF((MID(H4,ROW(INDIRECT("1:"&LEN(H4))),1)<>"
"),(ROW(INDIRECT("1:"&LEN(H4)))),""))+1)
TRIMRIGHT: =LEFT(H4,MAX(IF((MID(H4,ROW(INDIRECT("1:"&LEN(H4))),1)<>"
"),(ROW(INDIRECT("1:"&LEN(H4)))),"")))

TRIMLEFT&RIGHT: Combine the two

Ola Sandstrom


Note:
Since it's an Array formula you have to end the formulas by holding down
Ctrl+Shift and then press Enter.
It will work if " " is Not found both at the beginning and at the end.
My idea was to find the Min position <>" " (trimleft) and the
Max...(trimright)