issue with Match formula

[Eurekaonide]

Dear All
I have a formula in one cell which is doing this, =8260&RIGHT(F6,6) and returns say this as a result 8260159860 then next to it I have a formula stating =MATCH(X6,'sheet1'!C:C,0) but it returns an error - if I manually type in the number 8260159860 it returns a figure correctly - I have checked that all the formats are number and tried it on general too Ive checked for any spaces or funny characters but I just cant see why it would do this. I also tried doing and =(The cellB4) to try and reformat etc and this doesn't work either any ideas what is going wrong please? Many thanks as always

 

[Ron Coderre]

It appears that Col_C on Sheet1 contains numbers and your concatenation formula returns text.
Try either of these approaches:

=--(8260&RIGHT(F6,6))

or

=MATCH(--X6,Sheet1!C:C,0)

The double-minus coerces numeric text into numbers.
You could also: Add zero or multiply by 1, but the double-minus has only one purpose...to convert numeric text to numerics.

Does that help?

 

[Misca]

When you're combining strings (RIGHT is a text function and returns a string) to other strings or numbers you'll always end up with a string. The easiest way to convert strings to numbers would be using the double negative.

If you want to check it out yourself type 8260159860 into a cell (A1) and your formula into another one (B1). Then check if A1=B1. Excel says they're not. Next check if A1=--B1 and you should get TRUE.