Number entered as text in text formatted cell

Question

I have a column where the entire column was selected and the format specified as text. However, when I enter data that looks like a number, e.g. 12345, I get the warning about a number entered as text.&nbsp; That entry is used in a different cell in a VLOOKUP formula.&nbsp; The VLOOKUP fails until I change the data to be 'real' text by using Data | Text to Columns.&nbsp; What can I do to make the data I enter be treated fully as text?&nbsp; Thanks!

preagan · Answer

Hello FClifton,&nbsp;Entering a single apostrophe, "'", before your data (i.e. '12345) will cause your data to be stored as text.

fclifton · Answer

HelloPReagan ,&nbsp;Yes, that does enter the number as text, but the apostrophe, "'" is in the cell and makes the VLOOKUP not work.&nbsp; The VLOOKUP needs to be just text as that is what it is in the table which is being searched.&nbsp; But thanks for the suggestion!&nbsp;Frank

mathetes · Answer

FClifton&nbsp;is it possible to rethink the design here? It’s generally a good idea (for avoiding the kind of problem you’re encountering) for the lookup reference to be at the very least consistent in nature (text OR numeric, but not both) and short. Some kind of code can be ideal ( so long as not confused with Cell addresses)

preagan · Answer

FClifton&nbsp;&nbsp;Maybe the following will be helpful:https://exceljet.net/formula/vlookup-with-numbers-and-text

patrick2788 · Answer

FClifton&nbsp;I think the bolded part is what you're interested in. This formula will check for number or text.&nbsp;=IFERROR(VLOOKUP(TEXT(A2,"0"),table_array,2,0),VLOOKUP(A2*1,table_array,2,0))

Forum Discussion

Number entered as text in text formatted cell

5 Replies

Resources