Meraz Al Nahian
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.
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.
- 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
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.
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.
- 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)
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.
- 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.
- 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.
See that 0 becomes -0000-, similarly 1 will be seen as -0001- and so on.
- Thereafter, manually type the first User ID.
- 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.
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.
