How to Add Text to Beginning of Cell in Excel (7 Quick Tricks)

In this tutorial, I am going to share with you 7 quick tricks to add text to the beginning of a cell in excel. You may need to append extra text in a data cell to make it more clear. You may also want to add an extra character into a cell to mark it differently. Obviously, anybody can do this manually. But with the powerful features that are available in excel, we can do this in a very short time.


Download Practice Workbook

You can download the practice workbook from here.


7 Quick Tricks to Add Text to the Beginning of Cell in Excel

1. Use Ampersand(&) Operator to Add Text to Beginning of Cell in Excel

The ampersand operator mainly joins multiple text strings into one string. We will use this operator to add the text “Passed-” to the beginning of each mark data cell below. Let us see how we can do that.

Use Ampersand(&) Operator to Add Text to Beginning of Cell in Excel

Steps:

  • First, double-click on cell C5 and type in the following formula:
="Passed-"&B5

Use Ampersand(&) Operator to Add Text to Beginning of Cell in Excel

  • Now, press Enter and the text will be added before the marks.

  • Finally, copy the formula of cell C5 to the cells below using the Fill Handle.

Read More: How to Add Text in Excel Spreadsheet (6 Easy Ways)


2. Using Excel CONCAT Function

The CONCAT function in excel works almost the same way as the ampersand to add text to the beginning of a cell in excel. But the only difference is in the way it is used. Follow the steps below to see this function in action.

Steps:

  • First of all, double-click on cell C5 and enter the following formula:
=CONCAT("Passed-",B5)

Add Text to Beginning of Cell Using Excel CONCAT Function

  • Then, press Enter.
  • Immediately, the desired text will add to cell C5.
  • After that, simply copy the formula of cell C5 to the cells below.
  • As a result, all the cells will now have the text added to the beginning.

Read More: How to Add Text in the Middle of a Cell in Excel (5 Easy Methods)


3. Apply Excel Flash Fill Feature

The Flash Fill feature is available in Excel 2013 and the later versions. This feature actually works like a magic wand to add text to the beginning of a cell in excel. Also, it gives us the desired result in the blink of an eye. Let us see how to use it.

Steps:

  • To begin with, double-click on cell C5 and manually type the desired text before the existing data.
  • Then, press Enter.

Apply Excel Flash Fill Feature

  • Now, go to the Data tab on top of your screen.
  • Next, under the Data Tools section select Flash Fill.

Apply Excel Flash Fill Feature

  • Consequently, Excel will recognize the desired text in cell C5 and copy it to all the cells below.

Read More: How to Add Text to End of Cell in Excel (6 Easy Methods)


4. Using REPLACE Function

The REPLACE function in excel substitutes characters in a text string according to the position. We will use this unique property of this function to add a piece of text to the beginning of the original data cell in excel. Let us jump right into it.

Steps:

  • First, double-click on cell C5 and type in the following formula:
=REPLACE(B5,1,0,"Passed-")

Using REPLACE Function

  • Second, press Enter.
  • After that, excel will insert the desired text before the marks.

Using REPLACE Function

  • At last, copy the REPLACE function to the cells below by dragging the Fill Handle.

  • So, the desired text will add to all the cells below and you can easily check that by clicking on any of them.

Read More: How to Add Text to Cell Without Deleting in Excel (8 Easy Methods)


5. Apply TEXTJOIN Function

The TEXTJOIN function gives us the ability to join multiple strings of text with a delimiter between them. With this feature, we can quickly add text to the beginning of a cell. Below are the steps to use this function.

Steps:

  • As in the previous methods, start by double clicking on cell C5 and add the following formula:
=TEXTJOIN(": ",TRUE,"Passed",B5)

Apply TEXTJOIN Function

  • Now, press Enter.
  • As a result, the extra text will add to cell C5.
  • Afterward, just copy this formula to all the cells in column C.

Read More: How to Add a Word in All Rows in Excel (4 Smart Methods)


6. Utilizing Format Cells Feature to Add up to 2 Letters Text to Beginning of Cell

This method is actually quite different from the rest of the methods. We will use excel custom cell formatting to add text at the beginning of the original data. Note that, this method has a limitation in that it allows a text string with up to 2 letters. With that said, it is still a much quicker method to use as we shall see below.

Steps:

  • In the first place, copy the value of cell B5 to cell C5.

Utilizing Format Cells Feature to Add up to 2 Letters Text to Beginning of Cell

  • Next, while you are on cell C5, go to the Home tab and navigate to the Number section.
  • Then, select the More Number Formats option from the drop-down.

Utilizing Format Cells Feature to Add up to 2 Letters Text to Beginning of Cell

  • Now, a new window Format Cells There, go to the option Custom.
  • Then, in the box below Type, insert the following text:
\OK#
  • After that press OK.

Utilizing Format Cells Feature to Add up to 2 Letters Text to Beginning of Cell

  • Consequently, Excel will add the extra text before the data in cell C5.

  • Similarly, repeat the above methods for the other data cells.

Read More: How to Combine Text and Number in Excel (4 Suitable Ways)


7. Use Excel VBA to Prepend Text to the Beginning of Cell

The use of Excel VBA is very effective when we have to add some text to the beginning of a cell repetitively. Also, you can easily select the cells that you want to add text to and run the macro only once. This saves a lot of time. Let us how we can write some VBA code for this purpose.

Steps:

  • To begin with, go to the Developer tab and select Visual Basic.

Use Excel VBA to Prepend Text to the Beginning of Cell

  • Then, in the new Visual Basic window, go to Insert and click Module.

Use Excel VBA to Prepend Text to the Beginning of Cell

  • Next, in the Module1 window type the following VBA code:
Sub AddTextBefore()
Dim cls As Range
For Each cls In Application.Selection
If cls.Value <> "" Then cls.Offset(0, 1).Value = "Passed-" & cls.Value
Next
End Sub

Use Excel VBA to Prepend Text to the Beginning of Cell

  • After that, close the VBA window and while you are on cell B5, go to the View tab.
  • Now, from the Macros drop-down select View Macros.

Use Excel VBA to Prepend Text to the Beginning of Cell

  • Here, you will see the macro that we just created. Click on Run.

Use Excel VBA to Prepend Text to the Beginning of Cell

  • Consequently, the VBA code will run and add the desired text before the data.

  • In the same way, select the cells from B6 to B10 and Run the previous macro.

  • Finally, the macro will insert the additional text into all the cells.

Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)


Conclusion

I really hope that you were able to grasp the methods I showed above and applied them easily. Note that the method that you will use to add text to the beginning of the cell in excel depends on your situation. For large datasets, VBA might be the best option. Whereas in other cases, the functions might be more helpful. Also, practice more and more with various sizes of datasets. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.


Related Articles

Nazmul Hossain Shovon
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo