trouble combining LEFT and MATCH

[jakeopolis]

[SOLVED]

Hi there,

I'm trying to use MATCH to match a specific value in column A with the same value in column B. However, the values in column A all have an extra 3 characters, starting with a decimal point. For example, A17 is the value "2190.HZ" and it needs to be matched with the value "2109" in column B. I am using this formula to truncate the value in A17:

=LEFT(A17,FIND(".",A17,1)-1)

Which properly returns "2109." However, when I use MATCH on the value returned from above, I get an error.

So for example, my sheet is setup like this:
A17=2109.HZ
B26=2109
C1=LEFT(A17,FIND(".",A17,1)-1)
D1=MATCH(C1,B17:B28,0)

And cell D1 returns an error. Any ideas?

Thanks!

[Jonmo1]

The value returned by C1 is a TEXT String (even if it appears numeric) because the Left function returns TEXT.
But it's likely the values in B17:B28 are actually numbers.

Try changing C1 to
=LEFT(A17,FIND(".",A17,1)-1)+0

[jakeopolis]

The value returned by C1 is a TEXT String (even if it appears numeric) because the Left function returns TEXT.
But it's likely the values in B17:B28 are actually numbers.

Try changing C1 to
=LEFT(A17,FIND(".",A17,1)-1)+0

yes!! that totally works, thank you. i am confused as to why adding that +0 solves it; could you explain?

[Jonmo1]

Glad to help, thanks for the feedback.
When excel performas any math operation like + - * and /,
it attempts to convert any text string that looks like a number into a real number.

[jakeopolis]

Glad to help, thanks for the feedback.
When excel performas any math operation like + - * and /,
it attempts to convert any text string that looks like a number into a real number.

ah, makes sense. very cool! Thanks for your extremely quick help!