Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old October 4th 20, 10:27 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2020
Posts: 3
Default 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   Report Post  
Old October 4th 20, 11:24 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2003
Posts: 517
Default 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   Report Post  
Old October 6th 20, 12:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2020
Posts: 3
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. Babloo Excel Worksheet Functions 4 April 29th 11 11:27 PM
Truncate Left Characters in Cell Value Ray Clark[_2_] Excel Programming 4 December 27th 07 12:00 AM
Truncate words [email protected] Excel Worksheet Functions 2 April 12th 06 10:59 PM
Trying to truncate or separate the first 3 characters/digits of co Jim Excel Discussion (Misc queries) 4 January 13th 06 02:51 PM
Truncate last five characters Jesse Hamilton Excel Programming 9 November 11th 03 07:43 PM


All times are GMT +1. The time now is 02:05 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017