Splitting Text to Multiple Cells

Written by Allen Wyatt (last updated January 16, 2021)
This tip applies to Excel 97, 2000, 2002, and 2003

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:

=SplitMe(A5,2)

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3899) applies to Microsoft Excel 97, 2000, 2002, and 2003.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

One of the finishing touches used in some types of documents are an index or a special table, such as a table of ...

Discover More

Checking for Duplicate Rows Based on a Range of Columns

When working with data in Excel, you might want to figure out which rows of data represent duplicates of other rows. If ...

Discover More

Determining If a Cell is Bold

Want to figure out if the text in a cell is bold? The best approach is to use your own user-defined function, as ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

Deleting Old Data from a Worksheet

If you keep on-going data in a worksheet, some of your dataâ€"over timeâ€"may need to be deleted. If you have an ...

Discover More

Finding Unused Names

After months or years of naming things (such as cell ranges), you may find your workbook cluttered with a bunch of names ...

Discover More

Easily Entering Dispersed Data

Need to enter information into a bunch of cells that aren't anywhere near each other in the worksheet? Here's a handy way ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is one more than 0?

There are currently no comments for this tip. (Be the first to leave your commentâ€”just use the simple form above!)

This Site

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.