Function Problems

How to Automatically Append Shelf Labels to Numbers in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to automatically prepend the most recent text-based shelf label to numeric values listed in the rows immediately following it, outputting a combined string.

Product: Excel
Device & OS: not provided
Scenario: Organizing inventory or warehouse data where numeric item codes or quantities need to inherit a parent category label dynamically.
Observed behavior: Numeric values currently sit in rows below text labels without context. The goal is to output a combined string where each number inherits the closest preceding text label, such as converting a numeric value to 'Shelf 1 123'.

Before you start

Ensure your data is organized in a single column with text labels followed by their respective numeric values, and verify that your Excel version supports dynamic array functions if you plan to use the formula method.

Solution 1Recommended

Use XLOOKUP and IF Functions to Append Labels

Ideal for newer versions of Excel that support dynamic arrays, allowing you to instantly extract and combine the preceding text label with your numbers in a helper column.

This method uses an advanced formula combination to dynamically look upwards from the current row, find the nearest text value, extract its numerical part, and combine it with the current row's number.

Select the target cell

Select cell B2, or the cell in a blank column adjacent to your first data point.

Enter the lookup formula

Input the following formula: =IF(ISNUMBER(A2),"Shelf "&REGEXEXTRACT(XLOOKUP(TRUE,ISTEXT(A$1:A2),A$1:A2,,,-1),"[0-9]+")&" "&A2,""). Note that REGEXEXTRACT is available in newer builds of Excel; otherwise, standard text extraction formulas can be substituted.

Apply to all rows

Press Enter, then click and drag the fill handle at the bottom right of the cell downwards to apply this formula to the remaining rows in your dataset.

Formula Compatibility: If you are using an older version of Excel that does not support XLOOKUP or Regex functions, use the VBA macro method instead.

Solution 2

Use a VBA Macro to Automate Label Appending

Best for older versions of Excel or when you want to replace the original data in-place without adding extra helper columns.

Efficient Data Management

Easily Automate Inventory Data with WPS Spreadsheet

WPS Spreadsheet provides powerful data processing capabilities, fully supporting complex lookup formulas and built-in VBA macros. This allows you to effortlessly automate repetitive tasks like appending category labels to numerical data in your worksheets.

1. Download and Install WPS Office: Download WPS Office for free from the official website and complete the quick installation process.
2. Open Your Dataset: Launch WPS Spreadsheet and open your existing inventory data file.
3. Apply Formulas or Macros: Enter your advanced lookup formulas in an adjacent column or open the Developer tab to run your VBA script.
4. Save Your Work: Save the processed document securely in .xlsx format for full compatibility.

Fully compatible with Microsoft Excel (.xlsx) file formats, ensuring your formulas and macros work flawlessly.Supports advanced functions like XLOOKUP for dynamic data handling without extra add-ins.Includes a built-in VBA editor (in applicable versions) to run automation scripts seamlessly.Lightweight, fast, and free to use for everyday office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my XLOOKUP formula return a #NAME? error?

A #NAME? error usually occurs if you are using an older version of Excel that does not support the XLOOKUP or REGEXEXTRACT functions natively. For older versions, it is highly recommended to use the VBA macro solution instead.

Can I keep my original data untouched when appending labels?

Yes, if you use the formula method in a separate column (like Column B), your original numbers in Column A will remain untouched. The VBA macro provided, however, modifies the data in-place. You can adjust the VBA script to output to Column B if you wish to preserve the originals.

What if my text labels have a different format instead of just one letter and a number?

If your labels are formatted differently (e.g., 'Row12' instead of 'A12'), you will need to adjust the extraction logic in the VBA script from Mid(..., 2) to dynamically extract the numeric portion, or adjust the Regex pattern in the formula accordingly.

Excel & Data Problems