Forum Discussion

FClifton's avatar
FClifton
Copper Contributor
Nov 07, 2019

Number entered as text in text formatted cell

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 tex...
excel
Formulas and Functions
PReagan's avatar
PReagan
Bronze Contributor
Nov 08, 2019

Hello FClifton,

 

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

  • FClifton's avatar
    FClifton
    Copper Contributor
    Nov 08, 2019

    HelloPReagan ,

     

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

     

    Frank

    • Patrick2788's avatar
      Patrick2788
      Silver Contributor
      Nov 08, 2019

      FClifton 

      I think the bolded part is what you're interested in. This formula will check for number or text.

       

      =IFERROR(VLOOKUP(TEXT(A2,"0"),table_array,2,0),VLOOKUP(A2*1,table_array,2,0))

    • PReagan's avatar
      PReagan
      Bronze Contributor
      Nov 08, 2019

      FClifton 

       

      Maybe the following will be helpful:

      https://exceljet.net/formula/vlookup-with-numbers-and-text

    • mathetes's avatar
      mathetes
      Silver Contributor
      Nov 08, 2019

      FClifton 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)

Resources