How to validate values entered in a multiline Excel cell

Question

I get a spreadsheet from a procedure that multiline all the values into a single cell. To ensure that every line in that cell adheres to a particular format, I need to create a formula.

For instance, I need to verify that each line adheres to the format when the following values are all in one cell:

• 3 digits (123)
• 7 digits (1234567)
• 4 digits (1234)
• 4 digits (1234)
• 2 digits.2digits (12.12).

123 1234567 1234    1234    12.12345678
123 1234568 1234    1234    12.12
123 1234569 1234    1234    12.12345678
123 1234560 1234    1234    12.12

Answer 1

Try:

The formula in B1:

=AND(BYROW(TEXTSPLIT(A1," "," ",1),LAMBDA(x,SUM((LEN(x)={3,7,4,4,5})ISNUMBER(-x)(MID(TAKE(x,-1),3,1)=".")))))

Or, write a bit saner:

=AND(BYROW(TEXTSPLIT(A1," ",CHAR(10),1),LAMBDA(x,SUM((LEN(x)={3,7,4,4,5})*ISNUMBER(-x)*(MID(TAKE(x,-1),3,1)=".")))))