How to Split Word into Different Cells in Excel

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


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.

split word into different cells1

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

split word into different cells2

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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

split word into different cells3

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

split word into different cells4

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

split word into different cells5

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

split word into different cells6

#8 Let’see the result:

split word into different cells7

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

 

Comments

So empty here ... leave a comment!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sidebar