How to Add Text to Cell in Excel (6 Easy Methods)

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.

excel add text to cell


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.

Use Ampersand Symbol to Add Text to Cell in Excel

  • After that, AutoFill the formula to the rest of the cells in Column C.


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.

Apply Flash Fill Feature to Add Text to Cell in Excel

  • Now select that (C5) or those cells and press CTRLE on your keyboard. As a result, you will be able to add text to cells.

Apply Flash Fill Feature to Add Text to Cell in Excel


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.

Insert CONCATENATE Function to Add Text to Cell in Excel

  • Further, AutoFill the CONCATENATE function to the rest of the cells in column C which has been given in the screenshot.


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.

Combine LEFT, RIGHT, and LEN Functions to Add Text to Cell

  • Further, AutoFill the functions to the rest of the cells in column C which has been given in the screenshot.


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.

Apply TEXTJOIN Function to Add Text to Cell

  • Further, AutoFill the CONCATENATE function to the rest of the cells in column C which has been given in the screenshot.

Apply VBA Code to Add Text to Cell


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

Apply VBA Code to Add Text to Cell

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

Apply VBA Code to Add Text to Cell

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

Apply VBA Code to Add Text to Cell


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

Kawser

Kawser

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo