27 Responses to “Text to columns: Split words in a cell [Array formula]”

1. 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. Right click 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
2. Oscar says:

   March 27, 2011 at 6:31 am

   Thanks for your contribution!

   Reply
3. 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
4. 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
5. 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
6. Oscar says:

   July 26, 2011 at 8:27 am

   Sree Pavan,

   Did you copy the code into a module?

   Where to copy the code

   Right click 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
7. 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
8. 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
9. 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
10. 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
11. 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
12. 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/2010/11/22/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
13. 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/

14. 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
15. 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
16. 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
17. 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?

18. 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?

19. 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
20. 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

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