How to convert text to number?

[frenchtoast]

I have rows of data.

ie. '12345
and I want to use vlookup using number value 12345.

There isn't an error message (with pull-down arrow) to convert this '12345
into a number.

How do I convert this without having to manually change these cells?

Thanks!

 

[frenchtoast]

I don't quite understand. Is A2 the cell with '12345? What is the data/text
to column function?

The apostrophe in '12345 appears to be typed in (hence no error message).

 

[Jim Thomlinson]

Add a 0 (zero) to any blank cell. Copy the zero. Select the text. Right
click. Paste Special -> Add. The text will be converted to numbers...

 

[Chris Lavender]

Hi frenchtoast

Assuming your 12345 is in cell A21, the TEXT function will convert it to
text format, which will then match the format type in your data

=VLOOKUP(TEXT(A21,"0"),C,2,0)

HTH
Best rgds
Chris Lav

 

[frenchtoast]

Hi Jim,

Thanks. I tried this, and the vlookup worked. Except in the cells where I
Pasted Special the 0, those cells are now 0. I need these cells to not
change. Any thoughts?

 

[frenchtoast]

Hi Chris.

I tried this as well, though it returned #N/As.

 

[frenchtoast]

Hi David,

I tried the Data to Text command. This works except where the cells contain
'00012345 it converted to 12345. I need to keep 00012345 displayed. Do you
know how this can be done? (The vlookup worked).
Thank you.

 

[frenchtoast]

Thank you!

Format the cell as 00000000

 

[Zuj]

Dear All

can you tell me how can i covert (1/1,OK,0) into (1/1). i want the (OK,0) to
be removed completely from the field.

 

[David Biddulph]

It isn't very obvious how your message relates to the earlier part of the
thread, but try:
=SUBSTITUTE(A1,",OK,0","")