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!

 

[ramesanscesse]

Hey @Peter_SSs! Thank you so much for getting back to me, I really appreciate it!
After doing a quick initial test where it seemed to be working perfectly, I got super motivated to keep working on my little proofreading file, and I actually ended up spending my entire afternoon on it, haha! I kind of get why you guys like Excel, now! It is very satisfying when it does work

But as I was finishing up, I tried one last thing to test the conditional formatting you suggested, and... I got some strange results, not really sure why that's happening... Please see below!

Isn't it odd that if I set the character limitations in column C so it allows characters on the first line (doesn't have to be just "1", btw), then so it allows none on the second line, and then again so it allows them on the third one, I get an orange cell in column G despite the fact that there's only 1 character in F2?
And the weirdness doesn't stop there...

The exact same thing without "0" on the second line actually works as intended, for some reason...?

But then again, the "0" on the second line doesn't seem to be problematic in these cases:

I am completely confused as to why all of these things are happening... Conclusion of the day: Excel might be fun, it'll forever remain a mystery, to me! xD
But we're getting very close, I think! The current formula and conditional formatting should work as intended and without any issue given my current workflow, which is to paste the necessary data (max characters allowed + my own text) from another software. The cases where I will manually input the max characters allowed myself will be very rare, and even more so if there's a blank line in the middle like in the above examples, but... it sure would be interesting to know why that doesn't/can't work!

(I'll be replying to you in my next post, Cubist!)

 

[ramesanscesse]

Hi @Cubist! Thanks for taking the time to take a look at my post again!
The new formula you just shared does seem to work similarly to the one we were using previously (= the one Peter_SSs uses in post #29), but it looks like it can't detect empty lines! Please see below:

So the formula itself is probably perfect already, I think, right?
Now all that's left is to figure out how to make it work with the conditional formatting, if that sounds like something you'd like to try!

P.S.: please note that I didn't apply any conditional formatting in either of the screenshots included in this post so the focus is purely on the effects of the formula ^^

 

[Peter_SSs]

I got some strange results

Helpers can only offer suggestions for what we actually know about what you have and what you want. One of the issues is that we cannot be sure exactly what you have in your cells since an image does not show Alt+Enter characters and may not show space characters.
It also seems to me that your requirements/examples are gradually changing.
For me the upshot is that I don't actually know what you have or what you want.

Are you able to set out clearly, in words , exactly what the conditional formatting in column G is supposed to do?

 

[ramesanscesse]

Ah, sorry if this is a bit confusing!
I promise you, I am not even sure what I'm not explaining properly (I'm sure it's definitely my fault, though! being so inexperienced with Excel, perhaps what I thought was straightforward is actually very unclear), so please do not hesitate to ask me specific questions to help me phrase things better for you guys (just keep in mind I'm doing it in a language that's not even my own, so please bear with me here ). In the meantime, let me try and explain a few of the things you've mentioned by breaking everything down, sorry if that's a bit of a read!

Basically, what I've posted in #31 was just a few experiments using the formula and conditional formatting you've provided in #29. Before that, with the texts present in your column F, everything was working just fine!
So of course, I just wanted to play around and try out different cases, like for example, what happens if I have something like in my first screenshot, where the only character in F2 was the letter "M", without any space nor any line break. Since that cell only contains that one character, it should not, in theory, get highlighted in orange in G2, since it should be considered as fitting within the imposed limitations we have in C2 (where 1 character is allowed on the first line, 0 is allowed on the second line, and 1 other character is allowed on the third line). That means that if F2 only contains one line (made up of this one character), then that single line should also be considered as the first line that allows for 1 character in column C2, regardless of the absence of other lines. But it looks like right now, Excel doesn't consider that "the only one" also means "the first", even though it should.
That's why I shared that second screenshot in post #31: it seems strange that simply by deleting the "0" from column C (the max characters allowed for the second line in column F, which is none, in this scenario) makes the conditional formatting behave as I was expecting/hoping it would for my first screenshot. Again, meaning that it now considers that "the only character present in column F counts as its first line" which is nice, but I do wish that this "empty" second line (which actually contains the line break necessary to insert the third line, ofc) was considered just as "empty" when "0" was in it, the first screenshot...
In the third screenshot, F2 contains "M+[two empty lines]" and in the fourth, "M+[empty line]+M", where the conditional formatting is behaving as expected (hence the use of the green colour), but that was just to show that the presence of "0" in column C doesn't seem to be inherently problematic (although maybe I should have tried with "[empty line]+M+[empty line]" too, for good measure...). It just seems to be an issue when things are as I have explained for the first screenshot.

So, to put it simply, the conditional formatting is just supposed to highlight column G in orange whenever the amount of characters on any line of a cell in column F is going over the max characters allowed in the corresponding line of the corresponding cell in column C (or, if there's no exact "line-to-line match" between column C and column F, have the only line in column F correspond to the first line in column C, as I was explaining above), but also whenever the total characters in column G exceed the total characters allowed in column C regardless of how they're spread on each line, ofc.
Lastly, yes, my examples have evolved gadually as you guys kept improving the formula / conditional formatting little by little, but my requirements have stayed the same: 1) I needed to know the "length" (counted in characters) of each line of text in the cells of column F which we can cross off the "to-do" list thanks to you all, 2) now, I just need to know which cells contain overflows (= exceed the max character limit imposed in column C). The changing examples are simply due to me testing either the formula or conditional formatting you guys are proposing to try and cover all bases now, so my little proofreading file can be used in all types of situations in the future! Character length requirements do not stay the same all the time. For example, a cell containing a few lines of dialog will not have the same length requirements as a cell that is simply meant to contain the name of a spell or the name of an item. Hence why I'm trying to think of all the cases that could happen while this thread is still active, so I don't have to reopen it later and be like "Um, sorry guys, actually, there's this new thing that's happening now that I didn't think about a few months ago... ", so it does require a bit of testing out each time, sorry about that! But hopefully this somewhat lengthy explanation cleared things up a bit, at least Otherwise, feel free to ask more specific questions and I'll be happy to clarify further!

 

[Peter_SSs]

So, to put it simply, the conditional formatting is just supposed to highlight column G in orange whenever the amount of characters on any line of a cell in column F is going over the max characters allowed in the corresponding line of the corresponding cell in column C (or, if there's no exact "line-to-line match" between column C and column F, have the only line in column F correspond to the first line in column C

I'm not sure it is so simple.
If the above was followed then I don't see that column G in post 26 should get highlighted. In that image no line in column G is going over the max characters allowed in the corresponding line of the corresponding cell in column C.

 

[ramesanscesse]

Hey @Peter_SSs, sorry for the late reply!
Hopefully I can explain this one easily! It is because in post #26, the problem is not a character overflow, it is a line overflow! In the screenshot I included with that post, there are three lines that are being used in column F (due to the sneaky, invisible line break that I circled on the third line in column F, and you can see its presence being reported in column G thanks to the "0" on the third line as well), instead of the two lines that are allowed by column C.

See, column C actually serves two purposes: it not only tells you how many characters are allowed per line, but it also tells you how many lines you are allowed to use in total in the whole cell!
I created a couple of pictures to illustrate that, I hope that helps clarify things a little bit ^^

Now, reusing the third example from my picture above, please see the three-line version as well as the five-line version of that same text below.
→ The five-line version is actually a similar situation to what was happening in post #26: there is an extra line that's being used in in column F when it should not be the case as column C doesn't allow it, hence why it needs to be highlighted in orange.
→ And the three-line version is a similar issue to what I am showing in the first screenshot in post #31 as well.

I hope that makes sense (especially considering I did all that between 3:30 a.m. and 5 a.m. xD)!
Thanks to your feedback, I've been re-evaluate my own assumptions about what's straightforward and what isn't, so it's been super helpful!

 

[Peter_SSs]

Hopefully I can explain this one easily! It is because in post #26, the problem is not a character overflow, it is a line overflow!

the point I was trying to make was that post 34 was supposed to ..

set out clearly, in words , exactly what the conditional formatting in column G is supposed to do

.. yet it did not mention the circumstance given in post 26.

In any case, see how this goes.

ramesanscesse.xlsm
	C			F	G
1
2	1 0 1			M	1
3	1 0 1			M X	1 1
4
5	10
6	10			Shield	6
7	1 21			A Sneaky CHAR(10) below	1 21 0
8	7 7			Evil begone!	4 7
9	7 7			verybiglongtextononeline short	24 5
10	5 4			five four	4 4
11	5 3			five three	4 5
Sheet4

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

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:G11	Expression	=LET(lf,CHAR(10),tsc,TEXTSPLIT(C2,lf),n,COUNT(-tsc),OR(COUNT(-TEXTSPLIT(G2,lf))>n,MAX(IFERROR(TEXTSPLIT(G2&REPT(lf,n),lf)-tsc,0))>0))	text	NO

 

[ramesanscesse]

Aaahhh, sorry for misunderstanding! And you're right, I should have mentioned something about the line limit in post #34! Guess I was so focused on trying to explain post #31 that the previous stuff just slipped my mind, my bad
That's why I use these screenshots as visual aids, so that they can help me explain what I'm terrible at putting into words. Summarising stuff is really not my forte, haha (^^')

On another note... You did it!!! You found the perfect conditional formatting to go with the formula that Cubist found and that MARK858 improved, thank you so, so much for not giving up on me despite me being so bad at explaining everything (and many thanks to you two again, guys, if you're still lurking)!!!
I don't know if I can mark another post as the answer , but I'll just say this for posterity: post #37 definitely counts as that too!

I just have one last question, just in case that happens to be part of your area of expertise as well, but it's really just to serves as an FYI for myself, this time
It is somewhat related to what you've helped with, but maybe it can also be considered as a general "Microsoft Office being weird" question, since it's something I have seen in the past as well. Here it is: as you can see below, I have the exact same text three times in F2, F3 and F4. But for some reason, Excel doesn't count the length of its first line the same way depending on whether the text originated / was edited in Excel vs. if the text was copy-pasted from somewhere else.

View attachment 120149

And the reason why I said it might be a "weird Microsoft Office thing" is because it's something you can also see using the "Word Count" function in Microsoft Word, where it can be counted as either 14 or 15, depending on whether you choose to count the line break as an extra character or not. But at least, Word kind of lets you choose which version you prefer by showing you both versions, whereas Excel kind of decides for you, it seems?

Mind you, I do think that in this instance, the version shown in row 3 is the "safest" one, so no change required to either the formula or conditional formatting! It's simply something I've never really understood, and Google doesn't seem to be able to help either, so I'd just thought I'd ask here for my own information in case you happen to know: why is this happening and is there a way to toggle that on and off to display one version or the other, in Excel? Or would that require another formula and or formatting (and if so, no need to bother! the current ones you guys found are the ones I'm going to use, I'm only asking out of curiosity ^^)!

 

[Peter_SSs]

On another note... You did it!!! You found the perfect conditional formatting to go with the formula that Cubist found and that MARK858 improved, thank you so, so much for not giving up on me ...

You're welcome. Thanks for confirming the suggestion works for you.

I don't know if I can mark another post as the answer ,

You can only mark one - and that should be the one that (for you) best answers the original thread question.

I just have one last question, ...
... Here it is: as you can see below,

We cannot "see below". In any case it sounds like it might be quite a different question so would be best asked in a new thread.

 

[ramesanscesse]

Thanks for letting me know! I've marked post #37 as the final answer, then, since it contains both the right formula and the right conditional formatting (in the unlikely event someone else needs both, someday lol).

...Oh, and looks like my screenshots have indeed disappeared! That's odd, I could still see them after I sent post #38...! Oh well, that's fine I guess
It wasn't something I absolutely needed an answer to, it was just something I was kind of curious about (and it was somewhat related to what we were discussing previously, but it's not worth making a whole new thread just for that either, so I'll take that as a sign that it's time to move on for now xD)!

Again, thank you for everything, take care!