

 Thread Tools  Search this Thread 
#1
 
 
Number stored as text in vlookup
I has created a list of customers in Ms Excel. It is an sheet which gives information of the address of customers located in different areas. Normally the data has a mixed characters of alphabets and numbers. Here there is problem in the the column of customer id. I tried to correct it but no results. It is showing a small marks at the end of cell denoting numbers are stored as text. While copying the information to certain place the numbers changes like 2.30 goes to 2.3. This makes a big difference. What is the way to display the numbers as text only excel sheet and restore back to numbers if don't required. Reply. 
#2
 
 
Re: Number stored as text in vlookup
What you had mentioned by this I can figure out that the customer column of worksheet is formatted as General and not as Text. So your data has mixed up with numbers and text. Excel consider numbers and text differently. I will recommend you to first use my solution in a sample worksheet. Make different copy of you sheet and insert =IF(T(cell ref.)="",TEXT(cell ref.,"#"), cell ref.). Cell ref means the address of your cell like F12 or D10. By this values will be displayed in text. Just copying and pasting the text only values over the original data it will be converted automatically. 
#3
 
 
Re: Number stored as text in vlookup
The main reason behind your problem is that when your import or insert an external data they are left aligned. You can see numbers in the cell but excel see it as a text. The simple way to convert cell to a proper number quickly by highlighting your non functioning number click on the exclamation marks on the green box and select " Convert to Numbers". If you have an older version of excel then copy a empty cell. Select the list of text numbers. Use paste special from the edit menu. Perform Paste=values, operation=add. Then after that by applying a math operation on the text numbers the result will change to number. 
#4
 
 
Re: Number stored as text in vlookup
As per I believe your main problem is that in the one part your are having both numbers and text. While in the other or result you don't want. I got rid of this issue by embedding a vlookup statement in a IF statement. For example lets take that in your worksheet the first column has an list of your customer's id and the second one is area. And in the second worksheet in the first column the same id's and the second has other information. Now try the formula : =IF(ISNA(VLOOKUP(A1,'Sheet1'!$A$1:$B$4,2,FALSE))=TRUE,VLOOKUP(VALUE(A1),'sheet1'!$A$1:$B$4,2,FALSE), VLOOKUP(A1,'sheet1'!$A$1:$B$4,2,FALSE)) It is based on numeric value. 

Tags: array, excel, number, office, stored, text, vlookup 
Thread Tools  Search this Thread 

Similar Threads for: "Number stored as text in vlookup"  
Thread  Thread Starter  Forum  Replies  Last Post 
How to convert number to text in excel  Jevin  Software Development  9  17052012 07:42 PM 
Sum up a number of matches in Vlookup in Excel  AbĂ©lard  MS Office Support  2  24022012 06:58 PM 
How to use VLOOKUP IF in cell that contains Text  RYAN$  MS Office Support  2  11012012 04:06 PM 
How to detect the number of files stored in Linux directory  Shanbaag  Operating Systems  2  26062009 01:49 PM 
NOW WHAT? "The maximum number of secrets that may be stored in a single system has been exceeded"  aloha  Vista Help  6  13092008 03:15 AM 