Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
google sheets truncate #characters but full words
Hi,
Hoping some clever people can help out here! I have a list of sentences, which I need to place in another doc. This other doc can take up to 3 lines of separate text with each line having a set max number of characters. Is there a way to take the original full sentence and split this over the 1, 2 or 3 lines based on the number of characters which are limited to each line, but only take full words? For example, one sentence might be: Ideally the text result should look something like this each line If the first line in the doc can take a max character limit of 21. 21 characters is: Ideally the text resu However, I only want the result to be the full words from that result, so: Ideally the text I then want the second line to lead from that. If this second line in the doc can take a max character limit of 15. The next 15 characters after the previous full word, is (ignoring the start and end spaces): result should look someth Again, I only want the result to be the full words from that result, so: result should look And the same for the thrid line. If this limit were 18, the letters following on from the previous would be: something like this each lin Full words mean the result would need to be: something like this each So the final text back would be: 1st line: Ideally the text 2nd line: result should look 3rd line: something like this each However, if there is a sentence with only 18 chars, we still want the words back. Same goes for sentences that would fulfil 2 lines (21 and 15). Any help would be greatly appreciated. Thanks,! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
google sheets truncate #characters but full words
Phil Delaney wrote:
I have a list of sentences, which I need to place in another doc. This other doc can take up to 3 lines of separate text with each line having a set max number of characters. Is there a way to take the original full sentence and split this over the 1, 2 or 3 lines based on the number of characters which are limited to each line, but only take full words? For example, one sentence might be: Ideally the text result should look something like this each line If the first line in the doc can take a max character limit of 21. 21 characters is: Ideally the text resu However, I only want the result to be the full words from that result, so: Ideally the text I then want the second line to lead from that. If this second line in the doc can take a max character limit of 15. The next 15 characters after the previous full word, is (ignoring the start and end spaces): result should look someth Again, I only want the result to be the full words from that result, so: result should look And the same for the thrid line. If this limit were 18, the letters following on from the previous would be: something like this each lin Full words mean the result would need to be: something like this each So the final text back would be: 1st line: Ideally the text 2nd line: result should look 3rd line: something like this each However, if there is a sentence with only 18 chars, we still want the words back. Same goes for sentences that would fulfil 2 lines (21 and 15). I'm not going to write the formulae for you, but what you need to do is: 1st line: Find the last occurrence of " " at or before the size limit *plus one*, and use the characters before that. Unfortunately, you need something resembling VBA's InStr, which "normal" spreadsheet formulae don't seem to have. You can try using this solution from Rick Rothstein (watch the word wrap): https://www.mrexcel.com/board/thread...d-instrrev-in- excel.76765/ ....but no promises. 2nd...n-1 line: Similar to the 1st line, but this would be closer to VBA's Mid, starting at the character after the previously-found " ". last line: Similar to the above, using the rightmost characters from the character after the above. So... find find find | limit | limit |limit v v v v vv |--------------| |----------------| |-----------------| |-------| Ideally the text result should look something like this each line Hopefully you can figure it out from there. -- Nothing will be burning by then. Even solar fusion only lasts so long. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
google sheets truncate #characters but full words
Thanks Auric... Kind of makes sense, but I'm not sure I'm able to build the formula :/
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
Truncate Left Characters in Cell Value | Excel Programming | |||
Truncate words | Excel Worksheet Functions | |||
Trying to truncate or separate the first 3 characters/digits of co | Excel Discussion (Misc queries) | |||
Truncate last five characters | Excel Programming |