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]

Did you test the formula @Cubist posted?

I've been able to, now, thanks to you showing me how to copy-paste from that thing, and it actually seems to be working!!
Still need to test it with actual cells from the projects I work on, but that's very promising already, thank you both!

 

[MARK858]

I've been able to, now, thanks to you showing me how to copy-paste from that thing, and it actually seems to be working!!
Still need to test it with actual cells from the projects I work on, but that's very promising already, thank you both!

I did post an example in an edit to my previous post

Happy you got the copying from XL2BB sorted

 

[ramesanscesse]

I've just had a look at your edit, and it does seem to work for me in the same way!

The only small issue is: if I then try to copy-paste Cubist's formula to another Excel file, then it doesn't work anymore.
It counts all the lines I need to count in all the cells I add in rows below the original one perfectly, but I can't "export" it anywhere, it seems.

 

[MARK858]

The only small issue is: if I then try to copy-paste Cubist's formula to another Excel file, then it doesn't work anymore.

The only thing you should need to change in @Cubist formula is the A2 in the formula to the first cell in the range (as long as you are running it on 365) on the other file

 

[ramesanscesse]

That's what I did as soon as I saw that it worked on the sample. In the "destination" file, I changed "A2" into "G2" (to count the characters for my F2 column, there), but I got a "#VALUE!".
But I figured out what's the issue, and fortunately, it is only a... cosmetic (?) one. It only displays "#VALUE!" if there's nothing in my F2 cell, but as soon as I add some text there, "#VALUE!" is replaced by the character count I need, in G2.
See, I was expecting it to function like the normal "=LEN(F2)" formula did, where is there was no text in F2, nothing would appear in G2. Hence why I thought it couldn't be exported, but turns out, it can! I can live with "#VALUE!" appearing so long as the formula actually works when I type or copy-paste something in the text column

Thanks again, @Cubist Real MVP for sure!

 

[MARK858]

Just add a test if F2 is blank

=IF(F2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(F2,CHAR(10)))))

 

[ramesanscesse]

That worked great, thank you so much!!
... But in the meantime, I have discovered another possible issue. You've already been super helpful, so I don't want to be too annoying, but just in case it's not too much trouble, here's what's happening.
In addition to my original n00bish "=LEN(F2)" formula in G2, I also had a conditional formatting applied for the entire column G so that it would highlight the cells in orange if the character count in G was higher than the value I had in the corresponding row of column C. This was so that column G would not only tell me how long my text in column F was (only if the text was all on one line, which excluded a good chunk of the cells I was working on as they were on several lines, as you now know), but also if that text exceeded the character limitations I have to follow, aka the values present in column C.

Here's a simple example, to make things clearer, since I can't use XL2BB:
- I would have "5" in C2 (to tell me that I am not allowed to go over five characters in my text in F2)
- I'd write "Hello!" in F2
- Then column G would display "6" with a bright orange colour fill
→ So I would know to delete "!" in F2 to make my text fit within the required limitations in C2, and G2 would turn white again

For the conditional formatting, I was using what the commenter on this post was recommending to OP, and while it worked fine combined with the basic character count formula ("=LEN(F2)"), highlighting only the cells in column G that had a bigger number than those in column C, it now seems to be struggling with the new formulas (both Cubist's and your latest, improved version), highlighting all the cells in column G regardless of their value compared to the ones in column C...

Maybe it's just a case of "you can't have it all, darling", but my file to help with the proofreading my work was this close to being perfect (T_T)

 

[MARK858]

Do you mean something like E2 and F2 in the cells below (You'll need to copy the conditional formatting formulas manually i.e. select the formula in the XL2BB with your mouse or equivalent, copy it and then paste it in your conditional formatting rule), which checks if any of the results is above the number in C2 or D2 respectively

Book1
	A	B	C	D	E	F
2	Hello! Dfffff mmmm jjj kklojk hjklug	6 11 10 0 6 0	7	12
Sheet9

Cell Formulas
Range	Formula
B2	B2	=TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(A2,CHAR(10))))

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

 

[ramesanscesse]

Thank you again for taking the time to offer a solution, that's really kind of you

It took me a little while, but I've prepared a screenshot of what I am hoping to achieve, but I don't know how realistic that would be for the cells that are on multiple lines.
Sorry, I do realise it's not the ideal format on this forum, but maybe it provides a better idea of what I need, which is basically for whatever is in column G to be highlighted (or not) depending on whether they fit within the limitations imposed in column C, since each of these categories have a specific length I need to abide by.

(It's late in my part of the world, so I need to go to bed, but if you are able to provide your kind help once again, I'll see it tomorrow after work! )

 

[MARK858]

AFAIK Its not possible for conditional formatting to format part of a cell, the best you could do is split out the strings

Book1
	A	B	C	D	E	F	G	H	I
2	Hello! Dfffff mmmm jjj kklojk hjklug	6 11 10 0 6 0	7		Hello!	Dfffff mmmm	jjj kklojk		hjklug
3	Hello! Dfffff mmmm jjj kklojk hjklug	6 11 10 0 6 0	12		Hello!	Dfffff mmmm	jjj kklojk		hjklug
4	Hello! Dfffff mmmm jjj kklojk hjklug	6 11 10 0 6 0	10		Hello!	Dfffff mmmm	jjj kklojk		hjklug
Sheet9

Cell Formulas
Range	Formula
B2:B4	B2	=IF(A2="","",TEXTJOIN(CHAR(10),,LEN(TEXTSPLIT(A2,CHAR(10)))))
E2:J4	E2	=TEXTSPLIT(A2,CHAR(10))
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A4	Expression	=SUM(--(LEN(TEXTSPLIT(A2,CHAR(10)))>C2))	text	NO
E2:I4	Expression	=LEN(E2)>$C2	text	NO