## Text to columns: Split words in a cell (excel array formula)

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.

**See Rick Rothstein's formula in the comments!**

### Array formula in cell D1:

Copy cell D2 and paste to cell range D2:G6.

### Explaining formula in cell D1

=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)))

**Step 1 - Split characters in a cell into an array**

=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)))** **

MID(" "&$B2&" ", ROW($1:$99), 1)

becomes

MID(" car 123 A ", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 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**

=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)))** **

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))

becomes

SMALL(IF(ISERROR({1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}), "", ROW($1:$99)), COLUMN(A1))

becomes

SMALL(IF(ISERROR({1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; #VALUE!; #VALUE!; 1; #VALUE!; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1; 1}), "", ROW($1:$99)), COLUMN(A1))

becomes

SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 99}, COLUMN(A1))

becomes

SMALL({1; ""; ""; ""; 5; ""; ""; ""; 9; ""; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 99}, 1)

and returns 1.

**Step 3 - Calculate length argument in mid function**

=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))**)** **

SMALL({1; ""; ""; ""; **5**; ""; ""; ""; 9; ""; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 99}, **2**)-SMALL({**1**; ""; ""; ""; 5; ""; ""; ""; 9; ""; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 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

**Download excel example file**

Text-to-columns-v3.xls

(Excel 97-2003 Workbook *.xls)

**Functions:**

**MID(**text, start_num, num_chars**)
**Returns the characters from the middle of a text string, given starting position and length

** ROW(**reference

**returns the rownumber of a reference**

**)**

**SMALL(**array,k**)**

returns the k-th smallest number in this data set.

**IF(**logical_test,[value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**ISERROR(**value)

Checks whether a value is an error and returns TRUE or FALSE

**SEARCH(**find_text, within_text, [start_num]**)**

Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

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

That 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 codeRight 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...

Hi! that's really interesting, I use this feature to do that:

http://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:

http://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...

http://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… […]