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

PiAlx 0
Viewed 34

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)
excel-formula excel
3 Answers

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))
useryPy710

You could use FILTERXML() to validate the string value:

enter image description here

Formula in B1:

=NOT(ISERROR(FILTERXML("<t><s>"&UPPER(A1)&"</s></t>","//s[translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')*0=0][substring(.,1,1)*0!=0][substring(.,2,2)*0=0][string-length()=7]")))

Where:

  • "<t><s>"&UPPER(A1)&"</s></t>" - Creates a valid xml-string using the input from A1 in uppercase;
  • [translate(.,'ABCDEFGHIJKLMNOPQRSTUVWXYZ','')*0=0] - Checks if when all uppercase letters are removed, the remainder multiplied by zero equals zero. This validates only letters and numbers across the input;
  • [substring(.,1,1)*0!=0] - Validates that the first character in the string is not equal to zero when multiplied by zero (thus being a letter);
  • [substring(.,2,2)*0=0] - Validates that the substring from the 2nd character with a length of two equals zero when multiplied by zero (thus being numeric);
  • [string-length()=7] - Validates that the total length of the node equals seven.

If you have ms365, you could actually also use REDUCE() to mimic the above and be less verbose:

=AND(ISNUMBER(REDUCE(UPPER(A1),CHAR(SEQUENCE(24,,65)),LAMBDA(a,b,SUBSTITUTE(a,b,"")))*1),ISERROR(LEFT(A1)*1),ISNUMBER(MID(A1,2,2)*1),LEN(A1)=7)

In this case the recursive SUBSTITUTE() will replace all uppercase letters with an empty string in order to make sure that the remainder is numeric. The other nested AND() parameters are rather self-explainatory.

Note: WAAR and ONWAAR are the Dutch equivalent to TRUE and FALSE.

JvdV0

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)
Nishanth Fenn0
Related