I have to edit text in a system upload spreadsheet where I'm only allowed 40 characters per line. By using Alt-Enter I can create the multi line entries necessary to conform to this 40 character per line limit.
Does anyone know how I can count the characters on each line within a cell please ? I'm familiar with the LEN(A1) command, but that's all I know.
Any advice or suggestions would be most welcome.
TW
Terry
"... I thought I was a power user of Excel until I came onto these boards..."
How many lines will the cell have....
If you have 2 lines:
Please Login or Register to view this content.
If you have 3 lines:
Format the result cells to wrap text.Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
The actual number of lines I'm using is 4 lines, so I'll try and edit the 3 line one to include the extra line.
But, fantastic, thank you very much.
tw
I'm afraid I'm a bit prone to brave talk... is there any chance you could give me the code for 4 or 5 lines please ?
For 4 lines:
For 5 lines:Please Login or Register to view this content.
Please Login or Register to view this content.
Here's another way for 4
=SUBSTITUTE(TEXT(SUMPRODUCT(LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",100)),{1,101,201,301},100)))*{1000000,10000,100,1}),"00-00-00-00"),"-",CHAR(10))
I assume you have no more than 100 characters on each line, it'll display a little differently to NBVC's version, i.e. with leading zeroes. To modify for 5 lines
=SUBSTITUTE(TEXT(SUMPRODUCT(LEN(TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",100)),{1,101,201,301,401},100)))*{100000000,1000000,10000,100,1}),"00-00-00-00-00"),"-",CHAR(10))
I wanted to devise a formula that would give the count for any number (up to a point). I imagine that can be done but I'm still working on it........
Very interesting daddylonglegs...
This isn't the prettiest of formulas.. but it will accomodate from 1 line to 5 lines automatically...
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks