Data with String/Text Value with Vlookup formula

manggo

New Member
Joined
Jul 29, 2017
Messages
18
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,
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 29, 2017
Messages
18
Hi Rick Rothstein

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,675
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 4, 2017
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
Maybe this formula. De 2 last rows have a different result.
=INDEX($B$3:$B$23,MATCH(G3,$A$3:$A$23,0))
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
615
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,355
Messages
5,595,680
Members
414,008
Latest member
SNesbyCarr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top