Sometimes we want to fill 1,2,3,4,5… into each cell in a column, for implement this we can enter 1 in the first cell and then drag fill handle down to fill the following cells. But if there are some texts exist with number in cell for example Student001-1-A, we drag down the fill handle, the other cells are only copied the original value, number 001 will not be increased automatically. So, we need to find a simple way that to make number with texts continuous in cells. This article will provide you a simple solution to** create increment number with text** in cells easily.

**Precondition:**

See screenshot below. For student, we use 001,002,003…to identify them, we can type text like ‘Student001-1-A’ one by one manually, but if there are 100 or 1000 students, it will spend a long time to finish this task. So we need simple ways to solve this issue.

Table of Contents

## 1. Create Increment Number with Texts by ‘&’ in Excel

As we can see the texts are combined as ‘**Student00x-1-A**’, so we can separate them firstly, then create increment number in one column, and at last combine all texts again. Please see below steps for details.

**Step1:** Separate texts in column A to two parts.

(Make sure** -1-A** is saved in **Text **format, otherwise error will be displayed)

**Step2:** Select the range covers column C and column D, drag fill handle down to create increment numbers in column C, and text in column D is copied and pasted.

**Step3:** In cell E2 enter the formula:

` =C2&D2`

**Step4:** Drag down the fill handle in column E till reaching the last data.

**Step5:** Just copy column E, in column A, right click select **Paste Special->Paste Values** (the first choice).

Then increment numbers within texts are created properly.

## 2. Create Increment Number Within Texts by Formula in Excel

We can see that in texts ‘**Student001-1-A**’, except ‘**001**’ part, other parts are unchanged. So we can extract this part, and then create increment number based on it, and then use formula to combine all parts together.

**Step1: **Separate texts ‘Student001-1-A’ to three parts. In column C save ‘Student’, column D save ‘1’, column E save ‘-1-A’.

**Step2: **On D2, drag fill handle down to create increment numbers.

**Step3: **In F2 enter the formula:

` =$C$2&TEXT(D2,"000")&$E$2`

**Step4: **Click **Enter** to check result.

**Step5: **Drag the fill handle down to fill cells till the last data.

Then you can refer to above method step#5 to copy and paste data properly.

## 3. Video: Create Increment Number with Texts in Excel

This video will demonstrate how to create an increment number with texts in Excel using the concatenate operator and TEXT formula.

## 4. Related Functions

- Excel Text function

The Excel TEXT function converts a numeric value into text string with a specified format. The TEXT function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the TEXT function is as below: = TEXT (value, Format code)…