Search number values in texts and text in number values
Sometimes you need to search number values in texts and text in number values. This situation occurs when you have separate lists of same values but as text in one list and number in the other. There is a quick solution to overcome this annoying situation.
If excel evaluates a number value as a text in one list and number in another list, your lookup formulas will not match them. You can’t use lookup formulas as you always do. Therefore, you can’t make necessary calculations.
Here is an example of this situation:
We have 2 lists that has same item numbers. Item numbers are in text format in one list and number format in the other. We need to check stock availability for our orders but order numbers don’t match because of their different data types.
If you try to fetch stock quantities next to orders with a VLOOKUP formula, your formula will result with an error:
Likewise, if you try to fetch order quantities next to stocks with a VLOOKUP formula, your formula will result with an error:
Because Excel can’t search number values in texts and text in number values. But there is a workaround for both situations.
First one is the easy one. If your numbers are formatted as text, use them in a NUMBERVALUE() function inside VLOOKUP formula and Excel will evaluate it as number. You will be searching for number in numbers.
Second one is similar but a little bit trickier. If you need to search for a number in text values, use them in a TEXT() function inside VLOOKUP and Excel will evaluate it as text. You will be searching for text in text values.
You need to specify a text format in your TEXT function, so you need to write it as TEXT(A2;”#”) for Excel to recognize it as a plain text.
With the information above, you can easily search number values in texts and text in number values in Excel.