I have 2 columns I need to confirm contains the same document number the first column has 5999 the second column has it in an alpha numeric sequence 003D-999-02-C111. The document number I need to confirm is the same is the "999".

Are both your numbers ALWAYS the same length?

4 characters and 16 characters.Does the alpha numeric sequence ALWAYS show your target number beginning with the sixth character?

Is your target number ALWAYS the last three digits?MIKE

Mike, The 4 characters will be the same length 95% of the time the 5% they will be 3 characters. The 16 characters will be the same length 75% of the time with 25% of the time the characters being 22 characters with them always ending in -V.

The alpha numeric sequence will always show the target numbers begging with the sixth character and yes they will always be the three digits.

Shaun

With your data looking like this: A B 1) 5999 003D-999-02-C111In C1 enter the formula:

=IF(ISERROR(FIND(RIGHT(A1,3),B1,5)),"No Match","Match")

See how that works for you.

MIKE

Thanks Mike, it works however I found some of the data in the column A with the 4 digit number have a description like "5999 - Site Preparation" the rows with the description it's giving me a no match even though the numbers match. How can I update the formula to get the formula correct this. Here is what it looks like.

A B

1) 5999 - Site Preparation 003D-999-02-C111Shaun

With the first four characters of your Target string: 5999 Site Preparation

try this:

=IF(ISERROR(FIND(RIGHT(LEFT(A1,4),3),B1,5)),"No Match","Match")

MIKE

Thanks Mike worked perfect!

Ask Your Question

Weekly Poll

Would you ride in a self-driving car from Tesla?

Discuss in The Lounge

Poll History