Convert Number to Text (to enable vlookup)

**kenny.fsw** · 06-26-2013, 06:50 AM

Good morning,

I would like to make a macro to turn selected data from number to text.

Basically I have two sheets, one is data imported from a database that has a reference stored as text. The other is a sheet with that reference number on it, but stored as a number.

This reference is for the most part alpha numeric, but there is a small percentage that are just numeric stored as text - there is no fixed character length to these and no number begins with 0.

When I lookup from one sheet to the other I get a n/a, in order for it to read I have to change the format to text and hit f2, enter, f2, enter.......

This isn't a problem when I look at the report on a weekly basis as there is 20-30, but I need to look at the last 3 years and there is thousands.

I am looking for something to either automate the F2, Enter for a range of cells or maybe you know of a more efficient way to do this.

Any help would be greatly appreciated.

Kenny

**JasperD** · 06-26-2013, 06:57 AM

Hi Kenny,

try this

Please Login or Register  to view this content.

Please click the * below if this helps

**patel45** · 06-26-2013, 06:59 AM

convert numbers with TEXT function

**TMS** · 06-26-2013, 07:05 AM

If your formula is:

=VLOOKUP(A2, Sheet1!A:B,2,FALSE)

try:

=VLOOKUP(A2&"", Sheet1!A:B,2,FALSE)

or, real belt and braces:

=IFERROR(VLOOKUP(A2,$F$2:$G$8,2,FALSE),IFERROR(VLOOKUP(A2&"",$F$2:$G$8,2,FALSE),""))

So, that tries the value as is; if there is an error, it tries it converted to text; and, if it still fails you get a blank. You could convert that to an error message.

Regards, TMS

Regards, TMS