Empty cell contains invisible Object

**jomili** · 11-14-2011, 02:00 PM

I've running a vlookup on two cells added together. The gist is: There's always EITHER a contract, or an HCATS Contract, but never both. So, my lookup is the Contracts column added to the HCATS Contract column. This works well for 99% of my contracts. However, I have some situations like the attached, wherein my contract ID & the empty cell next to it add up to something different than my contract ID, though to all intents and purposes it appears the same. My vlookup won't resolve it. I'd appreciate any help I can get in resolving this one. This is only one example of about 1000 contracts, and I have to refresh my formulas every day, so need to figure this one out.

I appreciate any help you can provide.

Thanks,
John

**shg** · 11-14-2011, 02:22 PM

You are comparing a number and a text string that looks like a number. They will never be equal; all text strings are greater than all numbers.

**jomili** · 11-14-2011, 03:37 PM

Why is one a number string and one a text string? This is only one set of cells out of hundreds, and in all of the others I'm doing the same thing, adding the two cells together. In all of the cases the result is usable for my VLookup. So why is this set different?

**shg** · 11-15-2011, 06:30 PM

Catenation (what the ampersand operator does) always returns a string. Some functions (like COUNTIF) don't distinguish between numbers and text that looks like numbers; Lookup functions do.

If the result will always be numeric text, and the result you want is a number, do =--(A1&B1)

**jomili** · 11-15-2011, 06:36 PM

No, it won't always be numeric. But what's driving me crazy is that I have two columns. In column A I have a real number, in column B I have a blank cell. I concatenate the two, and the result is a text number (sometimes) or a real number (sometimes). So there appears to be something in some of the blank cells that changes my real numbers to text numbers.

I've solved it by multiplying the entire range by 1, which converts my text numbers to real numbers, so it's no longer bothering me, but I sure would like to know the "why" of it.