This post will guide you how to split word into different columns with a formula in Excel. How do I split word or number into separate cells with VBA Macro code in Excel 2010/1013/2016.

• Split Word into Different Cells with Formula
• Split Word into Different Cells with VBA Macro

Split Word into Different Cells with Formula

---

Assuming that you have a list of data in range B1:B4, and you need to split text value or a word into separate cells in your worksheet. How to do it. You can use a formula based on the MID function and the COLUMNS function. Like this:

=MID($B1,COLUMNS($B$1:B$1),1)

Type this formula in cell C1 and press Enter key, and then drag the AutoFill Handle over to other cells in Row1.

Then you still need to drag the AutoFill Handle down to other cell to apply this formula for B2:B4.

Split Word into Different Cells with VBA Macro

---

You can also use an Excel VBA Macro code to achieve the same result of splitting word into different cells. Here are the steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one Range that you want to split words. Click Ok button.

#7 Select one destination Cell that you want to place. Click Ok button.

#8 Let’see the result:

Related Functions

---

• Excel MID function
  The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
• Excel COLUMNS function
  The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….