Goal: A macro that will extract the first word in each cell string in a selected cell range.

This post will show you one way to extract the first word from a string in a selected range in Excel. In this example, we want to delete the ancillary details of professional designations after a person’s surname.

Sure we can delete each cell separately, but if we have a large data set, then it is preferable to write a customized macro.

Writing the macro

In summary, we want a macro that will extract only the first word in each string across the selected cell range.

Go into Visual Basic Editor, and insert a module.

Remember to notate your macro, as it will enable future users to analyze your macro if needed. We will call this macro firstLeftWord

Disabling the following Excel features will improve the macro’s performance. Incorporating an error handler in the macro will avoid a run-time error, if there are cells in the selected range that don’t require any formatting by the macro.

Next, we want to declare certain variables in our macro, which will improve the macro’s performance and avoid run-time errors.

Our object range will be the range of cells selected by a user. This code will loop through all the selected cells, and extract the first word in each cell’s string.

The line “Next cell” will move the macro to the next cell in the selected range

The is almost complete. We can now re-enable the Excel properties, which we earlier disabled before our macro was triggered.

Running the macro

Our macro is complete. Let’s review the macro and ensure it is 100% accurate. We can now test the macro on the selected data range.

The macro is correctly extracting the first word in the string in each cell.

Here is a free download of the following Excel workbook. It contains the macro to extract the first word in a string across a selected range of cells.