# How To Separate Characters Into Cells In Excel

If a cell contains words it can be easy to split these into individual cells using the ``` Text to columns ``` feature in Excel. Simply select the cells you want to split into multiple columns, navigate to the ``` Data ``` menu then click on the ``` Text to Columns ``` button. From this ``` Text Wizard ``` window select ``` Delimited ``` width (click ``` Next > ``` ), then set the delimiter type to ``` Space ``` (click ``` Next > ``` ) then click Finish.

Here are the windows from the Text to Columns Wizard :

If you’re using a Google Sheets spreadsheet you can simply use the ``` SPLIT ``` function, like so:

But how do you separate each character in a cell into individual cells?

To separate each character in the source cell ( ``` \$A\$1 ``` ) into multiple cells use the ``` MID ``` function like so in an adjacent cell, like ``` \$B\$1 ``` : ``` MID(\$A\$1, COLUMNS(\$A\$1:A1), 1) ``` and copy this formula across so that all characters from the source cell are separated into their individual cells.

## What Does The ``` MID ``` Function Do?

The ``` MID ``` function takes three parameters, and has the following signature:

``=MID(value, start, length)``

The first parameter takes a value, such as a string or a number to operate on. The second parameter is where to start capturing with ``` 1 ``` representing the first character from what was used in the ``` value ``` parameter. The third parameter is the number of characters to capture from the ``` start ``` value.

For example, ``` MID("Hello World", 4, 1) ``` would return just the 4th character ``` l ``` (as the length of the capture is just 1 character). If the formula were ``` MID("Hello world", 4, 2) ``` it would return ``` lo ``` (as the length of the capture is 2 characters).

Therefore, to extract each letter into its own cell would mean the ``` MID ``` formula would be capturing one character at a time. This would further mean the ``` length ``` property would be of value ``` 1 ``` and the ``` start ``` value would start at ``` 1 ``` and stop when it had progressed through the entire length of the cell.

Here’s an example:

Notice also from the above spreadsheet that if you try to capture an element that isn’t present in the cell, such as the third character from the word ``` HI ``` (there is none) it doesn’t error or throw an ``` #N/A ``` .

Two of the ``` MID ``` parameters are static: the first parameter references the cell to separate into individual cells, and the last parameter is just ``` 1 ``` . So how can you make the second parameter more dynamic?

By using the ``` COLUMNS ``` formula you can capture the right index of the character needed according to its position, something like so:

## Separate Characters in Cell: Summary

The ``` MID ``` formula enables users to capture a minimum of one character from a reference cell. By using the ``` MID(\$A\$1, COLUMNS(\$A\$1:A1), 1) ``` formula and copying this across (to the right) you can have all characters in the original cell ``` A1 ``` separated into individual cells.

This same technique can apply to both Microsoft Excel spreadsheets and Google Sheets as the ``` MID ``` formula is common to both platforms.

Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.