A Comprehensive Guide to Splitting Text in Excel

45 Responses to “A Comprehensive Guide to Splitting Text in Excel”

1. Rick Rothstein (MVP - Excel) says:

   September 11, 2010 at 4:04 am

   You have way more lines of code in your UDF than you need. Give this UDF a try instead...

   Function SplitWords(Rng As Range) As Variant
   SplitWords = WorksheetFunction.Transpose(Split(Join( _
   WorksheetFunction.Transpose(Rng))))
   End Function

   The function is actually a one-liner, but I posted it with a line continuation so that your comment processor wouldn't word-wrap it at a "funny" location.

   Reply
2. Rick Rothstein (MVP - Excel) says:

   September 11, 2010 at 4:30 am

   >> SplitWords($A$1:$A$10) + CTRL + SHIFT + ENTER.
   >> Copy cell C2 and paste it down as far as needed.

   By the way, is the above really how you implemented your UDF? I can't get your or my version of the function to work correctly using the above procedure... I am not sure what I am missing. Instead, I select all the cells to be filled, then type the formula into the Formula Bar and press CTRL+SHIFT+ENTER... doing it that way automatically fills the selected range correctly.

   Reply
3. Oscar says:

   September 11, 2010 at 8:09 am

   Rick Rothstein (MVP - Excel),

   Many thanks!!

   You will probably find more lengthy code here in the future, I was planning on blog posts like "Unique distinct words from a cell range (udf)" and "Duplicate words from a cell range (udf)". But I am sure I can´t write genious code like that. I am grateful, I have learned new things today!

   You are also right about how to implement the udf. Thanks for your contribution!

   Reply
4. Rick Rothstein (MVP - Excel) says:

   September 11, 2010 at 3:37 pm

   @Oscar,

   Below is a version of the SplitWords UDF that may be more useful than either of the two UDFs that we posted earlier. The problem with our current UDFs is that if you select a larger range to put the UDF formulas in than is required for the current range being processed, you get errors displayed in the cells that do not get populated with words. There are two reasons you might want to select a larger range of cells to populate with the UDF formula... one, you don't know how many cells to select because you don't know where the end of the split word list will be; two, you want to pass a larger range to the UDF so that you can dynamically handle new rows of data in the future and you want to populate the UDF formula through enough cells to handle such a future expansion to that original list. Using your sample worksheet with its 10 rows of data, replace whatever SplitWords UDF you current have with this one...

   Function SplitWords(Rng As Range) As Variant
   Dim List As String, Words As Variant
   Application.Volatile
   With WorksheetFunction
   List = .Trim(Join(.Transpose(Rng)))
   Words = Split(List)
   List = List & Space(Application.Caller.Count - UBound(Words))
   SplitWords = .Transpose(Split(List))
   End With
   End Function

   Now select, say, the range C2:C60 (this should be large enough to handle new words added to the list) and then put this formula in the Formula Bar...

   =SplitWords(A1:A20)

   and commit the formula using CTRL+SHIFT+ENTER (notice the range argument covers more cells than the number of cells with data in them... this is to be able to handle future entries in A11, A12, etc.). Okay, after you have done the above, the first thing to notice is that there are no errors being displayed in the cells that do not get populated with words. The second thing to notice is you can now fill in new data in cells A11 thru A20 and those words will get placed in the list in cells C2:C60. On top of that, if you skip rows in the word list range (that is, with your existing list of words in A1 thru A10, put "one two three" in, say, A18), the intervening blank rows will not be displayed in the resulting list of individual words in Column C. Hopefully, you and your readers will find this new version of the SplitWords UDF useful.

   Reply
5. Rick Rothstein (MVP - Excel) says:

   September 11, 2010 at 3:47 pm

   @Oscar,

   Whoops! I left a line of code in there from previous testing that can be removed. The Application.Volatile statement is not needed (that was left over from earlier attempts to correct code that was not working correctly). The UDF you and your readers should use is this one...

   Function SplitWords(Rng As Range) As Variant
   Dim List As String, Words As Variant
   With WorksheetFunction
   List = .Trim(Join(.Transpose(Rng)))
   Words = Split(List)
   List = List & Space(Application.Caller.Count - UBound(Words))
   SplitWords = .Transpose(Split(List))
   End With
   End Function

   Oh, by the way... I'd like to take a crack at the "Unique distinct words from a cell range (udf)" and "Duplicate words from a cell range (udf)" code that you mentioned, but I have a question as to what you want for the final output in that first one... Is the unique distinct word list to be only those words that do not have a duplicate elsewhere in the list, or a list of all words but only listed one time (without their duplicates)? If you do not want me to post these before you had a chance to try your hand at them, just say so. If that will be the case, then you can send me your email address and I'll foward any solutions I come up with directly to you so that you can incorporate them in your future article if you wish. Let me know either way how you want me to handle this.

   Reply
6. Rick Rothstein (MVP - Excel) says:

   September 11, 2010 at 5:30 pm

   @Oscar,

   Sigh! It seems I omitted a boundary check. My lastest UDF will produce a series of #VALUE! errors if the selected range is not large enough to house all the words in the split out list. Here is the corrected code...

   Function SplitWords(Rng As Range) As Variant
   Dim List As String, Words As Variant
   With WorksheetFunction
   List = .Trim(Join(.Transpose(Rng)))
   Words = Split(List)
   If Application.Caller.Count > UBound(Words) Then
   List = List & Space(Application.Caller.Count - UBound(Words))
   End If
   SplitWords = .Transpose(Split(List))
   End With
   End Function

   Reply
7. Oscar says:

   September 12, 2010 at 8:34 am

   Rick Rothstein (MVP - Excel),

   I would be more than happy if you would like to try to solve those problems. Actually there are three problems:
   Unique - Words occurring only once.
   Unique distinct - List of all words but only listed one time without their duplicates
   Duplicate - Words having duplicates.

   You can post or email your solutions.

   Thanks for all your work!!

   Reply
8. Rick Rothstein (MVP - Excel) says:

   September 12, 2010 at 9:47 am

   @Oscar,

   Here are the functions I have developed. I am going to sleep now, so I have not had time to test them fully, but I am pretty sure they work. Note that there is an optional argument allowing you to specify whether the matches will be case sensitive or not... the default is False, that is, to not match case so that One, one and ONE would be considered the same... specifying True for the optional argument would mean those three words would be considered as being different. These function are array functions and should be implemented by selecting the range to be filled (you can select more cells that will be necessary so that the list will dynamically expand if more words are added) and then committed by pressing CTRL+SHIFT+ENTER. You can specify a larger range for the first argument so that additional cells can be filled with words later on. Okay, here are the functions...

   Function DuplicateWords(Rng As Range, Optional _
   MatchCase As Boolean) As Variant
   Dim X As Long, WordCount As Long, List As String
   Dim Duplicates As Variant, Words() As String
   List = WorksheetFunction.Trim(Join(WorksheetFunction.Transpose(Rng)))
   Words = Split(List)
   For X = 0 To UBound(Words)
   If MatchCase Then
   If UBound(Split(" " & UCase(List) & " ", _
   " " & UCase(Words(X)) & " ")) > 1 Then
   Duplicates = Duplicates & StrConv(Words(X), vbProperCase) & " "
   List = Replace(List, Words(X), "")
   End If
   Else
   If UBound(Split(" " & List & " ", " " & Words(X) & " ")) > 1 Then
   Duplicates = Duplicates & Words(X) & " "
   List = Replace(List, Words(X), "")
   End If
   End If
   Next
   Duplicates = WorksheetFunction.Trim(Duplicates)
   Words = Split(Duplicates)
   If Application.Caller.Count > UBound(Words) Then
   Duplicates = Duplicates & Space(Application.Caller.Count - UBound(Words))
   End If
   DuplicateWords = WorksheetFunction.Transpose(Split(Duplicates))
   End Function

   Function UniqueWords(Rng As Range, Optional MatchCase As Boolean) As Variant
   Dim X As Long, WordCount As Long, List As String
   Dim Uniques As Variant, Words() As String
   List = WorksheetFunction.Trim(Join(WorksheetFunction.Transpose(Rng)))
   Words = Split(List)
   For X = 0 To UBound(Words)
   If MatchCase Then
   If UBound(Split(" " & UCase(List) & " ", _
   " " & UCase(Words(X)) & " ")) = 1 Then
   Uniques = Uniques & StrConv(Words(X), vbProperCase) & " "
   List = Replace(List, Words(X), "")
   End If
   Else
   If UBound(Split(" " & List & " ", " " & Words(X) & " ")) = 1 Then
   Uniques = Uniques & Words(X) & " "
   List = Replace(List, Words(X), "")
   End If
   End If
   Next
   Uniques = WorksheetFunction.Trim(Uniques)
   Words = Split(Uniques)
   If Application.Caller.Count > UBound(Words) Then
   Uniques = Uniques & Space(Application.Caller.Count - UBound(Words))
   End If
   UniqueWords = WorksheetFunction.Transpose(Split(Uniques))
   End Function

   Function ListOfWords(Rng As Range, Optional MatchCase As Boolean) As Variant
   Dim X As Long, Index As Long, List As String
   Dim Words() As String, LoW As Variant
   With WorksheetFunction
   Words = Split(.Trim(Join(.Transpose(Rng))))
   LoW = Split(Space(.Max(UBound(Words), Application.Caller.Count) + 1))
   For X = 0 To UBound(Words)
   If InStr(1, Chr(1) & List & Chr(1), Chr(1) & Words(X) & _
   Chr(1), 1 - Abs(MatchCase)) = 0 Then
   List = List & Chr(1) & Words(X)
   LoW(Index) = Words(X)
   Index = Index + 1
   End If
   Next
   ListOfWords = .Transpose(LoW)
   End With
   End Function

   Reply
9. Rick Rothstein (MVP - Excel) says:

   September 12, 2010 at 6:11 pm

   @Oscar,

   I think I must have been sleepier than I thought when I concocted those macros... there appear to be some minor problems with them... give me some time to investigate and straighten them out.

   Reply
10. Rick Rothstein (MVP - Excel) says:

    September 12, 2010 at 8:48 pm

    @Oscar,

    Okay, I think I have everything straightened out now. I'll list the code in a moment; but, to make things easier, here is a link where you can get a workbook with some sample data and the all the codes already implemented (and formatted so they are more readable that the codes listed below. That link is...

    https://www.filefactory.com/file/b361f7f/n/Word_Listing_Code.xls

    I noticed in my previous listings that when I didn't put line continuations on lines that were long enough to word-wrap, that copying those lines from your comment processor's listing "straightened out" the word-wrapped lines; so, I have decided not to try and insert any line continuations assuming you and your readers will be copy/pasting my code into a VB editor code window. So below are my macros for your three stated needs...

    Unique - Words occurring only once.

    Unique distinct - List of all words but only listed one time without their duplicates.

    Duplicate - Words having duplicates.

    Let me know how they work out for you or (hopefully not) about any bugs you might find in them...

    Function DuplicatedWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant
    Dim X As Long, WordCount As Long, List As String, Duplicates As Variant, Words() As String
    List = WorksheetFunction.Trim(Replace(Join(WorksheetFunction.Transpose(Rng)), Chr(160), " "))
    Words = Split(List)
    For X = 0 To UBound(Words)
    If CaseSensitive Then
    If UBound(Split(" " & List & " ", " " & Words(X) & " ")) > 1 Then
    Duplicates = Duplicates & Words(X) & " "
    List = Replace(List, Words(X), "", 1, -1, vbBinaryCompare)
    End If
    Else
    If UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & " ")) > 1 Then
    Duplicates = Duplicates & StrConv(Words(X), vbProperCase) & " "
    List = Replace(List, Words(X), "", 1, -1, vbTextCompare)
    End If
    End If
    Next
    Duplicates = WorksheetFunction.Trim(Duplicates)
    Words = Split(Duplicates)
    If Application.Caller.Count > UBound(Words) Then
    Duplicates = Duplicates & Space(Application.Caller.Count - UBound(Words))
    End If
    DuplicatedWords = WorksheetFunction.Transpose(Split(Duplicates))
    End Function

    Function UniqueWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant
    Dim X As Long, WordCount As Long, List As String, Uniques As Variant, Words() As String
    List = WorksheetFunction.Trim(Replace(Join(WorksheetFunction.Transpose(Rng)), Chr(160), " "))
    Words = Split(List)
    For X = 0 To UBound(Words)
    If CaseSensitive Then
    If UBound(Split(" " & List & " ", " " & Words(X) & " ")) = 1 Then
    Uniques = Uniques & Words(X) & " "
    List = Replace(List, Words(X), "")
    End If
    Else
    If UBound(Split(" " & UCase(List) & " ", " " & UCase(Words(X)) & " ")) = 1 Then
    Uniques = Uniques & StrConv(Words(X), vbProperCase) & " "
    List = Replace(List, Words(X), "")
    End If
    End If
    Next
    Uniques = WorksheetFunction.Trim(Uniques)
    Words = Split(Uniques)
    If Application.Caller.Count > UBound(Words) Then
    Uniques = Uniques & Space(Application.Caller.Count - UBound(Words))
    End If
    UniqueWords = WorksheetFunction.Transpose(Split(Uniques))
    End Function

    Function ListOfWords(Rng As Range, Optional CaseSensitive As Boolean) As Variant
    Dim X As Long, Index As Long, List As String, Words() As String, LoW As Variant
    With WorksheetFunction
    Words = Split(.Trim(Replace(Join(.Transpose(Rng)), Chr(160), " ")))
    LoW = Split(Space(.Max(UBound(Words), Application.Caller.Count) + 1))
    For X = 0 To UBound(Words)
    If InStr(1, Chr(1) & List & Chr(1), Chr(1) & Words(X) & Chr(1), 1 - Abs(CaseSensitive)) = 0 Then
    List = List & Chr(1) & Words(X)
    If CaseSensitive Then
    LoW(Index) = Words(X)
    Else
    LoW(Index) = StrConv(Words(X), vbProperCase)
    End If
    Index = Index + 1
    End If
    Next
    ListOfWords = .Transpose(LoW)
    End With
    End Function

    And, to keep all the code in one place, here is my last posted SplitWord macro...

    Function SplitWords(Rng As Range) As Variant
    Dim List As String, Words As Variant
    With WorksheetFunction
    List = .Trim(Replace(Join(.Transpose(Rng)), Chr(160), " "))
    Words = Split(List)
    If Application.Caller.Count > UBound(Words) Then
    List = List & Space(Application.Caller.Count - UBound(Words))
    End If
    SplitWords = .Transpose(Split(List))
    End With
    End Function

    All of these macros share the same functionality; namely, that one, you can select far more cells to load the formulas in than are required by the list (the empty text string will be displayed for cells not having an entry)... two, you can specify a larger range than the there are filled in cells as the argument to these macros to allow for future entries in the column... and three, for all but the SplitWords macro, you can specify whether the listing is to be case sensitive or not via the optional second argument with the default value being FALSE, meaning duplicated entries with different casing (like One, one, ONE, onE, etc.) will all be treated as if they were the same word with the same spelling... if you pass TRUE for that optional second argument, then those words would all be treated as if they were different words. One note... for all the "Case Insensitive" listing, the words are listed in Proper Case (first letter upper case, remaining letters lower case), the reason being if you had One, one and ONE then there is not reason to prefer one version over another, so I solve the problem by using Proper Case throughout. And, finally, as a reminder for your readers, these macros are implemented by first selecting a range to fill (remember, you can select more than will be required for you existing list in case more data is added later), then press with left mouse button on the Formula Bar and typing the UDF formula and then, finally, commiting the formula using CTRL+SHIFT+ENTER (not just Enter by itself).

    Reply
11. Oscar says:

    September 13, 2010 at 7:01 am

    Rick Rothstein (MVP - Excel),

    Great work!! I will post your code, explanations and your name in future blog articles.

    Now I need to understand your code.

    Thanks!!
    
    Edited: 2010-09-13 10:52

    Reply
12. Rick Rothstein (MVP - Excel) says:

    September 13, 2010 at 7:17 am

    I guess your "Great work" comment meant my macros passed your testing. As for understanding the code.. please feel free to ask for any explanations you might need.

    Reply
13. Rick Rothstein (MVP - Excel) says:

    December 13, 2010 at 4:57 pm

    @Oscar,

    First off, I just wanted to let you know your posted code got messed up at the first For statement... that line became combined with the line that follows it. Second, you have these two lines of code in your code...

    If Asc(Mid(Str, i, 1)) >= 65 And Asc(Mid(Str, i, 1)) <= 90 Or _ Asc(Mid(Str, i, 1)) >= 90 And Asc(Mid(Str, i, 1)) <= 122 Then If Asc(Mid(Str, Len(Str), 1)) >= 65 And Asc(Mid(Str, _
    Len(Str), 1)) <= 90 Or Asc(Mid(Str, Len(Str), 1)) >= 90 And _
    Asc(Mid(Str, Len(Str), 1)) <= 122 Then Str = Str & "'" Both of the tested ranges meet at ASCII 90 meaning they can be replaced by these single range tests instead... If Asc(Mid(Str, i, 1)) >= 65 And Asc(Mid(Str, i, 1)) <= 122 Then If Asc(Mid(Str, Len(Str), 1)) >= 65 And _
    Asc(Mid(Str, Len(Str), 1)) <= 122 Then Str = Str & "'" If that is really what you meant for your test range, then I wanted to point out your original two statements can be greatly simplified using the Like operator to these... If Mid(Str, i, 1) Like "[A-z]" Then If Mid(Str, Len(Str), 1) Like "[A-z]" Then Str = Str & "'" Now, I'm guessing you didn't mean the two ranges being tested to meet as the letter "Z"; rather, I think you may have simply mistyped 90 in the second range for 97 (the ASCII code for "a"). If that is the case, you will need to change your posted code and the code in your example worksheet accordingly. Oh, if you want, the Like operator can help out for this as well... If Asc(Mid(Str, i, 1)) Like "[A-Za-z]" Then If Asc(Mid(Str, Len(Str), 1)) Like "[A-Za-z]" Then Str = Str & "'"

    Reply
14. Oscar says:

    December 13, 2010 at 8:59 pm

    I have made the corrections!

    Thanks!!

    Reply
15. Rick Rothstein (MVP - Excel) says:

    March 25, 2011 at 5:40 pm

    Assuming VB code is permitted, this problem may be better handled with event code rather than formulas. With event code, you do not have to guess at how many rows or columns to copy the formula into... the code will adapt to whatever number of entries are made and for whatever number of words are in the text for those entries. Press with right mouse button on the name tab for the worksheet you want this functionality on, select View Code from the popup menu that appears and then copy paste the following code into the code window that opened up...

    Private Sub Worksheet_Change(ByVal Target As Range)
      Dim Cell As Range, Words() As String
      Const WordColumn As String = "B"
      Const OutputOffset As Long = 2
      If Not Intersect(Target, Columns(WordColumn)) Is Nothing Then
        If Intersect(Target, Columns(WordColumn)) = Target Then
          For Each Cell In Target
            Words = Split(WorksheetFunction.Trim(Cell.Value))
            Range(Cells(Target.Row, WordColumn).Offset(0, _
                  OutputOffset), Cells(Target.Row, _
                  Columns.Count)).ClearContents
            Target.Offset(0, OutputOffset).Resize(1, _
                   UBound(Words) + 1).Value = Words
          Next
        End If
      End If
    End Sub
    

    That is it... go back to the worksheet and type some words in Column B and watch the words appear in Column D outward.

    Reply
16. Oscar says:

    March 27, 2011 at 6:31 am

    Thanks for your contribution!

    Reply
17. Sree Pavan says:

    July 25, 2011 at 12:41 pm

    I tried Using the Array formula with .formulaarry in Excel, but I get 1004 error.
    How do I Use the above array formula in VB ??

    Reply
18. Oscar says:

    July 25, 2011 at 2:33 pm

    Sree Pavan,

    Why use the array formula in vba?
    I would use Rick Rothstein´s vb code instead.

    Why not copy the array formula into the formula bar?

    Reply
19. Sree Pavan says:

    July 25, 2011 at 4:02 pm

    Hi Oscar,

    I dont need all the texts from the column, I need only a few words\numbers & in non consecutive columns.

    For Example :

    Column A has "Cat Rat Mat bat"
    I need Mat in column D, Cat in column E & bat in column G.

    The array formula works for the above scenario, but the Private sub doesnt!! :(

    Reply
20. Oscar says:

    July 26, 2011 at 8:27 am

    Sree Pavan,

    Did you copy the code into a module?

    Where to copy the code

    Press with right mouse button on the name tab for the worksheet you want this functionality on, select View Code from the popup menu that appears and then copy paste the code into the code window that opened up...

    Reply
21. Rony says:

    January 3, 2012 at 4:30 am

    Hi! that's really interesting, I use this feature to do that:
    https://runakay.blogspot.com/2012/01/separating-one-cell-text-in-columns.html

    Reply
22. Evyatar says:

    January 10, 2012 at 1:53 am

    It's great many thanks :-)
    But the separated words have an extra unnecessary space before the first character (for each one of them) probably a +1/-1 problem, could you fix?
    I got over it by putting your formula in a simple MID formula, that way:
    =MID(MID(" "&$B2&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))),2,100)

    Reply
23. Oscar says:

    January 13, 2012 at 10:29 am

    Evyatar,

    Thanks for commenting!

    You are right, it is a +1/-1 problem. I added +1 inside the formula and problem solved.

    Blog post updated.

    Reply
24. Evyatar says:

    January 13, 2012 at 11:24 am

    :-)
    Thanks but now there is an extra " " at the end of each word,
    the formula should be:
    =MID(" "&$B2&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))-1)

    and again many thanks for this genius formula I was looking quit long for something like that.

    Do you think of a way that I can use it on a rows formula so I'll would have to use an array formula on your array formula.
    For example if I have a list of sentences and I want to check if a certain word appears in one of them?

    Reply
25. joe says:

    January 18, 2012 at 12:03 am

    This is now built in to 2010. Under data text to columns. This also lets you use not only spaces but other text (mine was seperatoed by ":")

    Reply
26. Oscar says:

    January 20, 2012 at 7:17 pm

    Evyatar,

    Thanks!!

    Use countif: countif(A1:A10, "*"&B1&"*")

    I think you'll find this post interesting:
    https://www.get-digital-help.com/return-multiple-matches-with-wildcard-vlookup-in-excel/

    joe,

    Yes, "Text to columns" also exists in previous versions. I just wanted to show it is possible using array formulas.

    Reply
27. Rick Rothstein (MVP - Excel) says:

    October 1, 2012 at 4:54 am

    Back when this article was first posted, I gave you a VB solution. I just came across this article again and now, although kind of late, I have a short, normally entered formula for you to consider...

    =TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A1)*999-998,999))

    Put the above formula in D2 and copy it across more columns than you think you will ever need (do not worry if there is no data to fill the excess cells), then copy that row of formulas down for as many rows as you want (you do not have to stop when your data ends either since, like for the excess columns, the excess rows will simply display the empty text string ("").

    Reply
    • Rick Rothstein (MVP - Excel) says:

      October 1, 2012 at 5:14 am

      Actually, given that non of your words will ever be longer than 99 characters, use this formula as it can handle many more split apart words than the version I posted earlier...

      =TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",99)),COLUMN(A1)*99-98,99))

    • Oscar says:

      October 2, 2012 at 3:58 pm

      Rick Rothstein (MVP - Excel),

      Great formula! What an inprovement,your formula is incredibly small, simple and probably a lot faster than mine.

    • Rick Rothstein (MVP - Excel) says:

      October 3, 2012 at 10:04 am

      Thanks, I'm glad you like the formula. For those who might be interested, here is a mini-blog article of mine where I discuss the generalized format underlying this formula...

      https://www.excelfox.com/forum/f22/get-field-delimited-text-string-333/

28. Michael Wellhousen says:

    June 17, 2013 at 3:12 am

    How would this formula be modified if a comma is used as a separator between text fields?

    Reply
29. Rick Rothstein (MVP - Excel) says:

    June 17, 2013 at 3:30 am

    Give this version of the formula I posted in the Comments a try...

    =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))

    If you look at the sub-thread immediately above your message in the Comments, you will find a link by me which would take you to a mini-blog article of mine that will give you the generized version of this formula so that you can modify it in anyway that you want.

    Reply
30. formula for seperating text says:

    December 17, 2013 at 12:25 pm

    […] This should help. You will need to make some modifications though. Text to columns: Split words in a cell (excel array formula) | Get Digital Help - Microsoft Excel re… […]

    Reply
31. ebooks online says:

    December 8, 2016 at 11:32 am

    I was curious if you ever thought of changing the structure
    of your website? Its very well written; I love what youve got to
    say. But maybe you could a little more in the way of content so people could
    connect with it better. Youve got an awful lot of text for only
    having 1 or two pictures. Maybe you could space it out better?

    Reply
32. Anil says:

    August 15, 2017 at 5:33 pm

    There is some problem to use this formula
    There is no numeric value when I used it ( there are words only)

    Some time this way is breaking the words (like "Bank" breaked into "Ba nk" ) into 2 or more cells
    Which should not be broken

    Reply
    • Oscar says:

      August 15, 2017 at 6:14 pm

      Anil,

      Which formula are you using?

    • Anil says:

      August 16, 2017 at 4:54 pm

      =TRIM(MID(SUBSTITUTE($A1,",",REPT(" ",99)),COLUMN(A1)*99-98,99))

      This one

      And one thing more it is seperating in column way (means from left to right)

      I want "up to down way" in only one row

      Is there any way or formula?

    • Oscar says:

      August 17, 2017 at 12:31 pm

      Anil,

      Can you provide a value that returns an error?

33. Dipankar says:

    December 12, 2018 at 4:44 am

    Dear Oscar,

    I am facing problem with the formula when I want to start this from column number AD & row 1. Below have attached my formula which I used in my excel.
    =MID(" "&$AD2&" ",SMALL(IF(ISERROR(SEARCH(MID(" "&$AD2&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)+1),COLUMN(AC1)),SMALL(IF(ISERROR(SEARCH(MID(" "&$AD2&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)),COLUMN(AC1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$AD2&" ",ROW($1:$99),1)," ")),"",ROW($1:$99)),COLUMN(AC1))-1)

    AC AD AE AF AG
    car 123 A #NUM!
    boat 234 AD T
    airplane 345 A
    bus 456 AV
    train 567 A V

    Reply
    • Oscar says:

      December 12, 2018 at 10:12 am

      Dipankar

      What is the problem?

      Which column contains your data?

34. Bob says:

    May 28, 2019 at 11:23 pm

    I get an error when I use this code. I followed everything to a T...

    "Invalid Name Error"

    Reply
35. Oscar says:

    June 3, 2019 at 12:15 pm

    Bob,

    which vba row was highlighted?

    Reply
36. Otto says:

    December 21, 2019 at 11:11 am

    Thanks for the formula. Can you please tell me what is the purpose of column(A1) in the formula? This formula is beyond my current skill level, but I want to understand it badly.

    Reply
37. Otto says:

    December 23, 2019 at 4:34 pm

    Can you tell me what is the purpose of the column A1 in the array formula?

    Reply
38. Xavier says:

    August 5, 2022 at 9:30 pm

    Nice job, indeed. Thank you for sharing.

    Reply

How to comment

How to add a formula to your comment
<code>Insert your formula here.</code>

Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes &lt; and > becomes &gt;

How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.

Contact Oscar

You can contact me through this contact form