Function Problems

How to Automatically Concatenate Names and Number Sequences in Excel

Kushani Nimanthika Oct 8, 2026 873 views

Question details

The user wants to generate repeated combinations of names and sequential numbers (such as Adam-01, Adam-02) without typing them manually.

How to Automatically Concatenate Names and Number Sequences in Excel

Product: Excel
Device & OS: not provided
Scenario: Combining name text with sequential numbers into a single column for a large data set.
Observed behavior: The goal is to efficiently populate a column with concatenated name-number strings using automated formulas or query workflows.

Before you start

Ensure your names and numbers are organized into separate, clean columns before combining them. If you plan to use Power Query, convert your data range into a formatted Table first.

Solution 1Recommended

Use an Excel Formula with the TEXT Function

The quickest way to combine names and numbers while maintaining a specific double-digit format (like '01', '02').

By using the ampersand (&) operator alongside the TEXT function, you can append a sequential number to a name and ensure it retains its leading zero.

Select the result cell

Click on the first empty cell in your intended result column where you want the combined text to appear.

Enter the concatenation formula

Type the formula =A2&"-"&TEXT(B2,"00"), assuming cell A2 contains the name and cell B2 contains the sequential number.

Apply the formula

Press Enter to see the combined result (e.g., 'Adam-01').

Fill down the column

Click and drag the small square (fill handle) at the bottom right corner of the cell down the column to copy the formula to the rest of your dataset.

Use an Excel Formula with the TEXT Function

Formatting Flexibility: You can change '00' to '000' in the TEXT function if your dataset requires a three-digit sequence, such as 'Adam-001'.

Solution 2

Concatenate Using Power Query

Ideal for automating combinations on massive datasets or when importing data from external sources.

Smart Spreadsheet Solution

Quickly Concatenate Data with WPS Spreadsheet

WPS Office offers a fully compatible spreadsheet tool where you can easily use text formulas like CONCATENATE or the '&' operator to combine names and sequential numbers seamlessly.

1. Open your dataset: Launch WPS Spreadsheet and open the file containing your names and numbers.
2. Input the formula: In an adjacent empty column, type =A2&"-"&TEXT(B2,"00") and press Enter.
3. Auto-fill the remaining rows: Double-click the fill handle at the bottom-right corner of the cell to instantly fill the sequence for your entire dataset.

Fully compatible with Microsoft Excel formulas and .xlsx file formats.Lightweight software that processes large data sets without lagging.Familiar user interface with virtually no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my concatenated number missing leading zeros?

If you simply use =A2&"-"&B2, the spreadsheet will treat the number purely as a value and drop any leading zeros. You must wrap the number cell reference in the TEXT function, like TEXT(B2,"00"), to force the leading zero to remain in the final text string.

Can I use the CONCATENATE function instead of the ampersand (&) symbol?

Yes. You can use the formula =CONCATENATE(A2, "-", TEXT(B2, "00")). This achieves the exact same result as using the ampersand (&) operator, though many users find the ampersand method faster to type.

How do I troubleshoot errors in my Power Query applied steps?

Go to the Data tab, click Queries and Connections, right-click your query, and select Edit. In the Power Query Editor, click through each item in the 'Applied Steps' pane on the right side to see exactly at which step the data transformation failed.

Is there a way to generate sequential numbers automatically if I don't have a number column?

Yes. If your names are listed in column A and you want to number them as they go down the rows, you can use the ROW function. Try a formula like =A2&"-"&TEXT(ROW(A1),"00") and drag it down.

Excel & Data Problems