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

Get FREE Advanced Excel Exercises with Solutions!

The article will show you some effective methods on how to create a number sequence in Excel with text. We need to add a series of numbers with sequence within one or multiple texts to generate automated usernames, passwords, invoice or product ID etc. This procedure helps us to keep records on something in the system. Fortunately, Excel is very efficient in this case. I’ll show you some simple effective techniques to generate sequential numbers with text in this article.


Download Practice Workbook


5 Ways to Create a Number Sequence with Text in Excel

In this article, I’ll be explaining the process of creating a number sequence with some User IDs. In the dataset, we have two text parts, one is the prefix, which will be in the first part of our User ID and the suffix part will be in the last part of the ID. There will be some sequential numbers in between these texts.

how to create a number sequence in excel with text


1. Using Excel TEXT and ROW Functions to Create a Number Sequence with Text

We can use Excel TEXT and ROW functions to create a number sequence with text. Let’s go through the description below.

Steps:

  • First, type 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
  • After that, press the ENTER button and you will see the first User ID in D5.

how to create a number sequence in excel with text using TEXT and ROW function

  • Next, use the Fill Handle to AutoFill the lower cells.

Thus you can create a number sequence in excel with text using TEXT and ROW functions.

Read More: How to Create a Number Sequence in Excel Without Dragging


2. Applying Ampersand and TEXT Function to Create a Text Number Sequence

Another way to create a number sequence with text is to use the Ampersand symbol (&) and the TEXT function. Let’s go through the instructions below for a better understanding.

Steps:

  • First, insert some serial numbers in column C and type the following formula in cell D5.

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

how to create a number sequence in excel with text using Ampersand and TEXT function

Here, 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.

  • After that, hit the ENTER button and you will see the first User ID in D5.

  • Next, drag the Fill icon downwards to AutoFill the lower cells.

how to create a number sequence in excel with text

Following the above procedures, you can create a number sequence in excel with text using the Ampersand symbol and the TEXT function.

Read More: Auto Generate Serial Number in Excel VBA (4 Ways)


3. Create a Number Sequence Using Excel CONCAT or CONCATENATE Function

Another efficient method to create a number sequence with text will be to use the CONCAT or CONCATENATE function. These two functions are generally used to attach strings or characters. So we can easily use them to make our usernames. Let’s go through the procedure below.

Steps:

  • First, type the following formula in cell D5.

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

Let’s explain the formula a little bit.

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
  • After that, press the ENTER button and you will see the first User ID in D5.

how to create a number sequence in excel with text using CONCAT or CONCATENATE function

  • Next, use the Fill Handle to AutoFill the lower cells.

Thus you can create a number sequence in excel with text using the CONCAT function.

Similarly, 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)

how to create a number sequence in excel with text

Read More: How to Create a Number Sequence in Excel Based on Criteria


4. Applying ROWS Function to Create a Number Sequence with Text

Although we used the ROWS function in the first method, the ROWS function can also be useful to create a number sequence with text. Let’s go through the description below.

Steps:

  • First, type 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 actually 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
  • After that, press the ENTER button and you will see the first User ID in D5.

how to create a number sequence in excel with text

  • Next, drag the Fill icon to 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.

Read More: Auto Numbering in Excel After Row Insert (5 Suitable Examples)


5. Implementing Flash Fill Command

If you are not a formula guy, you can use the Flash Fill command to create a number sequence. Let’s go through the process below.

Steps:

  • First, write the following texts like the following image.

how to create a number sequence in excel with text using flash fill command

  • Notice that there is a column named Number Sequence. We formatted the numbers for this column. To do that, we selected C5:C10 and then go to Number Format >> More Number Formats…

  • After that, select Number >> Custom and type -0000- in the type section as our username contains -0000- number format within it.
  • Later, click OK.

how to create a number sequence in excel with text

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

  • Thereafter, manually type the first User ID.

how to create a number sequence in excel with text

  • Next, 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.

You will see all the User ID automatically filled with the blank cells.

how to create a number sequence in excel with text

By following this approach, you can create a number sequence in excel with text using the Flash Fill command.

Read More: How to Increment Row Number in Excel Formula (6 Handy Ways)


Practice Section

Here, I’m giving you the dataset of this article so that you can practice these methods on your own. You may try to put the number sequence at the first or second part of the usernames applying these methods.


Conclusion

Suffice to say, we can pull the bottom line considering the fact that you will learn some very useful and effective methods on how to create a number sequence in Excel with text. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website ExcelDemy.


Related Articles 

Meraz Al Nahian

Meraz Al Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo