by Allen Wyatt
(last updated August 17, 2013)
Phil has a series of cells that contain text. Some of the text is too long, so he needs to split it into multiple cells. For instance, if a cell contains "the quick brown fox" and he needs to split the text so that no part is longer than 12 characters, Phil can easily do it. But he wants to make sure that the split is made at the space before the 12th character, rather than at the exact 12th character. Phil is at a loss as to how to do this.
You might think you could use the Text to Columns tool in Excel, but it is not suited well for the job. If you set the tool to split text based on delimiters such as a space, then you end up with a single word in each cell. If you set the tool to split the text as "fixed width," then it doesn't split words at spaces; it just makes sure that each chunk is whatever size you specify.
You could use a formula to get the desired results, but doing so would result in formulas that are amazingly long. For instance, if the too-long text is in cell A3, the following formula could be used to split out the first chunk of that text, at the space before the 12th character:
=IF(LEN($A3)>12,IF(ISERROR(FIND(" ",MID($A3,12,1) &MID($A3,11,1)&MID($A3,10,1)&MID($A3,9,1)&MID($A3,8,1) &MID($A3,7,1)&MID($A3,6,1)&MID($A3,5,1)&MID($A3,4,1) &MID($A3,3,1)&MID($A3,2,1))),MID($A3,1,1),MID($A3,1, 13-FIND(" ",MID($A3,12,1)&MID($A3,11,1)&MID($A3,10,1) &MID($A3,9,1)&MID($A3,8,1)&MID($A3,7,1)&MID($A3,6,1) &MID($A3,5,1)&MID($A3,4,1)&MID($A3,3,1)&MID($A3,2,1)))) ,RIGHT($A3,LEN($A3)))
Remember—this is all a single formula, just to get the first chunk. The formulas to get the second, third, fourth, and later chunks are even longer. Clearly, using a formula may not be the best approach.
This leaves using a macro. A macro can examine the text string and easily chop it up into pieces of the desired length. Consider the following user-defined function:
Function SplitMe(sSentence As String, iPos As Integer, Optional iLen = 12) Dim sSegments() As String Dim iSegments As Integer Dim sRest As String Dim sTemp As String Dim iSpace As Integer Dim J As Integer iSegments = 0 sRest = sSentence sTemp = Left(sRest, iLen + 1) Do Until Len(sTemp) <= iLen iSpace = 0 For J = Len(sTemp) To 1 Step -1 If Mid(sTemp, J, 1) = " " And iSpace = 0 Then iSpace = J Next J If iSpace > 0 Then sTemp = Left(sRest, iSpace - 1) sRest = Mid(sRest, iSpace + 1) Else sRest = Mid(sRest, Len(sTemp) + 1) End If iSegments = iSegments + 1 ReDim Preserve sSegments(1 To iSegments) sSegments(iSegments) = sTemp sTemp = Left(sRest, iLen + 1) Loop iSegments = iSegments + 1 ReDim Preserve sSegments(1 To iSegments) sSegments(iSegments) = sTemp If iPos <= iSegments Then SplitMe = sSegments(iPos) Else SplitMe = "" End If End Function
The function takes either two or three parameters. The first parameter is the string to be split up, the second is which chunk you want from the string, and the third (and optional) parameter is the desired length of each chunk. If you leave off the third parameter, then the function assumes you want each chunk to be a maximum of 12 characters. As an example, assuming that the text is in cell A5, the following will return the second chunk from the text where each chunk is up to 12 characters long:
The function will return good results, provided each word in the text string is no longer than the specified target length for each chunk. If it is, then you'll get some strange results, including some chunks that don't contain full words.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3899) applies to Microsoft Excel 97, 2000, 2002, and 2003.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
Excel allows you to edit your cell contents in two places. What if you want to limit where editing occurs, so it can only be ...Discover More
Don't want people using your workbook to be able to use AutoFill? You can add two quick macros that disable and enable the ...Discover More
Paste some information into a worksheet and Excel helpfully displays some options related t the paste operation. If you don't ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.