Character count on each line within one cell

[ramesanscesse]

Hi everyone!

I am desperately trying to find the right formula (can't be a macro or anything complicated like that) to count the amount of characters on each line within a cell.
I have been trying to find it for years, it's driving me nuts, so at long last, I worked up the courage to create an account and ask here, in hopes that you guys can help me put this matter to rest once and for all
Ideally, it's something that would display like this, in B2, once applied (and that could be dragged down to do the same for each additional text cell in the A column).

I used to have a supervisor at an old job who knew this formula, some 7 or 8 years ago, but I've changed companies since, and no one at my new company seems to know it even the higher-ups who are really good at Excel. And even my co-workers from my old company can't remember it either, we just know it was fairly lengthy. As you can imagine, I've tried googling it many times over the years, but since English isn't my first language and I am pretty much technologically illiterate (I'm joking ofc... but barely!), I haven't been able to find it, so I really hope I'll be able to get an answer here.
Many thanks in advance!

P.S.: if you're using technical terms, please explain them to me like you would to someone from the Middle Ages
I'm very much a "Letters Over Computers"-type of person, haha!

 

[Peter_SSs]

AFAIK Its not possible for conditional formatting to format part of a cell

The way I read col H in the image, that is not being asked for.

Also note that in the image some cells in col C have multiple values.

@ramesanscesse
In relation to my last sentence above, where a cell in column C has multiple values. In your image those cells have the same number (7) on each line within the cell. Is that always the case or could they be different like rows 8 & 9 in my example below?
Also, could the number of values in column C be different to the number of lines in column F (& column G)?

Anyway, this is my suggestion for the layout given

ramesanscesse.xlsm
	C	D	E	F	G
1
2	10			Mana Potion	11
3	10			HP Potion	9
4	10
5	10			Shield	6
6	7 7			Evil begone	4 6
7	7 7			verybiglongtextononeline short	24 5
8	3 5			two three	3 5
9	3 5			four three	4 5
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(F2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(F2,CHAR(10)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G9	Expression	=MAX(TEXTSPLIT(G2,CHAR(10))-TEXTSPLIT(C2,CHAR(10)))>0	text	NO

 

[Cubist]

You can try this.

Book2
	A	B	C
1	Text	Max Allowed	Text Count
2	Hello! Hi I hope you have a nice day, today.	7 3 50	6 2 34
3	Today is a beautiful day	1 8 1 2	5 2 1 13
Sheet3

Cell Formulas
Range	Formula
C2:C3	C2	=TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(A2,CHAR(10))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C3	Expression	=SUM(--(LEN(TEXTSPLIT(A2,CHAR(10)))>--TEXTSPLIT(B2,CHAR(10))))>0	text	NO

 

[MARK858]

The way I read col H in the image, that is not being asked for.

If it is not then there already a few posts (including my last 2) where the entire cell is highlighted

 

[Peter_SSs]

If it is not then there already a few posts (including my last 2) where the entire cell is highlighted

I didn't understand how the data in post #18 relates to the data/layout in the post #19 image but the post #20 CF does not work for me with the post #19 layout (G6 is not highlighted like in the image provided). Did I not apply it correctly?

ramesanscesse.xlsm
	C	D	E	F	G
1
2	10			Mana Potion	11
3	10			HP Potion	9
4	10			Shield	6
5	7 7			Evil begone	4 6
6	7 7			verybiglongtextononeline short	24 5
Sheet2

Cell Formulas
Range	Formula
G2:G6	G2	=IF(F2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(F2,CHAR(10)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G6	Expression	=SUM(--(LEN(TEXTSPLIT(F2,CHAR(10)))>C2))	text	NO

 

[MARK858]

Did I not apply it correctly?

No I think I misunderstood post 19

 

[ramesanscesse]

Hi guys! I can't thank you enough for taking the time to work on my request while I went off to sleep, last night!
I still have a lot of things to wrap up for work before the end of the week, so I don't have a lot of time today, but I still wanted to come here and take a look at your ideas, so here goes!

@MARK858 re:#20 > Unfortunately, I need to stick to the exact number of columns in the exact order as I've shown in my post #19 due the fact that my little proofreading file, once perfected, would later be integrated to a bigger, existing one that is organised in this specific way (but I saw that you later said you might have misunderstood my post, so worries, hehe! thanks for trying to help, really appreciate it!).

@Peter_SSs re:#21 > Yours and @Cubist's (re:#22) conditional formattings were absolutely fantastic and as far as I could tell, seemed to work the same way with no difference I could perceive between the two of them in terms of results, but they also had the same small issue (though hopefully, if you guys are willing to give it another go, it should be the last one! ). But before I share the screenshot to show you what it is, let me answer your questions first!
1. In all the projects I have worked on, whenever there was more than one line available within the a cell, the other line(s) was/were always the same length as the first one. Now, what you mention could happen someday for sure, but for now, it's never been the case... and hopefully it remains this way lol
2. Now, this is the issue I am about to share with you below! Yes, it could be the case, and most likely, it would indicate an error on my part in column F if I inadvertently use up more lines there than allowed in column C (sorry, maybe I should have mentioned it in my previous post!). Please see below the issue and why it would be absolutely wonderful if the conditional formatting could help prevent that as well.

I hope that makes sense! So, if you guys are able to give it one last try to make the cells in G become highlighted in orange when there's a trailing line break, that would be awesome and you'd have my eternal gratitude. But if this whole thing has taken up too much of your time already, I'd definitely understand, and the formula and conditional formattings that all of you have created or improved are already amazing as they are, and I know they'll be tremendously helpful in my work!

I'm going to bed now, but I will check this post again tomorrow, after work. Once again, thank you all so much for everything, you guys are the best!

 

[Peter_SSs]

it would indicate an error on my part in column F

Rather than try to deal with an error you have made, what about highlighting that error so you can fix that error?
This CF in column F will highlight the cell if the final character is a "sneaky, invisible line break"

ramesanscesse.xlsm
	C	D	E	F	G
1
2	10			Mana Potion	11
3	10			HP Potion	9
4	10
5	10			Shield	6
6	7 7			Evil begone	4 6
7	7 7			verybiglongtextononeline short	24 5 0
8	3 5			two three	3 5
9	3 5			four three	4 5
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(F2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(F2,CHAR(10)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F9	Expression	=RIGHT(F2,1)=CHAR(10)	text	NO
G2:G9	Expression	=MAX(TEXTSPLIT(G2,CHAR(10))-TEXTSPLIT(C2,CHAR(10)))>0	text	NO

 

[ramesanscesse]

Hi @Peter_SSs, sorry for the late reply, these past few days have been very busy on my side x(

Thank you for your suggestion, it's a bit of a shame I won't be able to put it to use for this specific file I'm trying to create ><
The thing is, this proofreading file is a multipurpose one, meaning that in addition to the formulas and conditional formattings you guys have been helping me with in column G, I need the other columns to help me with other things as well. The role of column F and the reason why I don't use colours in this one is because it's a column I colour-code manually depending on the type of issue I foresee in this or that cell, or if I need to come back to this cell later because I'm not satisfied with it just yet, etc. Hence why it would actually be easier to have column G be the column flagging everything "overflow-related", if that makes sense
Plus, it could also be the case (although it's a much rarer occurrence) that I wasn't paying attention to the number of lines allowed and ended up typing three lines of text in column F when only two are actually allowed in column C, for example, so making the conditional formatting flag only those sneaky, invisible line breaks would be limiting its uses too much.

However, your suggestion did inspire me an extra idea (= having trailing spaces be flagged as well, since they also fall into the "sneaky, invisible" category, haha! but without filling column F with a specific colour, so I'll have to give it some thought after I have a solution for what I have mentioned in post #26).

Again, thank you very much for all your help so far! If you'd like to keep investigating this, that'd be awesome! Otherwise, enjoy the rest of your weekend ^^

 

[Peter_SSs]

these past few days have been very busy on my side

Same for me since then.

What about this then?

ramesanscesse.xlsm
	C	D	E	F	G
1
2	10			Mana Potion	11
3	10			HP Potion	9
4	10
5	10			Shield	6
6	7 7			Evil begone	4 6
7	7 7			verybiglongtextononeline short	24 5 0
8	3 5			two three	3 5
9	3 5			four three	4 5
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(F2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(F2,CHAR(10)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G9	Expression	=IFNA(MAX(TEXTSPLIT(G2,CHAR(10))-TEXTSPLIT(C2,CHAR(10)))>0,1)	text	NO

 

[Cubist]

Try:

Book1
	C	D	E	F	G
2	10			Mana Potion	11
3	10			HP Potion	9
4	10
5	10			Shield	6
6	7 7			Evil begone	4 6
7	7 7			verybiglongtextononeline short	24 5
8	3 5			two three	3 5
9	3 5			four three	4 5
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IF(F2="","",TEXTJOIN(CHAR(10),1,LEN(TEXTSPLIT(F2,CHAR(10),,1))))