Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Split Long Text Cell into Two Shorter Cells Without Splitting Word
I have text cells that are up to 60 characters in length that I need to break
into 2 separate cells, each cell can be up to 30 characters long without splitting up any words. So, if the 30th character splits a word, it should split to the second cell at the previous space. How do I set up a formula to do this? I figured that if I wanted the first 30 characters, I could use the formula (if the text was in A1) =LEFT(A1,30). But this splits any words. Thanks in advance! |
#2
|
|||
|
|||
Of course you cannot. If you have 60 characters, and you split them at character 25 you will have 35 characters in your second cell, thereby failing your 30 character limit. Assuming then that you have 50 characters to split 30-30, you have a problem with the word that you do not wish to split, if it is 10 (or more) characters long and the last character is in position 30 you need to find the space that is 11 characters back, and there could be 3 or 4 spaces there on other lines. For a 'best try' you could use two helper columns and extract characters 25 to 33 into there, =Mid(A1,25,8) find the first space =IF(B2<"",FIND(" ",B2),0) and use that to split to the first split-cell =Left(A1,(30-6+c1)) and the second split-cell =Mid(a1,(30-6+c1),40) This will split most cells on a 5-character-back or 3-character-forward test, but will fail on large words. -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=385004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I break up a long text cell into shorter ones withoug s | Excel Discussion (Misc queries) | |||
Copy cell format to cell on another worksht and update automatical | Excel Worksheet Functions | |||
advanced filter - can't match a long text cell | Excel Discussion (Misc queries) | |||
On click, copy text into another cell - XL2K | Excel Worksheet Functions | |||
Help adding text values | Excel Worksheet Functions |