I'm having trouble creating the correct formula in Excel.
Got column A with rows filled with text of different length. Let's say from 50 to 6000 characters.
Column B:
- take string from column A, truncate it to 155 characters, without breaking words in half and without any spaces at the end; So if string length is <= 155 take value from column A, otherwise truncate it to max 155 characters without breaking words and remove space at the end of the string
Column C and D:
- take string from column A, split it to 200 characters, without breaking words in half and without any spaces at the end and paste it in column C, take the rest of the string and paste it in column D, without any spaces at the beginning of the string. So if string length is <= 200 take value from column A and use it in column C, otherwise split it to max 200 characters without breaking words and remove space at the end of the string, take the rest of the string and use it in column D and remove space at the beginning of the string.
Any ideas or tips how to solve this?
CodePudding user response：
Use these three formulas:
- 155 characters cleaned up:
=TRIM(TEXTBEFORE(LEFT(A2,155)," ",-1)) - 255 characters cleaned up:
=TRIM(TEXTBEFORE(LEFT(A2,255)," ",-1)) - Rest of sentence:
=TRIM(RIGHT(A2,LEN(A2)-LEN(C6)))
You need Excel 365 current channel for the TEXTBEFORE formula.
A negative value for parameter [instance_num] of TEXTBEFORE indicates that the last occurance of the delimiter is the limit.