How to validate values entered in a multiline Excel cell

0 votes

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 in Others by Kithuzzz
• 28,900 points
60 views

1 answer to this question.

0 votes

Try:

enter image description here

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)=".")))))
answered Jan 13 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

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

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

answered Oct 18, 2022 in Others by narikkadan
• 53,520 points
83 views
0 votes
1 answer

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

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

answered Oct 24, 2022 in Others by narikkadan
• 53,520 points
131 views
0 votes
1 answer

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

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

answered Oct 27, 2022 in Others by narikkadan
• 53,520 points
195 views
0 votes
1 answer

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

answered Nov 24, 2022 in Others by narikkadan
• 53,520 points
279 views
0 votes
1 answer

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

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
215 views
0 votes
1 answer

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

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

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
395 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

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

Feb 17, 2022 in Others by Edureka
• 13,630 points
177 views
0 votes
1 answer

IF - ELSE IF - ELSE Structure in Excel

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

answered Feb 18, 2022 in Others by gaurav
• 22,960 points
165 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,520 points
1,188 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 53,520 points
150 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP