In this post we will see how we could add/prefix a string to each value in a column in excel and how to append/suffix a string to each value in excel.
There are times when we are required to add some text before a value in a column in excel, for example in the data below we have employee id in column A and we have added/prefixed a string “A_” at the starting of each employee id for some requirement in column B also you could see that in the column C we have suffixed the employee department id for each of the value in the column A.
It’s a very simple operation to concatenate text string with any data value with the help of concatenation operator & in excel.
Step 1
Enter the formula =”A_”&A2 in cell B1, as you could see the string which we want to add to a data value should be within the quotes as A_ is in our formula.
Similarly to add suffix to a data value in a column insert the formula =A2&”_Dept_1″ in cell C2 as shown below.
You could see this time we have added the string at the last and have joined them with the help of concatenation operator & in excel.
Step 2
Now drag the formulae to the last to cover the entire data in column A
As you could see we have added a string in the beginning and at the end of a value in excel.
You could use the same method to add any of the string that you want within Quotes.
Hope this helped.
