How to Insert Spaces Before Capital Letters using VBA in Excel
Question details
The user needs to convert CamelCase text strings into readable text by inserting spaces before the internal uppercase letters.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Cleaning and formatting joined text data (like database column names) into readable labels within a spreadsheet.
- Observed behavior
- Text strings such as CustomerID, CustomerName, and CustomerAddress are joined together. The goal is to programmatically separate them using a VBA routine.
Before running VBA macros, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) to prevent losing your code. It is highly recommended to test the macro on a copy of your data first.
Use a Custom VBA Function to Split CamelCase Strings
Create a User-Defined Function (UDF) in VBA that iterates through the string characters and inserts a space whenever an internal uppercase letter is detected.
This approach allows you to use the custom VBA code directly in your worksheet cells, just like a native Excel formula.
By looping through each character, the routine identifies capital letters and safely inserts a space before them while preserving the very first character of the string.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.
In the top menu, click 'Insert' and select 'Module'. This creates a blank canvas for your code.
Paste a VBA string manipulation routine designed for splitting CamelCase (such as the splitbreak function) into the module window.
Close the VBA editor. Click on an empty cell in your spreadsheet and type your new custom function name, for example =SplitCamelCase(A1), to format your text.
Use Underscores Instead of CamelCase (Alternative Strategy)
If you have control over the source data generation, using underscores instead of CamelCase allows you to use standard Excel features rather than VBA.
Easily Run VBA Macros to Format Data in WPS Spreadsheet
WPS Spreadsheet offers robust support for VBA macros, allowing you to seamlessly run CamelCase splitting routines, build custom User-Defined Functions, and automate your workflow with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the CamelCase text.
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic' to open the macro editor.
- 3. Insert Your VBA Routine: Create a new module, paste your custom CamelCase splitting script, and save it.
- 4. Use the Custom Formula: Return to your spreadsheet and use the new custom formula to cleanly format your text data.
Frequently Asked Questions
Can I split CamelCase text without using VBA?
Yes. In newer versions of spreadsheet software, you can use Power Query to split text by transitioning from lowercase to uppercase. Alternatively, complex combinations of standard text formulas (like SUBSTITUTE, MID, and CONCAT) can work, though they are generally much harder to build and maintain than a VBA script.
Why does my VBA code add spaces between consecutive capitals like in 'CustomerID'?
A basic VBA loop will check for any uppercase letter and add a space before it. To prevent spaces between acronyms, the VBA code needs a conditional check (often using Regular Expressions or checking ASCII values) to verify if the preceding character was lowercase before inserting a space.
How do I save my workbook after adding the VBA code?
To preserve your macros, you must save the document as a Macro-Enabled Workbook (*.xlsm). If you attempt to save it as a standard .xlsx file, the application will prompt you, and if you proceed, your VBA code will be permanently removed.
