A missing reference, maybe?

[The parawon]

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

 

[nbrcrunch]

Actually you can use TRIM just as you suggest, without it affecting
formulas that reference the text cells....

1. write the trim formula in another column, referencing the cells
with extraneous spaces.

2. Copy the trim formula cells then paste/special/values-only back on
top of the original text values.


Also,
Yes, you can use Find/Replace to find spaces. Problem is it will
eliminate all spaces, even between words.

 

[Ola]

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)