Excel formula to identify certain numbers

September 12, 2011 at 11:29:52
Specs: Windows 2007, Pentium 4 2GHz / 4 gigs
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".

See More: Excel formula to identify certain numbers

Report •


#1
September 12, 2011 at 13:39:34
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

http://www.skeptic.com/


Report •

#2
September 12, 2011 at 14:16:32
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


Report •

#3
September 12, 2011 at 14:30:20
With your data looking like this:

     A           B
1) 5999    003D-999-02-C111

In C1 enter the formula:

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

See how that works for you.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
September 12, 2011 at 14:49:24
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-C111

Shaun



Report •

#5
September 12, 2011 at 15:22:23
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

http://www.skeptic.com/


Report •

#6
September 12, 2011 at 15:32:30
Thanks Mike worked perfect!

Report •


Ask Question