How to Extract Capital Letters with Spaces Using Excel VBA
Question details
The user needs to extract only the uppercase letters from a text string and separate each letter with a single space, using a method compatible with Excel 2013.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Generating acronyms or extracting specific capitalized initials from text strings in older versions of Excel where modern dynamic array functions are not supported.
- Observed behavior
- Because functions like LET, SEQUENCE, and TEXTJOIN are unavailable in Excel 2013, the user must rely on a custom VBA User-Defined Function (UDF) to achieve the desired text extraction.
Ensure that the Developer tab is enabled in your Excel ribbon so you can access the Visual Basic for Applications (VBA) editor. You will also need to save your workbook as a Macro-Enabled Workbook (.xlsm) to preserve the custom function.
Create a Custom VBA Function (UDF)
Use a custom VBA macro to scan the text, extract only the uppercase letters, and add a single space between each letter.
Since older versions of Excel do not support dynamic array functions like LET or TEXTJOIN, using a User-Defined Function (UDF) via VBA is the most efficient and reliable workaround.
Launch Excel, open your workbook, and press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.
In the VBA editor, click on 'Insert' in the top menu bar and select 'Module'. This will create a new standard module where you can write your custom function.
Paste your custom VBA code (e.g., ExtractCapsWSpc) into the blank module window. Ensure the code is designed to loop through each character, retain characters between ASCII 65 and 90 (uppercase letters), and concatenate them with a space.
Close the VBA editor and return to your Excel worksheet. Click on an empty cell and type the formula =ExtractCapsWSpc(A1) (assuming your source text is in cell A1), then press Enter.
Manage Macros and Custom Functions Easily with WPS Spreadsheet
WPS Spreadsheet offers excellent support for VBA macros, allowing you to easily implement custom functions like uppercase letter extraction. With full compatibility for Excel macro files, you get a seamless, lightweight, and powerful spreadsheet experience.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet or create a new one.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the main ribbon and click on 'Visual Basic' or 'Macro' to open the script editor.
- 3. Insert Your VBA Code: Right-click in the project explorer, insert a new Module, and paste your uppercase extraction VBA code.
- 4. Use Your Custom Formula: Return to your grid, type your newly created function into any cell, and press Enter to instantly see the extracted capital letters.
Frequently Asked Questions
Why doesn't the TEXTJOIN function work in my Excel version?
The TEXTJOIN function was introduced in Excel 2016 and Microsoft 365. If you are using Excel 2013 or an older version, this function is unavailable, requiring you to use a VBA custom function or helper columns instead.
How do I save an Excel file that contains a custom VBA function?
To preserve your custom VBA macros and functions, you must save your file as an Excel Macro-Enabled Workbook. Go to File > Save As, and select '.xlsm' from the file format dropdown menu.
Can I extract capital letters without VBA in older Excel versions?
While technically possible, extracting specific characters conditionally without modern array formulas or VBA requires complex, nested formulas using MID, CODE, and multiple helper columns. This is often highly impractical for long text strings, making the VBA method highly recommended.
