Formatting instead of formula

[Exceladd1ct]

Hello,
I have to vlookup some values in a table where the lookup column has rows with text and rows with numbers stored as text.

In order to get the right formatting so that the lookup works, I use the following formula that converts "numbers stored as text" to numbers and ignores the text rows.

=IFERROR(NUMBERVALUE(A1);A1)

Is there a way to apply formatting to achieve this instead of formula?

Thank you.

 

[Tivakaran]

you could use formula =A1*1 which will read the text as number

 

[Peter_SSs]

Try this

• Put a 1 in a vacant cell & copy that cell
• Select the column of data that consists of numbers and numbers stored as text
• Paste Special ... -> Multiply -> OK