Add text and number into specified position of cell in Excel

There may be times when you need to insert the identical text into all of the cells that are contained inside a column. It's possible that a specific title needs to go before the names in a list, or that a specific symbol needs to go at the end of the text in each cell. Both of these things need to be done. The task of adding text or numbers to cells in Excel is one of the most often performed tasks. Including things like inserting spaces between names, including prefixes and suffixes in cells, and inserting dashes in social security numbers.

Excel provides some extremely simple ways that you can add text to the beginning or end of the text that is present in a range of cells. These can be done independently of one another.

In this tutorial, you are going to learn about some methods to add text into specified position of cell.

Using ampersand (&) Operator

A useful feature of the operator represented by the ampersand (&) is to combine many text strings into a single one.

Let’s see one example and understand step by step how we can add text in a specific position in a cell.

Step 1

In our example, we have name of some lecturer who are promoted to get a position of professor. We have to add Professor before their name.

Step 2

Select one blank cell and write the following formula to add text before the name. In our example we have selected the cell B2 and written the formula to add Professor before the name of the lecturers there.

="text "&cell 

In our example, we have written the following formula

="Professor "&A2 

After pressing Enter, we will see the text Professor has been added before the names.

Step 3

To get the result in all the remaining cells, click on the ‘+’ sign appears on the lower right corner of the cell B2, which activates the autofill function and then drag down.

Using CONCATENATE Function

Excel's CONCATENATE function is a very useful one since it enables users to insert text both at the beginning and the end of a string of text. In terms of the functionality that it provides, the CONCATENATE() function is comparable to the ampersand (&) operator. The only distinction between the two is in the manner in which they are utilized. This function is applicable to us at the beginning to the end of the text.

Add text at the beginning of cell

Step 1

In our example, we have the names of some professors who used to be lecturers. We need to put "Professor" in front of their name. See the following image.

Step 2

We want to add text at the beginning of the names. To add at the beginning of the names, write the following formula at the formula bar.

=CONCATENATE("TEXT",cell) 

In our example, we want to add the text “Professor” at the beginning

=CONCATENATE("Professor ",A2) 

After pressing enter, we will see the text Professor has been added before the names. See the following image.

Step 3

To get the result in all the other cells, click the "+" sign in the lower right corner of cell B2. This turns on the autofill feature, and you can then drag down. See the following image.

Add text at the end of cell

Let’s suppose we want to add the department id at the end of the names of the professors given in above example.

Step 1

Write the following formula to add the text at the end of the cell.

= CONCATENATE(Cell,"Text")

In our example we are writing the following formula and press enter.

=CONCATENATE(B2,"(1201025)") 

See the following image.

Step 2

Choose cell C2, drag the Fill Handle down to the cell you want to fill with this formula. See the below picture.

Add Text in Middle of Cell

Step 1

In our example, we have some Roll Numbers in our Excel sheet and we want to add SOC in the middle of the roll no. See the following image.

Step 2 

To add SOC in the middle of the Roll No., write the following formula and press enter.

=LEFT(A2,2) & "SOC" & MID(A2,3,100) 

See the following image.

Step 3

Choose cell B2, drag the Fill Handle down to the cell you want to fill with this formula.

Conclusion

In this tutorial, you learnt how to add text and number into a specified position of cell in Excel.