matching a format in a lookup
 

In column A I have numbers in different formats. Some 6 digits alpha
numeric, some are 6 digits followed by 3 digits and separated with a dash
(6-3). Another group is similar: 2 digits - 5 digits - 2 digits (2-5-2). In
column B I'd like to insert a function (e.g. IF statement) that would respond
(yes, etc) when the adjacent cell in column A contained a 2-5-2 number. Does
anyone have a idea of how to do this? Thanks.

matching a format in a lookup
 

One way:

=IF(AND(ISNUMBER(-SUBSTITUTE(A1,"-","")), MID(A1,3,1)="-",
MID(A1,9,1)="-"), "yes", "no")

This will work for the examples given. However, note that it will also
return "yes" for a number pattern 2-2-2-2.



In article ,
dbs wrote:

In column A I have numbers in different formats. Some 6 digits alpha
numeric, some are 6 digits followed by 3 digits and separated with a dash
(6-3). Another group is similar: 2 digits - 5 digits - 2 digits (2-5-2). In
column B I'd like to insert a function (e.g. IF statement) that would respond
(yes, etc) when the adjacent cell in column A contained a 2-5-2 number. Does
anyone have a idea of how to do this? Thanks.

matching a format in a lookup
 

Might this work...

=IF(AND(ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="-",ISNUMBER(VALUE(MID(A1,4,5))),MID(A1,9,1)="-",ISNUMBER(VALUE(RIGHT(A1,2)))),"Yes","No")
--
Ken Hudson


"dbs" wrote:

In column A I have numbers in different formats. Some 6 digits alpha
numeric, some are 6 digits followed by 3 digits and separated with a dash
(6-3). Another group is similar: 2 digits - 5 digits - 2 digits (2-5-2). In
column B I'd like to insert a function (e.g. IF statement) that would respond
(yes, etc) when the adjacent cell in column A contained a 2-5-2 number. Does
anyone have a idea of how to do this? Thanks.

matching a format in a lookup
 

Thank you. My 2-5-2 is actually an alphanumeric so I had to tweak your
solution. But it worked great!! Thanks.

"JE McGimpsey" wrote:

One way:

=IF(AND(ISNUMBER(-SUBSTITUTE(A1,"-","")), MID(A1,3,1)="-",
MID(A1,9,1)="-"), "yes", "no")

This will work for the examples given. However, note that it will also
return "yes" for a number pattern 2-2-2-2.



In article ,
dbs wrote:

In column A I have numbers in different formats. Some 6 digits alpha
numeric, some are 6 digits followed by 3 digits and separated with a dash
(6-3). Another group is similar: 2 digits - 5 digits - 2 digits (2-5-2). In
column B I'd like to insert a function (e.g. IF statement) that would respond
(yes, etc) when the adjacent cell in column A contained a 2-5-2 number. Does
anyone have a idea of how to do this? Thanks.