Go Back   TechArena Community > Software > Windows Software
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Number stored as text in vlookup

Windows Software


Reply
 
Thread Tools Search this Thread
  #1  
Old 09-06-2009
Member
 
Join Date: May 2009
Posts: 48
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.

Reply With Quote
  #2  
Old 09-06-2009
Member
 
Join Date: May 2008
Posts: 3,312
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.
Reply With Quote
  #3  
Old 09-06-2009
Member
 
Join Date: Apr 2008
Posts: 4,641
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.
Reply With Quote
  #4  
Old 09-06-2009
Member
 
Join Date: May 2008
Posts: 4,564
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.
Reply With Quote
Reply

  TechArena Community > Software > Windows Software
Tags: , , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


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 17-05-2012 06:42 PM
Sum up a number of matches in Vlookup in Excel Abélard MS Office Support 2 24-02-2012 05:58 PM
How to use VLOOKUP IF in cell that contains Text RYAN$ MS Office Support 2 11-01-2012 03:06 PM
How to detect the number of files stored in Linux directory Shanbaag Operating Systems 2 26-06-2009 12:49 PM
NOW WHAT? "The maximum number of secrets that may be stored in a single system has been exceeded" aloha Vista Help 6 13-09-2008 02:15 AM


All times are GMT +5.5. The time now is 02:51 AM.