If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Text to values
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. |
#3
|
|||
|
|||
Text to values
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. |
#4
|
|||
|
|||
Text to values
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. |
#5
|
|||
|
|||
Text to values
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. . |
Thread Tools | |
Display Modes | |
|
|