Number stored as text in vlookup

[Alexander102]

Sponsored Links

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.

[Raine]

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.

[Eric B]

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.

[Glenny]

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.