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:

1. Excel formula used to add text to the cell.
2. Text addition at the beginning of every cell in the excel sheet.
3. Appending the particular text to the end of the cell in the excel sheet.
4. Inserting the reader into both sides of the string.
5. Combining the text from two or more individual cells.
6. Adding a unique character to the cell.
7. Insertion of the space between the texts.

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:

• First, we will select the respective cell where we usually want to get the output or the result and then type the equals sign (=).
• And next, we will move on type the desired text just inside the quotation marks.
• And soon after that, we will type an ampersand symbol (& ).
• After that, we will select the cell to which the text shall be added and then hit the Enter button to fetch the output.

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 example, we can use the formula mentioned below to prepend the text "Project:" to a project name in cell A2.

For all Excel versions:

="Project:"& A2

=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:

Without spaces:

=$E$2& A2

=CONCATENATE ($E$2, A2)

With spaces:

=$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:

=A2& "-US"

=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:

=A2& $D$2

=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.

• For example, let us assume that we want to add the string "Project:" to the beginning and "-US" to the end of the existing text in cell A2.

="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

1. Ampersand symbol.
2. CONCATENATE or CONCAT function.

• For example, if we wish to combine values from the columns A and the column B then with the help of the comma as well as with a space (", ") for the delimiter, we will enter one of the below formulas in cell B2 and then will drag down the column.

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.

• For example, to add the trademark symbol (?) to the text in the cell A2, then we can follow any of the below-mentioned formulas:

=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 (" ").

• For example, If we want to insert a space after the 10^th character in cells A2:A7, then we will be entering out the below formula in cell B2 and drag it through cell B7:

=LEFT (A2, 10) & " "& RIGHT (A2, LEN (A2) -10)

Or

=CONCATENATE (LEFT (A2, 10), " ", RIGHT (A2, LEN (A2) -10))

In all the respective original cells, the 10^th 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))

Or

=CONCATENATE (LEFT (A2, SEARCH (":", A2)), " ", RIGHT (A2, LEN (A2)-SEARCH (":", A2)))