Text to columns: Split words in a cell
This blog article describes how to split strings in a cell with space as a delimiting character, like Text to columns. Text to columns separates the contents of one cell into separate columns.
I present here four different formulas that does the job, I recommend example 3 or example 4. Short and easy to understand.
Table of Contents
- Array formula - blank as a delimiting character
- Array formula - any delimiting character(s)
- Example 2 - Excel 365 formula
- Excel 365 formula - any delimiting character(s)
- Example 3 - Rick Rothstein's formula
- Rick Rothstein's formula - Any delimiting character(s)
- Example 4 - FILTERXML function - any a delimiting character
- Get Excel file
1. Blank as delimiting character - array formula
Array formula in cell D1:
To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.
The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.
Copy cell D2 and paste to cell range D2:G6.
Explaining formula in cell D1
Step 1 - Split characters in a cell into an array
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract. The ROW function returns an array from 1 to 99.
MID(" "&$B2&" ", ROW($1:$99), 1)
becomes
MID(" car 123 A ", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; ... ; 99}, 1)
and returns this array:
{" "; "c"; "a"; "r"; " "; "1"; "2"; "3"; " "; "A"; ... ; ""}
Step 2 - Search for a blank as a delimiting character and return position
The SEARCH function returns the number of the character at which a specific character or text string is found reading left to right (not case-sensitive), if string is not found the function returns an error value. The ISERROR function returns TRUE if the value is an error.
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3). If the ISERROR function returns FALSE the function returns the corresponding row number.
The SMALL function then returns the k-th smallest value in the array based on the column function and a relative cell reference that changes when the cell is copied to the next cell. This makes the formula return a new value in each cell.
SMALL(IF(ISERROR(SEARCH(MID(" "&$B2&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))
becomes
SMALL(IF(ISERROR(SEARCH({" "; "c"; "a"; "r"; " "; "1"; "2"; "3"; " "; "A"; ... ; ""}, " ")), "", ROW($1:$99)), COLUMN(A1))
I have shortened the arrays to save space.
becomes
SMALL(IF(ISERROR({1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; ... ; 1}), "", ROW($1:$99)), COLUMN(A1))
becomes
SMALL(IF(ISERROR({1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; ... ; 1}), "", ROW($1:$99)), COLUMN(A1))
becomes
SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; ... ; 99}, COLUMN(A1))
becomes
SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; ... ; 99}, 1)
and returns 1.
Step 3 - Calculate length argument in mid function
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)))
SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; ... ; 99}, 2)-SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; ... ; 99}, 1)
becomes
5-1 equals 4.
Step 4 - Filter word
=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)))
becomes
=MID(" "&$B2&" ", 1, 4)
becomes
MID(" car 123 A ", 1, 4)
and returns "car" in cell D2.
2 Any delimiting character - array formula
The formula in cell D2 in the image above shows how to split a text string using a comma but you can use any number of characters if you like.
For example, replace this "," with the delimiting characters you want to use. Perhaps you want to use three characters example: " | ", make sure you add double quotes before and after. Replace all instances of "," with " | " in the formula below.
3. Blank as a delimiting character - Excel 365 formula
The formula in cell D2 is almost identical to the one in example 1, however, the Excel 365 LET function shortens the formula considerably. The only thing that changed is that I am using the SEQUENCE function instead of the ROW function. The SEQUENCE function works with smaller appropriately sized arrays which makes this formula more efficient.
The formula above is entered as a regular formula and it works only in Excel 365.
4 Comma as a delimiting character - Excel 365 formula
The image above demonstrates a formula that splits strings using a comma, however, you can use whatever character or characters you want.
Replace every instance of "," in the formula below with the string you want as a delimiting character. Make sure you use double quotes before and after. For example, " | ".
The formula above is entered as a regular formula and it works only in Excel 365.
5. Example 3 - Text to columns with a blank as a delimiting character
This is Rick Rothstein's formula, read his comment.
=TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A1)*999-998,999))
Explaining formula in cell D2
Step 1 - Create a string of blanks
The REPT function repeats a given text string.
REPT(" ",999)
I am not going to show 999 blanks here.
Step 2 - Substitute each blank with 999 blanks
The SUBSTITUTE function finds a given string in a value and replaces all instances of it with a specified value.
SUBSTITUTE($B2," ",REPT(" ",999))
This inserts blanks as padding between words, the TRIM function will remove the padding in a later step.
Step 3 - Create a dynamic value
The COLUMN function calculates the column number based on a cell reference. The cell reference used here is a relative cell reference meaning it changes when you copy the formula to adjacent cells.
COLUMN(A1)*999-998
becomes
1*999-998 equals 1.
In cell E2 the cell reference changes to B1
COLUMN(B1)*999-998
becomes
2*999-998
becomes
1998-998 equals 1000.
Step 4 - Get substring
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A1)*999-998,999)
becomes
MID(SUBSTITUTE($B2," ",REPT(" ",999)),1,999)
The result is a string containing 999 characters containing the first word and a lot of padding.
Step 5 - Remove blanks
The TRIM function deletes all blanks or space characters except single blanks between words in a cell value.
TRIM(MID(SUBSTITUTE($B2," ",REPT(" ",999)),COLUMN(A1)*999-998,999))
6. Example 4 - Text to columns with any delimiting character or characters
This is Rick Rothstein's formula, read his comment. This formula shows how to use a comma as a delimiting character, however, you can use whatever characters you like.
Replace "," with the characters you want to use. For example: " | ", this contains three characters.
=TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",999)),COLUMN(A1)*999-998,999))
Se section 5 for an explanation.
7. Example 4 - FILTERXML function
The FILTERXML function returns specific data from XML content by using the specified xpath, the function was first introduced in Excel 2013.
The formula works only in Excel 2013 and later versions.
Explaining formula in cell D2
Step 1 - Substitute delimiting character with xml
The SUBSTITUTE function finds a given string in a value and replaces all instances of it with a specified value.
SUBSTITUTE(B2, " ", "</B><B>")
becomes
SUBSTITUTE("car 123 A", " ", "</B><B>")
and returns
"car</B><B>123</B><B>A"
Step 2 - Append xml
The ampersand character & concatenates strings in an Excel formula.
"<A><B>"& SUBSTITUTE(B2, " ", "</B><B>") & "</B></A>"
becomes
"<A><B>"& "car</B><B>123</B><B>A" & "</B></A>"
and returns
"<A><B>car</B><B>123</B><B>A</B></A>"
Step 3 - Filter xml
The FILTERXML function returns specific data from XML content by using the specified xpath.
FILTERXML("<A><B>"& SUBSTITUTE(B2, " ", "</B><B>") & "</B></A>","//B")
becomes
FILTERXML("<A><B>car</B><B>123</B><B>A</B></A>","//B")
and returns
{"car";123;"A"}
Step 4 - Transpose values
The TRANSPOSE function allows you to convert a vertical range to a horizontal range, or vice versa.
TRANSPOSE(FILTERXML("<A><B>"& SUBSTITUTE(B2, " ", "</B><B>") & "</B></A>","//B"))
becomes
TRANSPOSE({"car";123;"A"})
and returns
{"car",123,"A"}
How to use the TextToColumns method
Weekly Blog EMAIL
Welcome! I am Oscar and here to help you out.Feel free to comment and ask Excel questions.
Make sure you subscribe to my newsletter so you don't miss new blog articles.
Related articles
The formula demonstrated above in cell range C3:C9 extracts the last word from adjacent cell in column B. =TRIM(RIGHT(SUBSTITUTE(B3, " […]
The REPT function repeats a specific text a chosen number of times. Formula in cell D3: =REPT(B3,C3) Excel Function Syntax […]
Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Split values equally into groups
Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]
The picture above shows you values that have multiple leading and trailing blanks, sometimes also between words. The TRIM function […]
Extract k-th word in cell value
The formula displayed above in cell range D3:D9 extracts a word based its position in a cell value. For example, […]
27 Responses to “Text to columns: Split words in a cell”
Leave a 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 < and > becomes >
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.
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 SubThat is it... go back to the worksheet and type some words in Column B and watch the words appear in Column D outward.
Thanks for your contribution!
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 ??
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?
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!! :(
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...
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
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)
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.
:-)
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?
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 ":")
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.
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 ("").
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))
Rick Rothstein (MVP - Excel),
Great formula! What an inprovement,your formula is incredibly small, simple and probably a lot faster than mine.
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/
How would this formula be modified if a comma is used as a separator between text fields?
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.
[…] 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… […]
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
Anil,
Which formula are you using?
=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?
Anil,
Can you provide a value that returns an error?
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
Dipankar
What is the problem?
Which column contains your data?
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.
Can you tell me what is the purpose of the column A1 in the array formula?