How to add text to beginning or end of all cells in Excel
A free Office suite fully compatible with Microsoft Office
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 versions 2010/2016/MAC/online.
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.
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.
Adding Text Using Ampersand Operator (&)
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.
The Flash Fill Option
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.
Did you learn about How To Add Text To Beginning Or End Of All Cells In Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.
You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences