The article will show you how to add text in the middle of a cell in Excel. If you work in a digital shop, you know that every product has its own ID. But sometimes you may need to bring some little changes to those IDs for whatever reasons. You can bring changes by putting some letters inside the older IDs. Inserting letters one by one will obviously cost you a lot of time. Excel has some effective and dynamic functions to add text in the middle of a word or string. We will demonstrate the application of those functions here in this article.
How to Add Text in the Middle of a Cell in Excel: 5 Efficient Ways
In the dataset, we have some IDs of different Smartphone Products.
Suppose similar products are shipped in your store and you need to insert other letters inside the IDs to update their status. You will find the solutions in the later sections.
1. Using Excel LEFT and MID Functions to Add Text in the Middle of a Cell
We can use the LEFT and MID functions to add one or multiple texts in the middle of a cell. Say we will insert the letter M after every second number of the Product IDs. Let’s go through the procedure below.
Steps:
- First, create a new column for the new IDs and type the following formula in cell D5.
=LEFT(C5,2) & "M" & MID(C5,3,6)
The formula here divides the text in C5 into two parts. The LEFT function returns the first two characters of the ID of C5 and the MID function returns 6 characters from the 3rd one of the ID as our IDs have 8 characters each. The Ampersands add the letter M between these two parts.
- After that, press the ENTER button and you will see the letter M after the 2nd number of the ID.
- Thereafter, use the Fill Handle to AutoFill the lower cells.
Thus you can easily add text in the middle of a cell in Excel using LEFT and MID functions.
Read More: How to Add Text to End of Cell in Excel
2. Applying REPLACE Function to Add Text in the Middle of a Cell
We can also use the REPLACE function to add text in the middle of a cell. Here we will also insert the letter M after two characters of each ID. Let’s go through the process below.
Steps:
- First, create a new column for the new IDs and type the following formula in cell D5.
=REPLACE(C5,3,0,"M")
The formula here will add an M after the 2nd character of the text in C5.
- After that, press the ENTER button, press ENTER, and drag the Fill icon downwards to AutoFill the lower cells.
Thus you can easily add text in the middle of a cell in Excel.
3. Implementing Combined Functions to Add Text in the Middle of a Cell
If you don’t want to use the Ampersand, you can effectively use the combination of the CONCATENATE, LEFT, RIGHT, and LEN functions to add text in the middle of a cell. Let’s go through the description below.
Steps:
- First, create a new column for the new IDs and type the following formula in cell D5.
=CONCATENATE(LEFT(C5,2),"M",RIGHT(C5,LEN(C5)-2))
The formula here will add an M after the 2nd character of the text in C5.
Formula Breakdown
Here we break down our formula to show how this works.
- LEN(C5)-2—-> returns the subtraction result between the character length of C5 and 2.
- Output: 6
- RIGHT(C5,LEN(C5)-2)—-> becomes
- RIGHT(C5,6)—-> turns into
- Output: “ABD344”
- LEFT(C5,2)—-> returns the first two characters of text in C5.
- Output: “23”
- CONCATENATE(LEFT(C5,2),”M”,RIGHT(C5,LEN(C5)-2))—-> becomes
- CONCATENATE( “23”,”M”, “ABD344”)—-> results into
- Output: “23MABD344”
- After that, press the ENTER button, press ENTER, and drag the Fill icon downwards to AutoFill the lower cells.
Thus you can easily add text in the middle of a cell in Excel.
Read More: How to Add Text in Excel Spreadsheet
4. Add Text in the Middle of a Cell Using VBA
We can also insert or add a text in the middle of a cell using VBA. Let’s go through the process below.
Steps:
- First, go to the Developer Tab and then select Visual Basic.
- After that, the VBA editor will appear. Select Insert >> Module to open a VBA Module.
- Now, type the following code in the VBA Module.
Sub AddTextInCell()
Dim mnCell As Range, nSelected_Range As Range
Set nSelected_Range = Application.Selection
Set nSelected_Range = Application.InputBox _
("Select the Range", _
"Add Text", nSelected_Range.Address, Type:=8)
For Each mnCell In nSelected_Range
mnCell.Value = VBA.Left(mnCell.Value, 2) & "M" & _
VBA.Mid(mnCell.Value, 3, VBA.Len(mnCell.Value) - 1)
Next
End Sub
This VBA code will add the letter M in the Product IDs like we did in Method 1 and 2. Here we used VBA Left, Mid and Len functions. Also, a For Loop was used. The coding mechanism is similar to the formula that we used in Method 1.
- After that, go back to your sheet and run the Macro named AddTextInCell.
- Thereafter a message box will appear. Select the range of IDs that you want to update and click OK.
There you go. You will see the updated Product IDs with an M after the 2nd character of each ID.
Thus you can add one or multiple texts using VBA built-in functions.
Read More: How to Add Text to Cell Value in Excel
5. Applying SUBSTITUTE Function to Add Text Based on a Criteria
If you want to add text in the middle of a cell based on criteria, you can use the SUBSTITUTE function for this purpose. Let’s go through the procedure below for a better understanding.
Steps:
Suppose, we want to add an X after every A in the Product IDs. To do that,
- First, create a new column for the new IDs and type the following formula in cell D5.
=SUBSTITUTE(C5,"A","AX")
The formula here will add an X whenever it finds an A in these IDs.
- After that, press the ENTER button, press ENTER, and drag the Fill icon downwards to AutoFill the lower cells.
Thus you can easily add text in the middle of a cell in Excel using the SUBSTITUTE function based on criteria.
Read More: How to Add Text to Beginning of Cell in Excel
Practice Section
Here, I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
In the end, we can surmise that you will learn the best possible ways to add text in the middle of a cell in Excel after going through this article. If you have any better methods 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.
