Results 1 to 4 of 4

Thread: Number stored as text in vlookup

  1. #1
    Join Date
    May 2009

    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. #2
    Join Date
    May 2008

    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. #3
    Join Date
    Apr 2008

    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. #4
    Join Date
    May 2008

    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.

Similar Threads

  1. How to convert number to text in excel
    By Jevin in forum Software Development
    Replies: 9
    Last Post: 17-05-2012, 06:42 PM
  2. Sum up a number of matches in Vlookup in Excel
    By Abélard in forum MS Office Support
    Replies: 2
    Last Post: 24-02-2012, 05:58 PM
  3. How to use VLOOKUP IF in cell that contains Text
    By RYAN$ in forum MS Office Support
    Replies: 2
    Last Post: 11-01-2012, 03:06 PM
  4. How to detect the number of files stored in Linux directory
    By Shanbaag in forum Operating Systems
    Replies: 2
    Last Post: 26-06-2009, 12:49 PM
  5. Replies: 6
    Last Post: 13-09-2008, 02:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts