Macro or formula to display the number of characters per line within an Excel cell

Dear all,

My name is Carlos, nice to e-meet you!

I have a special request and I was wondering if any of you have a workaround or a precise formula/macro to achieve it.

Basically I have an Excel file with several character limitations, so some cells can only have one line of text, other can have from two up to three lines. Most of the conditions are that the cell has to have 60 characters the most. So I was wondering if there is the possibility to show how many characters there are in a line and the results shown separated by a comma.

A visual example is the following:

Character Limit to Meet	Text	Number of Characters per Line within a Cell
60 characters * 2 lines	This is an example, first line This is the second line	30,23

I hope any of you have a reply to this question.

Best regards,

Dear Ford,

Thanks a lot for the quick and prompt reply.

Yes, it's not simple as it sounds but it seems to be possible as once I saw it perfectly implemented in a file using VBA, it's a pity I never learned VBA.

Regarding the formula that you shared, it seems to work fine when there are two lines in the cell, but when there is only one it shows #VALUE! and when there are three only the count of the first two lines is shown.

Still, I really want to thank you for your reply! This is really helpful as it seems almost 80% of the cells I'm checking have two lines in one cell.

Best regards,
Carlos

PS: I leave the thread as unsolved due the issues explained above.

Try this

Please Login or Register  to view this content.

Dear WouterM and Mandeep Baluja,

Thanks a lot for this function, it works perfectly fine and as expected!

It counts the number of character per line, even if there are three lines or more.

Regarding the previous formula, I just noticed it counts an extra character for the first file, I think it's counting the line break as a character and the third line doesn't shows up.

Again, thanks a lot to everyone for your help!

Best regards,
Carlos