How to add text or character to every cell in Microsoft Excel
It was well known that there can be various instances where we need to add the same type of text to all the individual cells in a column. And to achieve this, we need to add a particular title just before the names in a list, or instead of this, we can also add some specific type of symbol at the endof the text in every cell present in the particular excel sheet.
And the most important as well as the good thing about these (to add text or character to every cell in Excel) is that we are not required to manually do these (to add text or symbol to every cell in Excel) things.
In this tutorial, we will cover the various effective ways or method that can be used to perform the adding of text or character to every cell in Microsoft Excel, which are as follow:
Understanding each one of the above ways or the methods in detail with the help of the various example,
1) Excel formula used to add text to the cell.
Now to add out a specific character or specific text in a Microsoft Excel cell, we can efficiently concatenate a string and a cell reference by just making use of one of the following methods, which are discussed below:
a) Concatenation operator
The concatenation operator in Excel is the easiest and the finest way to add a text string to a particular cell using an ampersand character (& ), which is the concatenation operator in Microsoft Excel.
b) CONCATENATE function
And with the help of the concatenate same function output can be yielded.
C) CONCAT function
And for adding text to the cells in Excel 2019, Excel 365, and Excel Online, we can use the CONCAT function, primarily considered the modern replacement of CONCATENATE.
2) How can we add text to the beginning of cells in the excel sheet
To add the particular text or character to the front of a cell in the excel sheet, we need to perform the followings things which are as follows:
Moreover, on an alternate basis, we can also supply our particular text string and the cell reference as input parameters to the CONCATENATE or CONCAT function.
For all Excel versions:
=CONCATENATE ("Project:", A2)
And then, we will enter the formula in cell B2, drag it down to the column, and have the exact text efficiently inserted in all cells.
And per our convenience, we can efficiently input the target text in an individual cell (E2) and then after that will add two text cells both together:
=CONCATENATE ($E$2, A2)
=$E$2& " "& A2
=CONCATENATE ($E$2, " ", A2)
And the address of the particular cell that contains the prepended text should be locked with the $ sign so that it will not shift when copying the formula down.
And with the approach mentioned earlier, we can change the added text in one place without updating every formula.
3) How can we add text to the end of cells in an Excel sheet
We will now see how to append text or a specific character to an existing enclosure, and we will use the concatenation method again. And for the particular instance, to add out the string "-US<" to the end of cell A2, below -mentioned formula, we can effectively use the:
=CONCATENATE (A2, "-US")
=CONCAT (A2, "-US")
And alternatively, we can also enter the text in some of the cells and then will join two cells with text altogether:
=CONCATENATE (A2, $D$2)
And we should remember to use an absolute reference to append the text ($D$2) for the formula to copy correctly across the column respectively.
4) Add characters to the beginning as well as the end of a string.
As per the above-discussed method, we have already known how to prepend as well as to append text to an existing cell, and then after that there would be nothing that could prevent us from using both techniques within the single formula.
="Project:"& A2& "-US"
=CONCATENATE ("Project:", A2, "-US")
=CONCAT ("Project:", A2, "-US")
And with the strings input in separate cells, this will work very efficiently.
5) Combine text from two or more cells
And now, in this method, if we want to place values from the various multiple cells into one cell, then we will be concatenating the original cells with the help of the already used familiar techniques that are none other than the
Add text from two cells with the help of the ampersand:
=A2& ", "& B2
Combining text from two cells with CONCAT or CONCATENATE:
=CONCATENATE (A2, ", ", B2)
=CONCAT (A2, ", ", B2)
When adding text from two columns, we should use the relative cell reference (like A2), so they will adjust correctly for each row where the formula is copied.
6) How to add special characters to cells in Excel
And if we want to insert a unique character in an Excel cell, then we need to know its code in the ASCII system. And once the code is established, we will supply it to the CHAR function to return a corresponding character.
The CHAR function is the function that would accepts any number from 1 to 255. A list of printable character codes (values from 32 to 255) can be found.
And now, to add the unique character to a current value or a formula result, we can apply any concatenation method that we like best.
=A2& CHAR (153)
=CONCATENATE (A2& CHAR (153))
=CONCAT (A2& CHAR (153))
7) How to add space between text in Excel cell
Adding the space between the text in the Excel cell is considered the specific case. And to add space at the same position in all cells, we will use the formula to insert text just after the nth character, where text is the space character (" ").
=LEFT (A2, 10) & " "& RIGHT (A2, LEN (A2) -10)
=CONCATENATE (LEFT (A2, 10), " ", RIGHT (A2, LEN (A2) -10))
In all the respective original cells, the 10th character is a colon (:), so space is primarily inserted precisely where we need it:
And to insert space at a different position in each cell, we will adjust the formula that will add out the text just after or before the specific character.
From the below table, the colon (:) is effectively positioned after the project number, which may usually contain a variable number of characters. And if in case, we want to add a space just after the colon, then we can quickly locate its position with the help of the SEARCH function available in Microsoft Excel.
=LEFT (A2, SEARCH (":", A2)) & " "& RIGHT (A2, LEN (A2)-SEARCH (":", A2))
=CONCATENATE (LEFT (A2, SEARCH (":", A2)), " ", RIGHT (A2, LEN (A2)-SEARCH (":", A2)))