phantom blank on right of field

W

wolfpack95

I am combining values in 3 columns and then using a vlookup to compar
two inputs and output the value if they're equal or output -500 i
they're not equal. I've tested my code on several inputs and it'
worked beautifully but on my last attempt, one input sheet has a colum
with a phantom blank on the right side of SOME of the data (I can se
it when I click the cell there's an extra space).

How it should work if values are equal:
Value 1: "NGA001CR_1_183_5"
Value 2: "NGA001CR_1_183_5"
Output: "NGA001CR_1_183_5"

OR if they're not equal
Output: "-500"

The problem is the rightmost value (shown above as 5). It can rang
from 0 to 15 and on values less than 10, the phantom blank SOMETIME
shows up on the right side.

The VLOOKUP formula that works consistently on data without the phanto
blank is:
=IF(ISNA(VLOOKUP(C15, D:D, 1, FALSE)), -500, VLOOKUP(C15, D:D, 1
FALSE))

I've tried the Clean function, Left function, concatenating, usin
Find-Replace Blank, formatting the cells as Text, General or Numbers
etc. etc. but Excel doesn't consistently recognize the blank excep
when it does the comparison and gives me erroneous results. Pleas
help and thanks in advance.:confused
 
D

DNF Karran

I presume the blank is a [space]- " " not ""

You could then do a find for a space and replace with a blan
(nothing)

Dunca
 
W

wolfpack95

Frank, thanks for directing me to it!

I post rarely but lurk and try to pick up as many tips as I can....jus
wanted to say thanks and I'm glad I found this board
 
Top