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