Data with String/Text Value with Vlookup formula

[manggo]

Dear Master

I need your help please.

First sorry for my english.

I have coded database in excel, that mixed with text value format and number format.
I want to use vlookup formula, but I can't found the fastes way.
- to convert them to number format.
- or any other vlookup formula that can deal with my example


Would be very appreciate if you can see my example file I put on google drive, please.

StringVlookup1.xlsx - Google Drive

In my excel:
My coded data in colomn G and H

Green table is example if on number format. Everything is ok. No problem at all.

The simple way I can figure out is to convert my coded data to number. isnt it ?
But how do I do that ?
Copy, Paste Special, Value : not working.

Fyi The other way I did and worked (but if I have many colomn and thousands raw, this step would take long time.
copy all my coded data to notepad, then paste on new excel sheet, then do the vlookup formula.

Or, any other vlookup formula or other formula that can deal with my example ?

Any help would be very appreaciate.

Cheers
Manggo,

 

[Rick Rothstein]

Select all of Column A, right-click any cell in Column A and select "Format Cells..." from the pop-up menu that appears, click "General" on the Number tab for the dialog box that came up and then click the OK button, now select the Data tab of the Ribbon and click the "Text to Columns" icon on the "Data Tools" panel, click the "Finish" button on the dialog box as soon as it appears. Now do the same for Column G. Your "text numbers" will now all be real Excel numbers and you should now be able to do your VLOOKUP on them.

 

[manggo]

Hi Rick Rothstein

Yes. Thanks. Its worked.
But its only worked on one column right ?

 

[Rick Rothstein]

Hi Rick Rothstein

Yes. Thanks. Its worked.
But its only worked on one column right ?

Yes, a limitation of Text To Columns, which is why I said late in the text I posted "Now do the same for Column G".

 

[mart37]

Maybe this formula. De 2 last rows have a different result.
=INDEX($B$3:$B$23,MATCH(G3,$A$3:$A$23,0))

 

[mart37]

To convert text to value:
Enter in a cell number: 1
Copy that cell
Select all cells to convert (CTRL)
Choose by Paste for Paste Special
Choose Multiply