Check if first char position 1 is a letter position 2 and 3 a number in Excel-CodePudding

Im having some trouble combining two formulas in an if statement. I have a range of 6 columns which may contain a string with a length of 7, this string always starts with a letter followed by two random numbers (the rest of the string is random letter and numbers)

Im interested in finding every 7len string that starts with a letter followed by numbers in position 2 and 3. If the criteria are met the row is marked with a 1.

What I have counts every string with a lenght of 7

=IF(AND(LEN(J2)=7,(ISERR(LEFT(J2,1)*1))),1,0)

CodePudding user response：

If you have Excel 365 you can use this formula:

=LET(
lengthOf7,LEN(A2)=7,
firstLetter,NOT(ISNUMBER(LEFT(A2,1)*1)),
secondThirdNumber,ISNUMBER(MID(A2,2,2)*1),
AND(lengthOf7,firstLetter,secondThirdNumber)
)

It checks all your conditions and "names" them to be more readable.

If you don't have Excel 365 then you can use it like this:

=AND((LEN(A2)=7), NOT(ISNUMBER(LEFT(A2,1)*1), ISNUMBER(MID(A2,2,2)*1))

CodePudding user response：

try this:

=IF(AND(LEN(A1)=7,OR(AND(CODE(MID(A1,1,1))>=65,CODE(MID(A1,1,1))<=90),AND(CODE(MID(A1,1,1))>=97,CODE(MID(A1,1,1))<=122)),CODE(MID(A1,2,1))>47,CODE(MID(A1,2,1))<58,CODE(MID(A1,3,1))>47,CODE(MID(A1,3,1))<58),1,0)