# 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

#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?MIKEhttp://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.MIKEhttp://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 B1) 5999 - Site Preparation 003D-999-02-C111Shaun

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

Report •

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

Report •