Missing Reference?

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

"The parawon" <[email protected]> wrote in message
news:[email protected]...
> 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

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

In article <[email protected]>,
[email protected] says...
> 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
>

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

In article <[email protected]>,
[email protected] says...
> 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
>