Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a LARGE volume of numbers to text in Excel for VLOOKUP
I have a VERY large table that I want to use in a VLOOKUP function. Th
first column of the table, if I understand correctly, needs to be i text format. It is currently all in number format, causing my VLOOKU function to not work properly. If I right click on the entire column and format it into TEXT format, the numbers all align over to the left appearing to have been converted to text. However, the VLOOKU function still doesn't work. BUT....if I find the value in the tabl manually, F2 it, and then hit ENTER, my VLOOKUP function then DOE work. It almost seems to be that it doesn't actually convert to text unless do this step (F2, enter). The problem is, there's over 40000 rows i this table, so I can't possibly do this to every cell in that firs column. Is there a macro (I'm macro-challenged) or some other way tha I can do this quickly and easily -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a LARGE volume of numbers to text in Excel for VLOOKUP
First, Your data doesn't have to be text. It's just that often your search
argument is sometimes text and sometimes numbers, that make the results looking inconsistent. So the easiest thing is to make sure your search argument is a number (if you're sure your table is all numbers). You can use the VALUE() function for that. If for some reason you insist on making your table text, introduce a (temporary) extra column, use the TEXT() function to convert to text, copy and replace the original with the results using Paste SpecialValues -- Kind Regards, Niek Otten Microsoft MVP - Excel "JoFlo " wrote in message ... I have a VERY large table that I want to use in a VLOOKUP function. The first column of the table, if I understand correctly, needs to be in text format. It is currently all in number format, causing my VLOOKUP function to not work properly. If I right click on the entire column, and format it into TEXT format, the numbers all align over to the left, appearing to have been converted to text. However, the VLOOKUP function still doesn't work. BUT....if I find the value in the table manually, F2 it, and then hit ENTER, my VLOOKUP function then DOES work. It almost seems to be that it doesn't actually convert to text unless I do this step (F2, enter). The problem is, there's over 40000 rows in this table, so I can't possibly do this to every cell in that first column. Is there a macro (I'm macro-challenged) or some other way that I can do this quickly and easily? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting a LARGE volume of numbers to text in Excel for VLOOKUP
It might be possible to avoid even needing a macro: have
you tried adding another column to the left and using the TEXT function to force the value to be seen as text; in other words something like: A1 = TEXT(B1,"#######") etc... Don't know if this would work but could be an easy fix. -----Original Message----- I have a VERY large table that I want to use in a VLOOKUP function. The first column of the table, if I understand correctly, needs to be in text format. It is currently all in number format, causing my VLOOKUP function to not work properly. If I right click on the entire column, and format it into TEXT format, the numbers all align over to the left, appearing to have been converted to text. However, the VLOOKUP function still doesn't work. BUT....if I find the value in the table manually, F2 it, and then hit ENTER, my VLOOKUP function then DOES work. It almost seems to be that it doesn't actually convert to text unless I do this step (F2, enter). The problem is, there's over 40000 rows in this table, so I can't possibly do this to every cell in that first column. Is there a macro (I'm macro-challenged) or some other way that I can do this quickly and easily? --- Message posted from http://www.ExcelForum.com/ . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting large numbers as text in Excel | Excel Discussion (Misc queries) | |||
Converting Text to Numbers in Excel w/ Additional Spaces | Excel Worksheet Functions | |||
Converting multiple numbers saved as text in excel | Excel Discussion (Misc queries) | |||
Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
Drawing a graph from a large volume of unusual data | Excel Discussion (Misc queries) |