VBA & Macro Problems

How to Convert Structured Codes to Descriptions in Excel using VBA

Steven Parker Oct 23, 2026 869 views

Question details

The user needs a method to convert formatted alphanumeric codes (like P10S1AS01) into expanded, human-readable text descriptions (like Plant 10 Store 1 Section A Shelf 01).

Generate Readable Excel Descriptions from Structured Codes

Product: Excel
Device & OS: not provided
Scenario: Automating the translation of standardized internal codes into detailed text descriptions to improve reporting and data readability.
Observed behavior: The user requires a custom formula or VBA function capable of interpreting specific string segments and appending appropriate labels, while also handling conditions where certain code segments might be missing.

Before you start

Ensure your Excel Developer tab is enabled so you can access the Visual Basic Editor, and be prepared to save your file as a Macro-Enabled Workbook (.xlsm).

Solution 1Recommended

Create a Custom VBA Function (UDF) to Parse the Code

Build a User Defined Function using VBA to automatically detect substrings within your code, extract the alphanumeric variables, and concatenate them with their corresponding descriptive labels.

Standard Excel formulas can struggle with complex string splitting, especially when the length of characters varies. A custom VBA function allows you to use text parsing logic to identify delimiters (like 'S' or 'A') and extract the exact segments you need.

By implementing an IF condition inside your VBA code, you can also prevent labels (such as 'Shelf') from appearing when the corresponding section of the product code is intentionally left blank.

Open the Visual Basic Editor

Press Alt + F11 on your keyboard to launch the Visual Basic Editor. In the top menu, click 'Insert' and select 'Module' to create a new blank standard module.

Write the Parsing Logic

Define a new function, such as 'Function ParseCode(s As String) As String'. Use the 'InStr' and 'Mid' functions to locate your target letters (like 'P' for Plant or 'S' for Store/Shelf) and extract the numbers immediately following them. Add conditional logic to check if the extracted string length is greater than zero before appending the final label to avoid trailing text when parameters are missing.

Apply the Function to Your Cells

Close the Visual Basic Editor and return to your worksheet. In the destination cell (e.g., B2), type =ParseCode(A2) and press Enter. Click and drag the fill handle down to apply your new custom description generator to the rest of your data list.

Save as a Macro-Enabled Workbook

Go to File > Save As, and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown menu. This is required to ensure your custom VBA script is saved and functions correctly the next time you open the document.

Create a Custom VBA Function (UDF) to Parse the Code

Tip for Dynamic Handling: If you occasionally subtract parts of the code (such as removing S01), ensure your VBA script counts the remaining characters using the 'Len' function so the macro dynamically drops the final descriptor.

Efficient Data Processing with WPS Spreadsheet

Easily Manage Custom VBA Macros in WPS Office

WPS Spreadsheet fully supports Excel VBA macros and custom functions, allowing you to seamlessly process complex string manipulation tasks and automate your data workflows without compatibility issues.

1. Access the Developer Tools: Open your data file in WPS Spreadsheet, navigate to the 'Developer' tab on the ribbon, and click 'VBA Editor'.
2. Insert Your Script: Right-click your workbook project, insert a new Module, and define your custom string parsing function.
3. Run the Custom Formula: Return to your grid, type your custom formula (e.g., =ParseCode(A2)), and fill the column to automatically generate your descriptions.
4. Save the Document: Save your file as a .xlsm document natively within WPS to preserve your new automation tools.

Full compatibility with Microsoft Excel VBA scripts and XLSM file formatsBuilt-in Developer tools for writing, debugging, and testing custom functions nativelyLightweight application architecture ensuring fast processing speeds for heavy data setsFree and intuitive interface that requires no learning curve for existing Office users

microsoft office alternative - wps office

Frequently Asked Questions

Can I prevent a label from appearing if a part of the code is missing?

Yes. Within your VBA function, you can use conditional statements (like an IF statement) to check if the extracted substring is empty. If it is empty, you can instruct the code to skip appending that specific label (e.g., 'Shelf') to the final result string.

Why does my custom Excel function return a #NAME? error?

A #NAME? error typically occurs if macros are disabled in your workbook security settings, if the VBA code was placed in a worksheet object rather than a Standard Module, or if there is a spelling mistake when typing the function name into the cell.

Do I need to re-write the VBA code every time I open my spreadsheet?

No. As long as you save your file in the Macro-Enabled Workbook format (.xlsm), the VBA script is embedded within the document. It will be available automatically whenever you open the file, provided you click 'Enable Macros' when prompted.

Excel & Data Problems