**Watch Video – Create a Number Sequence with Text in Excel**

**Method 1 – Using Excel TEXT and ROW Functions to Create a Number Sequence with Text**

**Steps:**

- Enter the following formula in cell
**D5:**

`=$B$5&TEXT(ROW(A1)-1,"-0000-")&$C$5`

**Formula Breakdown**

The formula uses the **TEXT **and **ROW** functions and returns the username. Here’s how this formula works:

**ROW(A1)—->**returns the row number of cell**A1**, which is**1**.**ROW(A1)-1—->**becomes- Output:
**0**

- Output:
**TEXT(ROW(A1)-1,”-0000-“)—->**simplifies to**TEXT(0,”-0000-“)—->**turns into the text format of**0**.- Output: ‘
**-0000-**’

- Output: ‘
**$B$5&TEXT(ROW(A1)-1,”-0000-“)&$C$5—->**results into**“user” & “-0000-” & “TW”—->**returns the username.- Output:
**user-0000-TW**

- Output:

- Press
**ENTER,**and you will see the first**User ID**in**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

You can create a **number sequence** in Excel with text using **TEXT **and **ROW **functions.

**Read More:**

**Method 2 – Applying Ampersand and TEXT Function to Create a Text Number Sequence**

**Steps:**

- Insert some serial numbers in column
**C.** - Enter the following formula in cell
**D5:**

`=$B$5&TEXT(D5,"-0000-")&$C$5`

We have some serial numbers in column **D**. The **TEXT** function converts the text format of the number in **D5** to **-0000- **and the **Prefix **and **Suffix **of the **User ID **are then added by the **Ampersands**.

- Press
**ENTER,**and you will see the first**User ID**in**D5**.

- Drag the
**Fill icon**downwards to**AutoFill**the lower cells.

Following the above procedures, you can generate an auto number sequence in Excel with text using the** Ampersand** symbol and the **TEXT** function.

**Method 3 – ****Creating a Number Sequence ****Using Excel CONCAT or CONCATENATE Function**

**Steps:**

- Enter the following formula in cell
**D5:**

`=CONCAT($B$5,TEXT(ROW(A1)-1,"-0000-"),$C$5)`

**Formula Breakdown**

The formula uses the **CONCAT**, **TEXT, **and **ROW** functions and returns the username. Here’s how this formula works:

**ROW(A1)—->**returns the row number of cell**A1**, which is**1**.**ROW(A1)-1—->**results into- Output:
**0**

- Output:
**TEXT(ROW(A1)-1,”-0000-“)—->**becomes**TEXT(0,”-0000-“)—->**turns into the text format of**0**.- Output: ‘
**-0000-**’

- Output: ‘
**CONCAT($B$5,TEXT(ROW(A1)-1,”-0000-“),$C$5)—->**becomes**CONCAT(“user”,”-0000-“,”TW”)—->**adds all the text parts and returns the username.- Output:
**user-0000-TW**

- Output:

- Press
**ENTER,**and you will see the first**User ID**in**D5**.

- Use the
**Fill Handle**to**AutoFill**the lower cells.

You can create a **number sequence** in Excel with text using the** CONCAT** function.

You can use the** CONCATENATE** function for this purpose. There is no difference between these two formulas except their function name.

`=CONCATENATE($B$5,TEXT(ROW(A1)-1,"-0000-"),$C$5)`

**Method 4 – Applying the ROWS Function to Create a Number Sequence with Text**

**Steps:**

- Enter the following formula in cell
**D5:**

`=$B$5&TEXT(ROWS(A$1:A1)-1,"-0000-")&$C$5`

**Formula Breakdown**

This formula uses the **TEXT **and **ROWS** functions and returns the username. Here’s how it works:

**ROWS(A$1:A1)—->**returns the number of rows between cell**A1**and**A1**, which is**1**.**ROWS(A$1:A1)-1—->**then becomes- Output:
**0**

- Output:
**TEXT(ROWS(A$1:A1)-1,”-0000-“)—->**turns into**TEXT(0,”-0000-“)—->**returns the text format of**0**.- Output: ‘
**-0000-**’

- Output: ‘
**$B$5&TEXT(ROW(A1)-1,”-0000-“)&$C$5—->**becomes**user&-0000-&TW—->**returns the username.- Output:
**user-0000-TW**

- Output:

- Press
**ENTER,**and you will see the first**User ID**in**D5**.

- Drag the
**Fill**icon downwards to**AutoFill**the lower cells.

Using the steps described above, you can create a **number sequence** in Excel with text using the **ROWS** functions.

**Method 5 – Implementing Flash Fill Command**

**Steps:**

- Enter the following texts, like the following image.

- Notice that there is a column named
**Number Sequence**. We formatted the numbers for this column. We selected**C5:C10**and then went to**Number Format**>>**More Number Formats…**

- Select
**Number**>>**Custom**and type –**0000**—in the type section, as our username contains the –**0000**—number format. - Click
**OK**.

See that 0 becomes -0000-. Similarly, 1 will be seen as -0001- and so on.

- Manually enter the first
**User ID**.

- Select that
**User ID**and go to**Data**>>**Flash Fill**or just press**CTRL+E**. This operation will return other usernames in the following cells.

All the **User IDs **are automatically filled with blank cells.

Following this approach, you can create a **number sequence** in Excel with text using the **Flash Fill **command.

## Practice Section

Using the above methods, try to put the number sequence at the first or second part of the usernames.

**Related Articles**

- How to Perform Numbering in One Cell in Excel
- How to Add Numbers 1 2 3 in Excel
- How to Increment Row Number in Excel Formula
- How to Create a Number Sequence in Excel Based on Criteria
- How to Use Number with Filter in Excel
- Subtotal Formula in Excel for Serial Number

**<< Go Back to Serial Number in Excel | Numbering in Excel | Learn Excel**