How to create increment number with or inside text in Excel?
In Excel, generating sequences of incrementing numbers is a fundamental task, especially for organizing data or creating unique identifiers. While the AutoFill Handle feature is an easy way to fill continuous standalone numbers, it doesn’t support combining incrementing numbers directly with text strings, such as generating "User001", "User002", and so on in a single step. This limitation can complicate tasks like creating custom usernames, email addresses, product codes, or invoice numbers that require both static text and a sequential numeric component. In this tutorial, we'll introduce several methods to create increment numbers integrated with or inside text strings in Excel, ranging from classic formulas to advanced automation with VBA and user-friendly specialized tools.
Common scenarios include: generating batches of uniquely labeled items, creating email lists with systematic naming, or producing serial numbers for product tracking. Each solution has its own advantages—whether it's ease of use, automation, flexibility in number placement, or suitability for large datasets.
Increment number with text by formula in Excel
Increment number with text with Kutools for Excel
VBA macro: create incrementing numbers within or inside text
Increment number with text by formula in Excel
Excel’s built-in formulas provide a straightforward method for combining text and incrementing numbers. This solution is particularly useful if your sequence is structured with fixed position numbers and text, and you need full transparency in how the identifier is composed.
1. Prepare your worksheet as illustrated below:
2. In cell D2, enter the following formula, then press Enter to apply:
=$A$2&TEXT(B2,"000")&$C$2
After the formula returns the result, drag the Fill Handle from D2 down to fill the rest of the cells in column D. Each cell will display the combined text and incremented number, such as “User001@localdomain.com”, “User002@localdomain.com”, etc., as shown below:
Notes and tips:
$A$2 and $C$2) to keep the text elements fixed when copying the formula down.This approach is highly transparent and ideal for well-structured data but often requires manual setup of helper columns for each component. Also, if your text and number placement varies from row to row, this approach may require extra modifications.
Increment number with text with Kutools for Excel
If you frequently need to generate incrementing text identifiers and prefer a graphical interface or want more dynamic options (such as flexible prefix, suffix, custom increments, or remembering your settings for repeated use), Kutools for Excel offers a specialized feature called Insert Sequence Number. With this tool, you can create complex numbering schemes in just a few clicks—no formula setup required.
1. On the Excel ribbon, go to Kutools > Insert > Insert Sequence Number… as shown below:
2. In the Insert Sequence Number dialog box, click New to expand additional setup options. Here’s how to configure:
3. Select the range of cells where you want to insert the incrementing numbers with text, then click Fill Range. The customized numbers will instantly populate the selected area, yielding results like “User001@localdomain.com”, “User002@localdomain.com”…
Additional notes: Once you assemble and save a numbering template in Kutools, it can be quickly reused, even across multiple workbooks. If the last generated number was 30, the next time you use this sequence, the numbering will automatically resume from 31. This is especially helpful for avoiding duplicate numbers or tracking across large datasets.
This method is recommended for users who work with sequential data regularly and want both efficiency and flexibility in a point-and-click interface, plus template saving for recurring batch tasks. If you require advanced, position-based customization beyond prefix/suffix, automated scripting may be more suitable (see below).
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
Increment number with text with Kutools for Excel
VBA macro: create incrementing numbers within or inside text
If you require maximum control—such as inserting an incrementing number into any part of a text string, generating complex identifiers on the fly, or applying this operation to multiple rows at once—a VBA macro provides automation and customization that formulas or add-ins can’t always match. VBA is ideal for bulk operations, customized patterns, or when needing to embed numbers anywhere (not just at the start or end) of the string.
Before you begin, always remember to save your workbook as a macro-enabled file (.xlsm) to prevent loss of your VBA code. Additionally, consider testing any macro on a copy of your data first to avoid unexpected changes.
How to use the macro:
1. Open your workbook, then on the Excel ribbon click Developer Tools > Visual Basic. (If the Developer tab is not visible, enable it via File > Options > Customize Ribbon.)
2. In the Microsoft Visual Basic for Applications editor window, click Insert > Module. Copy and paste the following code into the blank module:
Sub InsertIncrementNumberIntoText()
Dim cell As Range
Dim rng As Range
Dim startNum As Long
Dim increment As Long
Dim pattern As String
Dim position As Variant
Dim currentNum As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select the range to apply incremented text.", xTitleId, rng.Address, Type:=8)
startNum = Application.InputBox("Enter the starting number:", xTitleId, 1, Type:=1)
increment = Application.InputBox("Enter the increment between numbers:", xTitleId, 1, Type:=1)
pattern = Application.InputBox("Enter the pattern (use {n} to mark the insertion point, e.g. 'User{n}@local.com'):", xTitleId, "User{n}@local.com", Type:=2)
currentNum = startNum
If rng Is Nothing Then Exit Sub
For Each cell In rng
cell.Value = Replace(pattern, "{n}", currentNum)
currentNum = currentNum + increment
Next
End Sub3. To execute the macro, click the 
button or press F5. A dialog will guide you to select the target range, set your starting number, choose the increment, and define your desired text pattern (for example, use ID-{n}-LIST to embed an incrementing number between custom text segments).
- You can insert "{n}" anywhere in your pattern—before, after, or inside the text—to customize the placement of the incremented number.
- After you confirm, each selected cell is filled with the appropriately incremented identifier based on your chosen pattern and interval.
- To apply the macro to a new area, simply re-run it and specify new parameters as needed.
- If you make a mistake, use the Undo feature (Ctrl+Z) or run the macro again.
Troubleshooting and tips:
{n}. Patterns without this token will be filled literally, so double-check your pattern before running.This solution is especially useful for advanced scenarios such as embedding numbers within filenames, creating patterned IDs across multiple rows at once, and automating complex sequential naming schemes.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in