How to Create a Number Sequence with Text in Excel (5 Methods)

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
TEXT(ROW(A1)-1,”-0000-“)—-> simplifies to
TEXT(0,”-0000-“)—-> turns into the text format of 0.
- Output: ‘-0000-’
$B$5&TEXT(ROW(A1)-1,”-0000-“)&$C$5—-> results into
“user” & “-0000-” & “TW”—-> returns the username.
- Output: user-0000-TW

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
TEXT(ROW(A1)-1,”-0000-“)—-> becomes
TEXT(0,”-0000-“)—-> turns into the text format of 0.
- Output: ‘-0000-’
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

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
TEXT(ROWS(A$1:A1)-1,”-0000-“)—-> turns into
TEXT(0,”-0000-“)—-> returns the text format of 0.
- Output: ‘-0000-’
$B$5&TEXT(ROW(A1)-1,”-0000-“)&$C$5—-> becomes
user&-0000-&TW—-> returns the username.
- Output: user-0000-TW

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.