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)…