Text to values

[Nadine]

I need to convert cells of data to values. The data is the result of the
following formula: =LEFT(D2,SEARCH(" ",D2)-1). Example of data results:
1099r, 1073, 10866c, 09R4, etc. I need Excel to see them as values in order
for a formula to recognize them. It doesn't matter if the cell is formatted
as general, text, or number. This is in Excel 2003.

Thanks so much.

 

[Max]

Some thoughts. You can actually slant everything to be text comparisons
instead of numeric comparisons. From the sample data that you posted, that
seems to me the better route. LEFT per se will return everything as text, so
those returns are fine n ready for you to compare as text. What you need to
do is to switch the other formula to be text comparisons, eg compare other
lookup values as text. Eg: use = vlookup(A2&"", ...) instead of =vlookup(A2,
....) if A2 might contain real numbers. Any worth? hit YES below

 

[Pete_UK]

Do you mean that you want to fix the values returned by the formula,
so that you no longer have the formula, or do you mean that you want
the numeric characters from the result of your formula (only), so that
09R4 would become 94, and 1099r would become 1099?

Pete

 

[Nadine]

Thanks Max. Yes, I have changed my match formula to text so it has worked.
I didn't know how to do that with the vlookup so thanks for sharing that.
Unfortunately vlookup takes up much too much disk space with the large file
I'm using. Thanks again for the help.

 

[Nadine]

The answer is I don't want to do either of those. I originally wanted the
text value of 1099r to match a value in another worksheet which also showed
1099r but one was stored as text and one was stored as numeric. Look at
Max's response. He's suggesting instead of trying to change the text value
to numeric to instead change the numeric value to text. This way the MATCH
formula can match it and give me the result I'm looking for. Thanks for
responding.