Count to and after a character

[sydinstaller]

Hi,

I have a tricky one regarding count/len of text in a cell.
This excel sheet is used to upload data to our stock management system.

There is a limit of 30 characters per line but we can put more if each line is separated with a | .

E.G. of a cell content:

This is NOT OK
"This product description has more than 30 characters"

This is OK
"This product description|has more than 30 characters"

What I would like to do is have a formula (not a VB script) that could count if there are more than 30 characters before the "|" and even between two or more "|"

E.G. of an OK text line
"This product description|has more than 30 characters|and every 30 or less|characters is separated|with a pipe"

E.G. of NOT OK
"This product description|has more than 30 characters|and every 30 or less characters is separated|with a pipe"

The "and every 30 or less characters is separated" is more than 30 characters long so I should get a error of some kind.


Is this possible to do?

Note: I can't use VB as this sheet goes to 60+ people all with different security setting and versions etc so a formula is preferable.



Regards
Daniel

 

[Yongle]

Would this simple formula work?

=LEFT(A1,30) &"|"&MID(A1,31,30)&"|"&MID(A1,91,30)&"|"&MID(A1,121,30)
(add further segments if necessary)

Or would the unnecessary repeated "|" with shorter strings cause a problem?


almost there with

=SUBSTITUTE(LEFT(A1,30) &"|"&MID(A1,31,30)&"|"&MID(A1,91,30)&"|"&MID(A1,121,30),"||","")

 

[pvr928]

Hi sydinstaller

Try this (entered as an array formula - paste the formula in the cell and hit CTRL + SHIFT + ENTER at the same time):

=IF(LEN(MID(A5,LEN(A5)+1-MATCH(MAX(FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|"))),FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|")),0),MAX(FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|")))-1))>30,"Error","Ok")

Once you've created the array formula in one cell, click and hold or double click the square on the bottom right of the highlighted cell and drag down to cover the range you want.

With thanks to https://www.listendata.com/2015/10/excel-find-longest-word-in-cell.html

Cheers

pvr928

 

[Yongle]

Very ugly but...

this copes with anything up to 180 characters
=SUBSTITUTE(LEFT(A2,30)&"|"&MID(A2,31,30)&"|"&MID(A2,61,30)&"|"&MID(A2,91,30)&"|"&MID(A2,121,30)&"|"&MID(A2,151,30)&(IF(MOD(LEN(A2),30)=0,"|",""))&(REPT("|",ROUNDDOWN(LEN(A2)/30,0)+1)),"||","")

or if 150 characters enough
=SUBSTITUTE(LEFT(A2,30)&"|"&MID(A2,31,30)&"|"&MID(A2,61,30)&"|"&MID(A2,91,30)&"|"&MID(A2,121,30)&(IF(MOD(LEN(A2),30)=0,"|",""))&(REPT("|",ROUNDDOWN(LEN(A2)/30,0))),"||","")

shorter still for 120 max characters
=SUBSTITUTE(LEFT(A2,30)&"|"&MID(A2,31,30)&"|"&MID(A2,61,30)&"|"&MID(A2,91,30)&(IF(MOD(LEN(A2),30)=0,"|",""))&(REPT("|",ROUNDDOWN(LEN(A2)/30,0)+1)),"||","")

and if description never exceeds 90 characters
=SUBSTITUTE(LEFT(A2,30)&"|"&MID(A2,31,30)&"|"&MID(A2,61,30)&(IF(MOD(LEN(A2),30)=0,"|",""))&(REPT("|",ROUNDDOWN(LEN(A2)/30,0))),"||","")


you no doubt already spotted that I should have included a string for chars 61 to 90 in my first post

 

[sydinstaller]

Hi sydinstaller

Try this (entered as an array formula - paste the formula in the cell and hit CTRL + SHIFT + ENTER at the same time):

=IF(LEN(MID(A5,LEN(A5)+1-MATCH(MAX(FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|"))),FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|")),0),MAX(FIND("|",(RIGHT(A5,ROW(INDIRECT("1:"&LEN(A5))))&"|")))-1))>30,"Error","Ok")

Once you've created the array formula in one cell, click and hold or double click the square on the bottom right of the highlighted cell and drag down to cover the range you want.

With thanks to https://www.listendata.com/2015/10/excel-find-longest-word-in-cell.html

Cheers

pvr928

Thanks pvr928

This works perfectly.

I don't know anything about array formulas but this is great. I have now also added some conditional formatting base on the result.

Are there any limitations to array formulas apart from remembering to re-array the formula if you make any changes?


Thank you again.

Daniel

 

[pvr928]

Hi sydinstaller

Glad to hear it worked out for you. Thanks for the feedback.

Array formulae are very powerful and can be used to accomplish some pretty amazing things. I am not an expert on them, so can't go into specifics about their limitations, except to say they are computationally burdensome - ie they have the potential to turn your worksheet into treacle, so don't fill your worksheet up with them.

These are a few resources to get you started, but there are plenty of people on this forum who can help out too.

https://www.ablebits.com/office-addins-blog/2015/02/25/array-formulas-functions-excel/

https://exceljet.net/glossary/array-formula

Cheers

pvr928