#NA, cleaning data!

I am stumped. I am trying to complete a vlookup formula and keep getting a #NA result. I can get the Formula to work if I go to the lookup value, put my curser on the last digit and press delete. I have tried to use the clean function, left fuction, trim, and when I use the code funtion it returns 57. I'm out of idea's, any suggestions would be great.

Thanks

Originally Posted by pjoaquin

I'm guessing there is some nonprinting character in your cell that the CLEAN function does not cover (there are about 8 that CLEAN does not work on). To see how to remove them, you can go into Excel Help and search for "Remove spaces and nonprinting characters from text".

You could also do a find-replace all. In one of the problem cells, place your cursor to the right of your last visible character. Select any text to to the right of that point (shift+arrow right). Press CTRL+C to copy that text.

Open the Replace window, and in the Find What box, press CTRL+V to paste. In the Replace With box, do nothing. Click Replace All.

Hopefully that fixes your problem.

_________________________________________________________________

Thanks, this is very odd, but when I go to do the replace as you suggested, something odd happens........When I put my curser to the right of the last visible cell and do a shift arrow right, before I even go to replace the formula works. So I tried just going to the cell and putting the curser on the last visible charactre and do a control right arrow and the resulting formula works.....

I tried to do the edit replace but it was not successful. Thanks for the suggestion.

Troy

Originally Posted by Bryan Hessey

can you post your lookup formula as you are using it, and does the Lookup value come from a formula? - if so can you Lookup the INT( ) of that value?

the #N/A if using ,True is a value lower than the first item in the table, if using ,False is an item not found, code 57 is a number 9

does a =Len() show the correct length for the field?

---

________________________________________________________________

Here is the formula I am using (=VLOOKUP(D20,'MFAF points.xls'!A$2:$C$7231,3,FALSE)). The lookup value does not come from a formula, it is basically a list of zip codes which I am comparing to another list of zipcodes and service their corresponding service location. When I run a =Len formula it returns the correct number of characters.

Thanks,

Troy

Originally Posted by Carim

Hi,

Most probably, you are dealing with data downloaded from a web site with hidden spaces (called non breaking spaces) ... represented by =char(160) which all standard cleaning or triming functions ignore ...
The only solution is your VBA code to remove them ...

Thanks. The downside is I am a formula guy and VBA is not an option for me, nor do I have the resources availible to work through. It is very odd, after trying a few other actions, I have found that by simply putting my curser in the cell and hitting enter, it causes something to change in such a way that my formula then works.....

Troy