Have you ever faced a situation where you are asked to make last minute changes to an excel sheet like adding a prefix or suffix to the text, but always wondered how to do so? as doing it manually is not an easy solution. If you try to do it manually consider you will have to invest your complete day. Why to do it manually when you can do it automatically. By just using a formula this difficult task will be a child work.
In this article we will explain you how to do it with help of an example.
Note: The text that you need to add as a Suffix or Prefix should be same, as only then you can drag the formula to work for complete column.
How To Add Prefix And Suffix To a Cell
To understand how it works let’s take an example. In the sheet below we have three columns A, B, C. We need to add “https://wethegeek.com” as prefix to the text. So what we will do is we will type in the formula in column b to add prefix and similarly to add suffix will use column C. We will add URL as prefix and name of a person as suffix.
The simplest way to do is by using concatenate operator i.e. & in excel
Step 1: In column B named as Prefix + Text type: =” https://wethegeek.com” & A2. Where the text in quotes is the prefix and A2 is the address of the cell where the text is. Once you type in the formula and press enter the prefix will be added.
Refer to the attached screenshot for further clarification.
In the same manner to add suffix to a data value in column C insert the formula =A2&”Preeti” where the text in quotes is the suffix. Now press enter and see the suffix will be added.
Must Read: How To Recover Any Unsaved Or Overwritten Microsoft Excel Files
Refer to the attached screenshot for further clarification.
Step 2: Once the formula is set select the cell and drag it to complete cell range till where you want to add a prefix or suffix.
For clarification refer to screenshot below:
This will you will be able to get the prefix and suffix added to text in no time.
Alternatively, you can even use Concatenate function also. To do so follow the steps below:
Step 1: Click Formula tab and then click on Insert Function. Now in the Insert Function window type “Concatenate” click on Go and from the select function choose it and click Ok button.
Step 2: This will open the Function Arguments window, here in Text1 column enter the text you want to prefix for ex: “https://wethegeek.com ” and in Text2 column to which you want to enter the prefix here it is “A2”. This way you will be able to create the formula to add prefix to all the text in the cell. See screenshot attached below for more clarification:
To add prefix to number of cells just select the cell with formula and drag it down to the cells till where you need to add the prefix.
Must Read:How To Hide Columns And Rows
Adding Suffix
To add suffix, you need to follow the same steps.
The only difference is in Text1 under Function Argument window you need to enter the column address of the cell to which you need to add the suffix. In my case it is cell A2 and in Text2 add the suffix in my case it is “Preeti”.
For further clarification refer to the screenshot below:
To add suffix to number of cells just select the cell with formula and drag it down to the cells till where you need to add the suffix.
This way you will be able to add suffix and prefix to the text easily in much faster manner.
We hope you find this article helpful. You can use any of the methods described above whichever you find is easy and convenient to use. If you face any problem please let us know, we will try to help. Also, share a feedback on how you liked the article.