VBA & Macro Problems

How to Extract First Names in Excel While Ignoring Prefixes and Initials

John WilsonJohn Wilson Oct 29, 2026 870 views

Question details

The user needs to extract the actual first name from a full name string in Excel by explicitly skipping common prefixes and single or double-letter initials.

Product
Excel
Device & OS
not provided
Scenario
Cleaning up a dataset containing full names where entries have varying prefixes (like Mohd, Syed, Kumari) and initials before the actual first name.
Observed behavior
Standard Excel text formulas cannot easily dynamically skip variable prefixes and initials, requiring a custom VBA solution to correctly isolate the target name.
Before you start

Ensure you have the Developer tab enabled in Excel and save your workbook as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use a Custom VBA Function to Skip Prefixes and Initials

Create a user-defined function in VBA to parse the name string, identify and skip specific prefixes, and extract the first valid name.

This custom VBA function splits the full name into individual words, checks each word against a predefined list of prefixes (such as Mohd, Mohammad, Md, Kumari, Syed), and ignores one or two-letter initials.

Note that this logic might accidentally skip actual short names like 'Al', 'Ed', or 'Jo'. Review the extracted results carefully to ensure data accuracy.

1
Open the VBA Editor

Press 'Alt + F11' in Excel to open the Microsoft Visual Basic for Applications (VBA) editor.

2
Insert a New Module

Click on 'Insert' in the top menu and select 'Module' to create a blank script window.

3
Write the Custom Function

Create a function named GetFirstName. Use the 'Split' function to separate the name by spaces, then loop through the array to check each part sequentially. Skip strings matching your prefix list ('Mohd', 'Mohammad', 'Md', 'Mohammed', 'Muhammad', 'Km', 'Kumari', 'Syed') and strings with a length of 2 or less.

4
Return the Valid Name

Set the function to return the first string that does not match the skip conditions, then exit the function loop.

5
Apply the Function in Excel

Return to your worksheet, select a blank cell next to your data, and type '=GetFirstName(A2)' (assuming the full name is in cell A2). Press Enter and drag the fill handle down to apply it to other rows.

Use a Custom VBA Function to Skip Prefixes and Initials
Handling Short Names: Because the code ignores 1 or 2 letter words to filter out initials, valid short names (e.g., Bo, Ty, Ed) will also be skipped. You may need to add exception rules in the VBA code if your dataset contains these names.
Advanced Spreadsheet Capabilities

Extract and Process Text Easily with WPS Spreadsheet

WPS Office offers robust Spreadsheet capabilities, fully supporting VBA macros to automate complex data extraction tasks like ignoring name prefixes.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and open your name dataset in WPS Spreadsheet.
  2. 2. Enable Developer Tools: Go to the 'Developer' tab and click on 'VBA Editor' (ensure the VBA module is installed if prompted).
  3. 3. Add the Custom Function: Insert a new module, paste your GetFirstName VBA code, and close the editor.
  4. 4. Extract the Names: Use '=GetFirstName(A2)' in your sheet just like in Excel, and instantly clean your data.
Fully compatible with Microsoft Excel VBA macros and functions.Lightweight, fast installation, and easy-to-use interface.Advanced text processing tools like Flash Fill for quick data extraction.Cost-effective and highly efficient for everyday office tasks.
microsoft office alternative - wps office

Frequently Asked Questions

Can I extract first names without using VBA?

Yes, you can use the built-in Flash Fill feature (Ctrl + E). Simply type the desired first name in the adjacent column for the first few rows, and the software will attempt to recognize the pattern and fill in the rest. However, Flash Fill might struggle with highly irregular prefixes compared to a VBA script.

Why is my custom VBA function returning a '#NAME?' error?

This error occurs if the software doesn't recognize the function name. Ensure that you have placed the VBA code inside a standard Module (not a Sheet module or ThisWorkbook), that macros are enabled in your security settings, and that you spelled the function correctly in your formula.

How do I add a new prefix to the ignore list in the VBA code?

You can modify the conditional 'If' statement or 'Select Case' block inside your VBA function to include the new prefix. For example, add 'Case "Dr.", "Mr."' to explicitly skip professional titles alongside the existing list of names.