Text to values

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.

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
--
Max
Singapore
---
"Nadine" wrote:
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.

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

On Apr 21, 11:33*pm, Nadine wrote:
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.

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.

"Max" wrote:

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
--
Max
Singapore
---
"Nadine" wrote:
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.

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.

"Pete_UK" wrote:

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

On Apr 21, 11:33 pm, Nadine wrote:
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.

.