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

 

[pgc01]

Hi circledchicken, shyy

I'm glad you liked it.

This was a simple case. If it's a more complex case, I like to use an input mask instead.

For ex., let's say we have 10 characters, the first 3 characters, the 5th and the last must be letters, the rest must be digits.
In this case it's easier to use a mask, like "AAA0A0000A" where the "A" stands for a letter and the "0" for a digit.

The formula would be:

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

You can, of course, have more complex variants like other options besides letters and digits.
Using a mask adds structure to the solution and the formula is easy to adapt.

 

[circledchicken]

That's very nice - thanks for sharing that!

I was a little confused at first about the >=A and <=Z, because I thought it might be in some way related to the associated numbers you get when you extract the numeric code of a character using the CODE function.

But now I see it's making use of Excel's default alphanumeric sort sequence (i think!).

Do you have a computer science background? You come up with some great solutions.

 

[taurean]

Though thread titles do not bear any resemblance this was another thread that came up a week back:
http://www.mrexcel.com/forum/excel-questions/717837-characters-diagnosis-2.html

 

[markmzz]

I think that this small modification in the Pgc01's formulas work too:

Formula1:
=AND(MID(A1,{1,2,3},1)>="A",MID(A1,{1,2,3},1)<="Z",MID(A1,{4,5,6,7},1)>="0",MID(A1,{4,5,6,7},1)<="9")

Formula2:
=AND(MID(A12,{1,2,3,5,10},1)>="A",MID(A12,{1,2,3,5,10},1)<="Z",MID(A12,{4,6,7,8,9},1)>="0",MID(A12,{4,6,7,8,9},1)<="9")

Markmzz

 

[KGee]

Sorry for not posting back but this did resolve my issue and I was able to modify the code and use somewhere else so it was a double win for me!

Thanks