It’s that time again folks. Time for some more #MacroMonday fun. Today I want to show you how to write a simple #Excel macro that will take a sentence (or a load of text) and split it into individual words. I want each word to be in its own cell in Column B of my Excel worksheet. Here is a sample of the original text and the desired outcome.

Preparing To Write The Macro.

First, you will need to open the Visual Basic Editor.  There are two ways to do this.  Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic.  Both methods have the same result.   You then have a choice, you can either create a module to store your code either in your Personal Macro Workbook or in your current workbook.  What’s the difference?.  If you save the macro in your Personal Macro workbook it will be available for use in any of my Excel workbooks.  If you store it in the current workbook then use is restricted to that workbook.

This macro will be useful in the future so I am going to save it in my Personal Macro Workbook.

More About Your Personal Macro Workbook (PMW).

If you want to read more about your Excel PMW then check out my blog posts below.

Macro Mondays -Creating and Updating Your Personal Macro Workbook

Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook

Why Is My Personal Macro Workbook Not Loading Automatically?

Preparing To Write The Macro.

Let’s start off the macro by inserting a New Module.  Do this by selecting the Personal.xlsb workbook, then Insert Module. Type Sub then the name of your macro.  In this example, I have called it simply ConvertToWords.  Notice that Excel will automatically enter the end text End Sub to end the Sub Routine.  We simply need to enter the rest of the code between these two lines.

[stextbox id=’info’]
Sub ConvertToWords
End Sub
[/stextbox]

Declaring Variables.

Next, we will need to declare either one or more variable. This just ensures that Excel allocated some memory to store that information. We need to just declare two variables in the macro.

[stextbox id=’info’]
Dim sText As String
Dim arText As Variant
[/stextbox]

sText, in this case, represents our sentence which we are going to split into words. arText represents the array of cells that will contain the separated words in Column B in my worksheet.

Using The Range Object.

Now, we set the range object which is the representation of a cell (or cells) on your worksheet. In this example, we are setting sText as the value of cell A1.

[stextbox id=’info’]
sText = Range(“A1”).Value
[/stextbox]

Using The SPLIT Function.

Next, we use the SPLIT function to split the value of sText. the syntax of SPLIT is as follows.

Split ( expression [,delimiter] [,limit] [,compare] ) where

The string to split into substrings based on a delimiter. The delimiter Optional. The delimiter is used to split the expression into substrings. If not specified, the delimiter will default to a space character. The argument of limit is optional. This is the maximum number of substrings split from expression. If not specified, the limit will default to -1 which will split out all substrings. The compare argument is optional. This is the type of comparison to perform when parsing the substrings and can be one of the following:

VBA Constant	Value	Explanation
CompareMethod.Binary	0	Binary comparison
CompareMethod.Text	1	Textual comparison

[stextbox id=’info’]
arText = Split(sText, ” “)
[/stextbox]

Transposing The Sentence.

Finally, we use the Excel VBA Ctsr and Ubound functions. These convert the value in our range to a string value. Rhe range length is determined by 1+ the length of the arText array. Finally, the strings are transposed using the Transpose Worksheet Function.

[stextbox id=’info’]
Range(“B1:B” & CStr(1 + UBound(arText))).Value = WorksheetFunction.Transpose(arText)
[/stextbox]

Ending The Macro.

The routine then ends with the End Sub piece of code.  This was already entered into the module for us when started the type the name of the Macro.

[stextbox id=’info’]
End Sub
[/stextbox]

If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.

Likewise, if you want to see all of the blog posts in the Macro Mondays Series Click The Link Below

 

How To Excel At Excel – Macro Mondays Blog Posts.

 

So, Don’t forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.