VBA Function To Calculate Number of Words in a String

Associated Files Download Links

Count Number of Words in a String

The following VBA function counts the number of words in a string by searching for spaces within the string. It uses the MID Function. You could accomplish something similar with the SEARCH or FIND Functions.

It is worth noting a couple of points:
• This code must be inserted in a Workbook Module – from the VBA pane, click on Insert and then module

201 no of words in a string 400x241

• The function can be called from an excel workbook cell either with an either explicit text or cell reference as an argument:
A3 = Number_of_Words(“Pig Dog Cat”)
A4 = Number_of_Words(D1)

The function works by traversing the length of a string and seeing if the next character is a space and if so it adds one to the number of spaces in the string.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro – A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

alt text

Learn More!

<<Return to VBA Examples

Did you find this VBA tutorial useful? Then share it with your friends and colleagues:

You may also like some of this related content...

VBA has many string functions that will allow you to manipulate and work with text
In VBA, you can compare two strings using the StrComp function. In this tutorial, you
You can perform logic on, or return individual characters from a string in VBA by
Add Spaces to a String A common way to add spaces to a string with
Extract Numerical Portion of String The following function will extract the numerical portion from a