Morning! Not the best title for this thread...but here is what i am looking to do.
I have a cell full of test. say 500 characters.
I have a another program that i need to get this text into, but i have limitations regarding characters per line.
My limitations are 36 lines with 42 characters per line
i am trying to break the text at the last space before the 42nd character
In Cell A1:
Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
I have played with Find and Substitute and managed to get the first line correct:
Using Column A to find the correct space to start at (+1), and column B to return the text (MID)
in A6: 42 (the max length of the line)
in A7: =FIND("*",SUBSTITUTE(A1," ","*",A$6-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which find the last space before the 42nd character (40)
in B7: =MID(A1,1,A7) which returns "Lorem Ipsum is simply dummy text of the"
Perfect!
My problem, is now getting the following lines correct
in A8: =FIND("*",SUBSTITUTE(MID(A1,A7+2,LEN(A1)-A7)," ","*",A$7-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which returns 46, which is one space too far
in B8: =MID(A1,A7+1,A8) which returns "printing and typesetting industry. Lorem Ipsum" - this is 46 characters long. It should be less than 42 and stop at the space after "Lorem"
any pre-weekend help would be appreciated!!
I have a cell full of test. say 500 characters.
I have a another program that i need to get this text into, but i have limitations regarding characters per line.
My limitations are 36 lines with 42 characters per line
i am trying to break the text at the last space before the 42nd character
In Cell A1:
Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.
I have played with Find and Substitute and managed to get the first line correct:
Using Column A to find the correct space to start at (+1), and column B to return the text (MID)
in A6: 42 (the max length of the line)
in A7: =FIND("*",SUBSTITUTE(A1," ","*",A$6-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which find the last space before the 42nd character (40)
in B7: =MID(A1,1,A7) which returns "Lorem Ipsum is simply dummy text of the"
Perfect!
My problem, is now getting the following lines correct
in A8: =FIND("*",SUBSTITUTE(MID(A1,A7+2,LEN(A1)-A7)," ","*",A$7-LEN(SUBSTITUTE(LEFT(A1,42)," ","")))) which returns 46, which is one space too far
in B8: =MID(A1,A7+1,A8) which returns "printing and typesetting industry. Lorem Ipsum" - this is 46 characters long. It should be less than 42 and stop at the space after "Lorem"
any pre-weekend help would be appreciated!!