Kawser
Sometimes you may need to add text to a cell, one solution is to use a new column and use formulas. To gain a clear grasp of this topic, you have to know about some Excel functions. This article will provide you with six quick methods to add text to cell in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
6 Suitable Ways to Add Text to Cell in Excel
Let’s get introduced to our dataset first. I have some arbitrary names of songs on my sheet and I’ll add text to a new cell using the CONCATENATE, LEFT, RIGHT, LEN, TEXTJOIN Functions, Flash Fill feature, and VBA also.
1. Use Ampersand Symbol to Add Text to Beginning of Cell in Excel
We can use an ampersand (&) operator to merge text strings effortlessly in Excel. Let’s see how we use it to add text in cells.
📌 Steps:
- First of all, select cell C5.
- Secondly, put down the formula below.
="ID: "&B5- Next, hit the ENTER key.
- After that, AutoFill the formula to the rest of the cells in Column C.
Read More: How to Find and Replace in Excel Column (6 Ways)
2. Apply Flash Fill Feature to Add Text to Every Cell in Excel
You can also do the above job just using the new feature Flash Fill in Excel 2013 or later version. You have to know firstly how to use Flash Fill. Say we will insert the text .mp3 at the ending of every text in cells in the B5:B14 range. Let’s go through the procedure below.
📌 Steps:
- At first, select cell C5.
- Then, write down “The Monster.mp3” manually in the cell.
- Now select that (C5) or those cells and press CTRL + E on your keyboard. As a result, you will be able to add text to cells.
Read More: Find and Replace a Text in a Range with Excel VBA (Macro and UserForm)
3. Insert CONCATENATE Function to Add Text to Multiple Cells in Excel
You can apply the CONCATENATE function to add text to cells in Excel. Let’s follow the instructions below to add text to cells!
📌 Steps:
- First of all, select cell C5 for the convenience of our work.
- After that, write down the below function in that cell.
=CONCATENATE("Song: ",B5)- Hence, simply, press Enter on your keyboard. As a result, you will be able to get the return of the CONCATENATE function and the return is Song: The Monster.
- Further, AutoFill the CONCATENATE function to the rest of the cells in column C which has been given in the screenshot.
Read More: Excel VBA to Find and Replace Text in a Column (2 Examples)
4. Combine LEFT, RIGHT, and LEN Functions to Add Text to Cell
In this section, we will add text to cells using the LEFT, RIGHT, and LEN functions. This is an easy and time-saving task. Let’s follow the instructions below to add text to cells!
📌 Steps:
- The following formula inserts a hyphen after the third character in a cell:
=LEFT(B5,3)&"-"&RIGHT(B5,LEN(B5)-3)- Hence, simply, press Enter on your keyboard. As a result, you will be able to get the return of the LEFT, RIGHT, and LEN functions and the return is The- Monster.
- Further, AutoFill the functions to the rest of the cells in column C which has been given in the screenshot.
Read More: How to Substitute Multiple Characters in Excel (6 Ways)
Similar Readings
- How to Find and Replace Asterisk (*) Character in Excel
- Find and Replace Carriage Return in Excel (with Quick Steps)
- How to Find and Replace Values in Multiple Excel Files (3 Methods)
- [Fixed!] Excel Find and Replace Not Working (6 Solutions)
- How to Find and Replace Using Formula in Excel (4 Examples)
5. Perform TEXTJOIN Function to Add Text to Every Cell
Also, we can try out the problem with the TEXTJOIN function. Let’s go through the procedure below to be able to do that.
📌 Steps:
- Firstly, select cell C5.
- After that, paste the formula below.
=TEXTJOIN(": ",TRUE,"Song",B5)- Lastly, press ENTER.
- Further, AutoFill the CONCATENATE function to the rest of the cells in column C which has been given in the screenshot.
Read More: How to Add Text Before a Formula in Excel (5 Easy Ways)
6. Apply VBA Code to Add Text to Multiple Cells
Last but not least, I’ll show how to add text to a cell in Excel using a simple VBA code. It’s very helpful for some particular moments. From our dataset, we will add text to cells in Excel using a simple VBA code. Let’s follow the instructions below to learn!
Step 1:
- First of all, open a Module, to do that, firstly, from your Developer tab, go to,
Developer → Visual Basic
- After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications – Add Text to Cell will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,
Insert → Module
- Hence, the Add Text to Cell module pops up. In the Add Text to Cell module, write down the below VBA
Sub add_text_to_cell()
Dim x As Range
Dim y As Range
Set x = Application.Selection
For Each y In x
y.Offset(0, 1).Value = "Song: " & y.Value
Next y
End Sub
- Besides, save the file as an Excel Macro-enabled workbook.
Step 2:
- Now, return to the worksheet VBA.
- After that, select cells in the B5:B14 range.
- Then, go to the Developer ribbon.
- Later, select Macros on the Code group.
- Thus, it will open up the Macro dialog box.
- Here, we can see the macro add_text selected, which we’ve created just now.
- Then, click on the Run button.
- Hence, we can see the blank cells filled automatically with our desired results.
Read More: How to Find and Replace within Selection in Excel (7 Methods)
Things to Remember
➜ While a value can not found in the referenced cell, the #N/A! error happens in Excel.
➜ The #VALUE! error occurs when any of the given inputs are non-numeric.
Conclusion
I hope all of the suitable methods mentioned above to add text to cells will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
Further Readings
- How to Find and Replace Multiple Words at Once in Excel (7 Methods)
- Add Text and Formula in the Same Cell in Excel (4 Examples)
- How to Find and Replace Multiple Values in Excel with VBA (3 Examples)
- Excel VBA: How to Find and Replace Text in Word Document
- How to Find and Replace from List with Macro in Excel (5 Examples)
