Missing Reference?

[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

 

[Fredrik Wahlgren]

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

How did you get something like "Sales " into the cell in the first place?
Did you import it from a database?

/Fredrik

 

[Tushar Mehta]

You could use the array formula
=INDEX($B$2:$B$3,MATCH(C2,TRIM($A$2:$A$3),0))

where column A contains the names (with leading/trailing spaces) and B
contains the result value you want returned.

--
An array formula is entered with CTRL-SHIFT-ENTER rather than just
ENTER. If done correctly, XL will display curly brackets { and }
around the formula

Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Tushar Mehta]

I see I just wasted my time helping you since you have already received
multiple answers to all the multiposts you made on the topic.

What a wonderful way to waste world-class volunteer resources -- and at
no cost to you!

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions