Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Splitting Text to Multiple Cells

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.

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

Related Tips:

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

Du Nguyen    30 Jul 2016, 09:32
I have a problem with the text (in Excel VBA) is too big in a cell. The max. row height is 409 and text length is 3,147 chars in column "C"; let say Cells(19, "C"). How can I split this cells into multiple cells, maybe 3 or 4 cells so I can read the text. For example Cells(19, "C") with text length of 800 chars. Cells(20, "C") with the same length and Cells(21, "C") and so on until I can read the whole text by using
 Rows(index).EntireRow.Insert, etc.
 Please let me know.
 Thanks for your help,
 DU
Michael (Micky) Avidan    15 Nov 2015, 07:15
@Praveen Raj,
Assuming the long string: USD 100|INR 6000|PNY 600|EUR 200|GBP 500|SIN 500| resides in cell A1 - please type(!) all 6 currencies (the results) you are expecting from the formula to retrieve and where !?
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL
Shannon     15 Nov 2015, 04:34
Thank you for this, it was of great assistance to me. I appreciate it!!
Praveen Raj    10 Aug 2015, 02:22
prob: USD 100|INR 6000|PNY 600|EUR 200|GBP 500|SIN 500|..... so on( all in a same cell).

i want to separate alone the currency not the value. Is there any Formula for that to do so.

Note:I know to separate it via Date>Text to column.

Please provide me the Right formula, it will be useful for me to do a project.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.