Check string in cell

[KGee]

Is there a formula I can use to check a cell for a specific format?

For example, I want to check that the first three characters are letters, followed by four numbers. So the first example below would be a positive match and the second would not. Similar to using "format" in VBA.
ABC1234
ABCD123

 

[shyy]

Try this

Excel 2010

	A	B
1	ABC1234	TRUE
2	ABCD1234	FALSE
3	1234ABCD	FALSE

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =AND(IF(ISNUMBER(VALUE(LEFT(A1,3)))=TRUE,0,1),ISNUMBER(VALUE(MID(A1,4,4)))) B2 =AND(IF(ISNUMBER(VALUE(LEFT(A2,3)))=TRUE,0,1),ISNUMBER(VALUE(MID(A2,4,4)))) B3 =AND(IF(ISNUMBER(VALUE(LEFT(A3,3)))=TRUE,0,1),ISNUMBER(VALUE(MID(A3,4,4)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[circledchicken]

shyy, I think your solution may fail where there is something like this for example:

AB12345

KGee, another alternative to try might be something like this:

=AND(
    ISNA(
      LOOKUP(
        9.99999999999999E+307,
        -MID(A1, ROW(INDEX(A:A, 1):INDEX(A:A, 3)), 1))),
    ISNUMBER(-MID(A1, 4, 4)))

This checks for the first three characters not being numbers, and the next 4 characters being numbers.
You could add an additional constraint to the AND function to confirm the length of the string is 7 characters, but I'm not sure if you wanted to do that.

 

[shyy]

You are right, it did fail

 

[pgc01]

shyy, I think your solution may fail where there is something like this for example:

AB12345

KGee, another alternative to try might be something like this:

=AND(
    ISNA(
      LOOKUP(
        9.99999999999999E+307,
        -MID(A1, ROW(INDEX(A:A, 1):INDEX(A:A, 3)), 1))),
    ISNUMBER(-MID(A1, 4, 4)))

This checks for the first three characters not being numbers, and the next 4 characters being numbers.
You could add an additional constraint to the AND function to confirm the length of the string is 7 characters, but I'm not sure if you wanted to do that.

Hi circledchicken

You formula checks if the last 4 characters are a number, but not if they are digits, which I think is what the OP needs.

For ex.

ABC1.23 passes the test.

also the first 3 characters should be letters.

 

[pgc01]

Hi

Try also:

=AND(IF({1,2,3,4,5,6,7}<=3,(MID(A1,{1,2,3},1)>="A")*(MID(A1,{1,2,3},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7},1))))

You may add a check if the cell has only 7 characters, if needed.

 

[shyy]

What does the {1,2,3,4,5,6,7} do?

Hi

Try also:

=AND(IF({1,2,3,4,5,6,7}<=3,(MID(A1,{1,2,3},1)>="A")*(MID(A1,{1,2,3},1)<="Z"),ISNUMBER(-MID(A1,{1,2,3,4,5,6,7},1))))

You may add a check if the cell has only 7 characters, if needed.

 

[pgc01]

What does the {1,2,3,4,5,6,7} do?

I'm checking each of the 7 first characters of the string individually. The array has the indices of the characters I'm testing.

 

[shyy]

I see, so if the cell has ABCDEF1234 you would do {1,2,3,4,5,6,7,8,9,10}

Thanks

 

[circledchicken]

Hi circledchicken

You formula checks if the last 4 characters are a number, but not if they are digits, which I think is what the OP needs.

That's a good point - thanks for the correction.
Also, I really like your formula approach - very nice!