2 Identical looking text cells that are not EXACT

[spcw]

For a vlookup_value I concatenate a cell and text string. The lookup value becomes "Total ABC", LEN=9. The value from the array is "Total ABC", LEN=9. But comparing these 2 cells, they are not EXACT. How can I find the difference between the two so I can concatenate the correct lookup value?

Thanks,
Brad

[newdoverman]

I suspect that the = is not the same in both cells. Select the = sign from both cells, copy and paste them into vacant cells. Then, use =CHAR(cell reference) on each cell to see if the CHAR returns the same value.

[spcw]

Hi Newdoverman,

The text values don't have an equal sign. It is simply ... Total ABC.
Both cells have the same length, LEN=9, so there don't seem to be any hidden spaces.

Thanks,
Brad

[Hang Glider]

you might have a formatting issue - in your concatenation source or your array - that prevents the match. Or an extra space at beginning or end of either (array or source). Happens to me plenty.

[newdoverman]

Select the SPACE instead as there can be many different characters that produce a space. One way to test the SPACE is to copy the space from one of the cells and paste it into the space of the other cell.

To eliminate the possibility of spaces before or after the texts, use =TRIM(CELL REFERENCE) then compare the two.

[spcw]

Solved the problem... whatever it was. I copied the <Total > from the array into my concatenate formula. Looked the same to me but oh well.

[spcw]

Thanks, NDM!