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/threads/equivalent-of-instr-and-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.
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)