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```
Jan 13, 2023 in Others 280 views

## 1 answer to this question.

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)=".")))))
```
• 63,700 points

## How to stick an embedded document in a specific cell of an excel

Solution Select the documents (you can use the ...READ MORE

## How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

## Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

## How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

## Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

## Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

## IF - ELSE IF - ELSE Structure in Excel

In this case, you can use nested ...READ MORE