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!

 

[Cubist]

Edit- I suggest you post your sample data using XL2BB.
My guess the lines are separated by line breaks. Try:

Book2
	A	B
1
2	Hello! I hope you have a nice day, today.	6 0 34
Sheet3

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

 

[ramesanscesse]

Hi Cubist! Yes, it would basically be like the example I've provided on the screenshot : text that is spread on several lines within the same cell.
And then, there could be other cells in the same case on the other rows of the sheet, of course.

 

[MARK858]

Cribbing mainly from XOR XL, if there are only 3 entries (and remembering to wrap the cell)

Book1
	A	B
1	Hello Have a nice day	5 0 15
2	This is a test Tested Again	14 0 12
3	Tested for The Last time	10 3 9
Sheet8

Cell Formulas
Range	Formula
B1:B3	B1	=LEN(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),999*(ROWS($1:$1)-1)+1,999))) & CHAR(10) & LEN(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),999*(ROWS($1:$2)-1)+1,999)))&CHAR(10) &LEN(TRIM(MID(SUBSTITUTE($A1,CHAR(10),REPT(" ",999)),999*(ROWS($1:$3)-1)+1,999)))

 

[ramesanscesse]

Oh, I just saw your edit!
Thanks for the answer, I'm not sure what this XL2BB thing is, but from the link you posted, it looks extremely complicated ><

I'm looking for a formula that I can use offline by simply copy-pasting it in Excel, without the need of external tools.

 

[MARK858]

I can use offline by simply copy-pasting it in Excel

Click the

Symbol in the XL2BB screenshot either I or @Cubist posted, click A1 in your Excel Sheet and press Ctrl + V

Might be worth reading the links below

Guidelines

Guidelines for MrExcel Message Board Use

www.mrexcel.com

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[Scott Huish]

I'm a little confused how Cubist's solution did not help. Is this not for Excel 365?

 

[MARK858]

I'm a little confused how Cubist's solution did not help. Is this not for Excel 365?

My 1st post was because I missed @Cubist edit (probably didn't refresh the screen), I don't think the OP knew how to copy from XL2BB

 

[ramesanscesse]

Hi MARK858, thank you for your reply and for the links.
Unfortunately, I am not able to download any external file on this computer (and I don't have one of my own), so I can't install the tool you guys are talking about, hence the screenshot.

But I did copy-paste what you posted into Excel, and while it works well for the sample you provided, if I add a fourth line in A1, for example, then it doesn't work anymore. IIRC, the formula we were using at my old company worked regardless of the amount of lines that the cell contained, I think (but it's been so long tbh, I could be mistaken).

 

[MARK858]

Hi MARK858, thank you for your reply and for the links.
Unfortunately, I am not able to download any external file on this computer (and I don't have one of my own), so I can't install the tool you guys are talking about, hence the screenshot.

But I did copy-paste what you posted into Excel, and while it works well for the sample you provided, if I add a fourth line in A1, for example, then it doesn't work anymore. IIRC, the formula we were using at my old company worked regardless of the amount of lines that the cell contained, I think (but it's been so long tbh, I could be mistaken).

Did you test the formula @Cubist posted (see below)?

Book1
	A	B
2	Hello! I hope you have a nice day, today. Dfffff mmmm jjj kklojk hjklug	6 0 34 11 10 0 6 0
Sheet9

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