Article updated on January 15, 2018

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:

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

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

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)