How to Add Text to the Beginning or End of All Cells in Excel

In order to make sure that the data in your Excel file is organized in a way that makes sense, you will want to add some text to the beginning or end of all cells. This is not just for aesthetic purposes—it's also important because it will help you keep track of what the data means. In some cases, you may need to add text to the beginning of all cells in Excel. For example, if you have a list of addresses and you want to include each address with its corresponding city name, then adding Address or City to the beginning of all cells will be useful. Information provided in this article are compatible with Office versions 2010/2016/MAC/online.

Method 1：The CONCAT and CONCATENATE Function

CONCAT and CONCATENATE function are very helpful if you wish to add a certain title in the beginning or end of a list. Here, I will show you an example of adding “Dr.” to the beginning of a list of names.

Steps:

1. Type “=con” in the target cell and choose if you want to use the CONCAT or the CONCATENATE function. Double-click on the chosen function.

2. Type the argument as the text you want to add in inverted commas (“”) and choose the cell you wish to add after it.

3. Press enter.

4. It's time to duplicate this formula in the remaining column's cells. Just click twice on the fill handle or hold and drag it down (located at the bottom right of cell the here B2).

5. You can see that it adds the prefix you want to add to all the cells, as far as you drag down.

6. Alternatively, Ctrl+C (copy) and Ctrl+V (paste) on keyboard can be used for shorter lists, that is copying and pasting the formula onto other cells.

Method 2：Ampersand Operator (&)

Using the ampersand operator to add text to the beginning or end of all cells in Excel provides a convenient and efficient way to modify and enhance your data.

Steps:

1.The & operator can also be used to add text in the beginning or end of many cells. Let’s discuss an example where you need to add the percentage sybol (%) after a lot of numbers.

2. Just type in “=” and the formula as shown.

3. The result would look like this when you press enter.

4. If you want a space between the number and the symbol, you can go about two following ways:

5. Note that the space is added before the symbol.

6. To duplicate this formula in the remaining column's cells, just click twice on the fill handle at the bottom-right corner of each cell or hold and drag it down. Or use Ctrl+C (copy) and Ctrl+V (paste) on keyboard for shorter lists.

Method 3：The Flash Fill Option

The Flash Fill option in Excel allows users to quickly add text to the beginning or end of all cells in a column. The Flash Fill option in Excel provides users with a fast, accurate, and efficient way to add text to multiple cells, enhancing productivity and data manipulation capabilities.

Steps:

1.If you wish to fill many cells with the same prefix, the Flash fill option can be very useful.

2. Under the ‘Data’ option in the main menu, a ‘Fill’ drop-down menu is availabele that has the ‘Flash Fill’ option.

3. Click on the text you want to fill onto the other cells and click on the Flash Fill option. The data will be copied onto the other cells related to the data. A shortcut of Flash Fill is Ctrl+E on keyboard.

How To Add Text Before/After Specific Characters?

The same as add text to the beginning or end of all cells in Excel. You can also add a particular text before or after a specific character in a cell in Excel. To add text before or after a certain character, first, you must find the position of a specific character by using the SEARCH function as: SEARCH (“char‘, cell) Using this function, you can find the exact position of the character, and then you can add the desired text before or after that particular character.

• How To Add Text After A Specific Character?

You can use the following function to add text after a specific character.

Steps: 

1. LEFT(cell, SEARCH("char", cell)) & "text" & RIGHT(cell, LEN(cell) - SEARCH("char", cell))

2. Or you can also use the CONCATENATE function.

3. CONCATENATE(LEFT(cell, SEARCH("char", cell)), "text", RIGHT(cell, LEN(cell) - SEARCH("char", cell)))

Let's take an example. Suppose we have the following data in Excel.

We want to add the department name after the word ID and before the ID number. For example, ID-HR-001. We can achieve this result in the following way.

1. Open the Excel sheet and click on the desired cell you wish to add text after a specific character.

2. Write the function as

=LEFT (B10, SEARCH ("-", B10)) & "HR-" & RIGHT (B10, LEN(B10) - SEARCH("-", B10))

3. inserting text after a specific character

4. You can also use the CONCATENATE function in the same way.

=CONCATENATE (B10, SEARCH ("-", B10)) & "HR-" & RIGHT (B10, LEN(B10) - SEARCH("-", B10))

• How To Add Text Before A Specific Character?

Suppose you have the following data and want to add the department name before the employee ID, e.g., HR-ID-001. You can achieve this by using the following method. 

1. Open the Excel sheet and click on the desired cell to which you wish to add text. Write the function as

   =LEFT (B10, SEARCH ("ID", B10) - 1) & "HR-" & RIGHT (B10, LEN (B10) - SEARCH ("ID", B10) + 1)

   
   

2. Adding text before a specific character.

3. You can also use the CONCATENATE function in the same way.

4. Remember that if the original cell contains multiple occurrences of the specific character, the text will be inserted before or after the first occurrence.

5. The SEARCH function is case insensitive. You can add text before or after a lower or upper-case letter using the FIND function. This is easier that adding text to the beginning or end of all cells in Excel

Although Microsoft Office and WPS Office can operate these functions, but I still recommend the use of WPS Office, because it is a free office software, with all the features of Microsoft Office, the interface is more simple, more suitable for beginners operating habits, but also supports the opening of all the Microsoft Office files. Quickly click the download button below, together into the WPS Office journey it!

FAQs About Adding Text To A Cell In Excel

Q1: How do I add text to the beginning and end of a cell in Excel?

You can use different functions of Excel to add text at the beginning or end of a cell in Excel. These functions include CONCAT, CONCATENATE, Ampersand operator, and the Flash Fill method.

Q2: How do I add text before all cells in Excel?

You can use the Ampersand operator to add text before all cells in Excel.

Q3: How do you add numbers to the beginning or end of all cells in Excel?

You can use the CONCAT, CONCATENATE, Ampersand operator, and Flash Fill method to add numbers to the beginning of all cells in Excel.