Adnan Masruf
Users frequently need to add text in Excel spreadsheet. In this way, they are able to communicate text data. They might need to add a prefix or suffix to a text dataset and also have to combine the texts from different cells. They could do it manually. But that manual process takes a lot of time. That is why we will discuss how to add text in Excel spreadsheet in six very efficient ways.
Download the Practice Workbook
6 Handy Approaches to Add Text in Excel Spreadsheet
In this article, we will discuss 6 effective ways to add text in Excel spreadsheet. In the first method, we will use the Concatenation Operator. Next, we will use the CONCAT function to accomplish the task. We will also use the TEXTJOIN, SEARCH, and REPLACE functions sequentially. Finally, we will resort to VBA code to concatenate text. Here, we have a sample dataset that we will use to illustrate the methods.
1. Using Ampersand Operator to Add Text in Excel
The concatenation operator is nothing but an ampersand character (&). This operator allows the users to add text in spreadsheet various ways. Here, we will discuss them one by one.
1.1 Applying Ampersand Operator to Add Text Without Space
We’ll add two texts in Excel spreadsheet together without any blanks. We can do that thanks to the concatenation operator. Follow the steps below to do it.
Step 1:
- Firstly, select the cell where you want to add the text.
- Write down this formula below
=B5&C5- Here, we will choose the D5 cell.
- Next, type the equal sign(“=”) in that cell.
- Choose the first text or cell containing the text you want to add.
- In this case, we will choose the B5 cell.
- After that, type the concatenation operator (“&”).
- Then, select the next text or cell with the text you want to add after the previous one.
- In our case, we will select the C5 cell.
- Finally, hit Enter.
Step 2:
- Consequently, the concatenated text will appear in the cell.
Step 3:
- Thirdly, hoover down to the bottom right of the cell containing the added text.
- A small plus sign will appear by the cursor.
Step 4:
- Then, move the cursor down along with the plus sign to the last cell of the dataset.
- Consequently, the rest of the cells will be auto-filled with the added text.
1.2 Utilizing Ampersand Operator to Add Text with Space
In this case, we will add two texts in Excel spreadsheet with a blank in between them. Follow the ensuing steps to do the task.
Step 1:
- Firstly, choose the cell to which you’ll be adding the text.
- Write down the formula below
=B5&” “&C5- We’ll pick cell D5 in this instance.
- Then, in that cell, enter the equal sign (“=”).
- To add text, select the first text or cell that contains that text.
- We’ll go with the B5 cell in this instance.
- Type the concatenation operator (“&”) after that.
- Now enter this symbol by typing ” “. This means a space.
- After that, choose the text or cell that follows the previous one in the document.
- Here, we will opt for cell C5.
- Finally, press Enter.
Step 2:
- As a result, there will be a space between the concatenated texts when they appear in the cell.
Step 3:
- Then, navigate to the cell’s bottom right, where the new text was added.
- You will see a small plus sign by the cursor.
Step 4:
- After that, move the plus sign and the cursor down to the dataset’s final cell.
- The added text will automatically fill the remaining cells.
1.3 Using Ampersand Operator to Add Text at the End of a String
In this additional method, we will append a text with another text. To finish the task, adhere to the below steps.
Step 1:
- Firstly, select the cell to which you will add the text first.
- Put the formula in writing.
=C5&”-US”- In this case, we’ll choose cell D5.
- Then, type the equal sign (“=”) in that cell.
- After that, choose the first text or cell that includes that text to add it to the document.
- In this case, we’re going to use the B5 cell.
- After that, enter the “&” sign for the concatenation operator.
- Then, write down the text that you want to add to the end of the previous string.
- In this instance, we will type “-US”.
- Finally, press Enter.
Step 2:
- Consequently, the text will be appended to the end of the string.
Step 3:
- Afterward, go to the cell’s bottom right, where the newly added text is located.
- By the cursor, there will be a tiny plus sign.
Step 4:
- Then, lower the plus sign and the cursor to the last cell of the dataset.
- The additional text will fill in the blank cells on its own.
1.4 Applying Ampersand Operator to Add Text After Nth Character
In this case, we will add a text after the nth character of another text in Excel speadsheet. Follow the outlined steps below to complete the task.
Step 1:
- Firstly, select the cell where you want to add the text.
- In our case, the cell is E5.
- Then, write down this formula in the cell.
=LEFT(D5,2)&"-"&RIGHT(D5,LEN(D5)-2)- Finally, press Enter.
Formula Breakdown
- LEFT(D5,2): The Left function extracts a substring from the left side of a text. It has two arguments, namely: text and num_char. The text argument asks for the text from which the function will extract a substring, and the num_char argument asks for the number of characters the substring will contain. Here, we want to extract the first two characters from the left of our D5 cell text. It will return “MP”.
- RIGHT(D5,LEN(D5)-2): The RIGHT function does the same job as the LEFT function except for the fact that it extracts a substring from the right of a text. The LEN function measures the length of text. So in this case, the LEN(D5) will return 4. Here, the RIGHT function took the same arguments as the LEFT function. The RIGHT function has to extract the rest of the string that the LEFT function did not extract. Thus, the num_char argument of the RIGHT function is LEN(D5)-2 or 4-2 which equals 2. So the RIGHT function will return the 2 characters from the right of the text. In this case, it’s “US”.
- LEFT(D5,2)&”-“&RIGHT(D5, LEN(D5)-2): The ampersand sign or the concatenation operator will add the texts together. In this case, the texts will be the result of the LEFT function, “MP”, a hash sign “-” and the result of the RIGHT function, “US”.
Step 2:
- As a result, we can see the formula has worked.
- The “-” sign is added in between the strings.
Step 3:
- After that, move the cursor to the bottom right corner of the selected cell.
- The cursor will be replaced by a tiny plus sign.
Step 4:
- After that, move the plus sign and place it in the last cell of the dataset
- As a result, the cells will be filled.
Read More: How to Combine Text and Number in Excel (4 Suitable Ways)
1.5 Deploying Ampersand Operator to Combine Text from Two or More Cells
In this illustration of methods, we will add text from different cells in Excel spreadsheet. Follow the sequential steps below to finish the task.
Step 1:
- Firstly, choose the cell to which you’ll be adding the text.
- Write the formula given below.
=C5&B5- We’ll pick the D5 cell in this case.
- Then, in that cell, enter the equal sign (“=”) .
- After that, select the first cell that contains the text that you wish to add now.
- In this case, we will choose the C5 cell.
- Type the “&” which is the concatenation operator, after that.
- Select the subsequent cell with the text you want to add after the first one.
- In this instance, we will choose the B5 cell.
- Finally, hit Enter.
Step 2:
- As a result, the cell will display the concatenated text.
Step 3:
- Then, navigate to the cell’s bottom right, where the new text was added.
- The cursor will change to a small plus sign.
Step 4:
- After that, move the plus sign down to the last cell of the dataset.
- Consequently, the rest of the cells will be auto-filled with the combined text.
Read More: Combine Text and Formula in Excel (4 Simple Ways)
2. Applying CONCAT Function to Add Text in Excel
The CONCAT function allows users to add text in Excel spreadsheet in various ways. Here, we will discuss them in detail.
2.1 Add Text Without Space
Without a single blank, we will combine two texts. Because of the CONCAT function, we are able to do that. Follow the steps below to do it.
Step 1:
- Firstly, choose the cell to which you’ll be adding the text.
- Type the following formula with the CONCAT function.
=CONCAT(B5,C5)- In this case, we’ll pick cell D5.
- Then, in that cell, enter the equal sign (“=”).
- After that, type “CONCAT”, and the CONCAT function will appear.
- To add text, select the first text or cell that contains it.
- We’ll go with the B5 cell in this instance.
- After that, insert a comma.
- Then, select the text or cell you want to add after the first one by clicking on it.
- Here, we will select the C5 cell.
- Finally, press the Enter key.
Step 2:
- As a result, the cell will display the concatenated text.
Step 3:
- After that, navigate to the cell with the added text now by moving your cursor to its bottom right corner.
- The cursor will change to a small plus sign.
Step 4:
- After that, move the plus sign and the cursor down to the dataset’s final cell.
- Consequently, Excel will fill the rest of the cells according to the formula.
2.2 Add Text with Space
We’ll add two texts in Excel spreadsheet with a space in between. We can do that thanks to the CONCAT function. The procedure is as follows.
Step 1:
- Firstly, choose the cell to which you will add the text first.
- Write down the following formula.
=CONCAT(B5,” “,C5)- In this case, we’ll choose cell D5.
- Then, enter the equal sign (“=”) in that cell,
- After that, type “CONCAT” to access the CONCAT function.
- To add text, select the first text or cell that contains it.
- We’ll select the B5 cell in this instance.
- Add a comma after that.
- Next, add a double quotation(“ “) with a space in between the quotation.
- Then, select the text or cell you want to add after that.
- Here, we will choose the C5 cell.
- Finally, hit Enter to continue.
Step 2:
- Consequently, the concatenated text will appear in the cell.
Step 3:
- After that, by moving your cursor to its bottom right corner, navigate to the cell with the added text
- The cursor will turn into a small plus sign.
Step 4:
- Then, slide the plus sign and the cursor down to the dataset’s final cell.
- With the help of the formula, Excel will fill in the remaining cells.
2.3 Add Text at the End of a String
This additional approach involves appending one text to another text in Excel spreadsheet. Follow the steps listed below to complete the task.
Step 1:
- Firstly, choose the cell to which you will add the text first.
- Type the formula below
=CONCAT(C5,”-US”)- In this instance, we will select cell D5.
- Then, in that cell type the equal sign (“=”).
- Type “CONCAT” on the screen and the CONCAT function will appear.
- To add that text to the document, first, select the text or cell that contains it.
- In this case, we will choose the B5 cell.
- After that, enter a comma.
- Write down the text you want to add after that to the previous string.
- We’ll enter “-US” in this case.
- Finally, hit Enter key.
Step 2:
- Consequently, the text will be added to the end of the string.
Step 3:
- Afterward, go to the cell’s bottom right corner.
- By the cursor, there will be a tiny plus sign.
Step 4:
- Then, slide down the plus sign and the cursor to the last cell of the dataset.
- As a result, the rest of the cells will be auto-filled by Excel.
2.4 Add Text After Nth Character
In this case, we will add a text after the nth character of another text in Excel spreadsheet. Follow the outlined steps below to complete the task.
Step 1:
- Firstly, choose the cell to which you’ll be adding the text.
- The cell in our example is E5.
- After that, write down this formula in the cell
=CONCAT(LEFT(D5,2),"-",RIGHT(D5,LEN(D5)-2))- Finally, hit Enter.
Formula Breakdown
- LEFT(D5,2): The Left function extracts a substring from the left side of a text. It has two arguments, namely: text and num_char. The text argument asks for the text from which the function will extract a substring, and the num_char argument asks for the number of characters the substring will contain. Here, we want to extract the first two characters from the left of our D5 cell text. It will return “MP”.
- RIGHT(D5,LEN(D5)-2): The RIGHT function does the same job as the LEFT function except for the fact that it extracts a substring from the right of a text. The LEN function measures the length of text. So in this case, the LEN(D5) will return 4. Here, the RIGHT function took the same arguments as the LEFT function. The RIGHT function has to extract the rest of the string that the LEFT function did not extract. Thus, the num_char argument of the RIGHT function is LEN(D5)-2 or 4-2 which equals 2. So the RIGHT function will return the 2 characters from the right of the text. In this case, it’s “US”.
- CONCAT(LEFT(D5,2),”-“,RIGHT(D5,LEN(D5)-2)): The CONCAT function will add the texts together. In this case, the texts will be the result of the LEFT function, “MP”, a hash sign “-” and the result of the RIGHT function, “US”.
Step 2:
- Consequently, we can see that the formula was successful.
- Between the strings, a “-“ symbol is added.
Step 3:
- Secondly, hoover the cursor down to the bottom right corner of the selected cell.
- A tiny plus sign will appear in place of the cursor.
Step 4:
- After that, relocate the plus sign and put it in the dataset’s final cell.
- As a result, the cells in Excel will be filled.
Read More: How to Combine Text and Numbers in Excel and Keep Formatting
2.5 Combine Text from Two or More Cells
We will add text from various cells in Excel Spreadsheet in this method illustration. To complete the task, follow the listed sequential steps.
Step 1:
- Firstly, select the cell to which you’ll be adding the text.
- Then, enter the formula below.
=CONCAT(C5,B5)- In this instance, we’ll choose the D5 cell.
- Then, type the equal sign (“=”) in that cell.
- Type “CONCAT” and the CONCAT function will be on the screen.
- After that, choose the first cell that has the text you want to add.
- In our case, we will choose the C5 cell.
- Enter a comma.
- Select the next cell with the text you want to add after the first one.
- Here, we will select the B5 cell.
- Finally, hit Enter.
Step 2:
- Consequently, the combined text will be visible in the cell.
Step 3:
- Secondly, hoover down to the cell’s bottom right, where the new text was added.
- There will be a small plus sign.
Step 4:
- Then, lower the plus sign down to the last cell of the dataset.
- Consequently, the combined text will automatically fill the remaining cells.
3. Applying TEXTJOIN Function to Add Text in Excel
In this method, we will add texts from multiple cells into one cell of Excel spreadsheet by the TEXTJOIN function. To finish the task, adhere to the steps listed beneath.
Step 1:
- Firstly, choose the cell to which you will add the text first.
- Enter the formula below.
=TEXTJOIN(“-”,TRUE,B5,C5,D5)- The D5 cell will be our selection in this case.
- Then, type the equal sign followed by ”TEXTJOIN”.
- The TEXTJOIN function will appear on the screen.
- The first argument it requires is a delimiter that will separate the texts.
- In our case, we will choose the hash sign as our delimiter.
- The next argument asks if it will ignore the empty text.
- We will choose TRUE as our argument.
- Finally, choose the cells that you want to add separated by commas.
- Here, we will select B5, C5, and D5 cells sequentially.
Step 2:
- As a result, the combined text will be visible in the cell.
- They will be separated by a hash.
Step 3:
- Thirdly, go to the cell’s bottom right, where the new text was added.
- A small plus sign will appear.
Step 4:
- Then, slide the plus sign down to the last cell of the dataset.
- As a result, the remaining cells will be filled automatically with the combined text.
Read More: How to Add Text to Multiple Cells in Excel (10 Easy Methods)
4. Implying SEARCH Function to Add Text in Excel
In this case, we will look through a specific text and insert a character right after a specific character. We will use the SEARCH function for this. Follow the steps below to do it.
Step 1:
- Firstly, choose the cell to which you’ll be adding the text.
- The cell in our example is E5.
- Now, write down this formula in the cell
=CONCAT(LEFT(D5,SEARCH(")",D5)),"-",RIGHT(D5,LEN(D5)-SEARCH(")",D5)))- Finally, press Enter.
Step 2:
- Consequently, we can see that between the strings, a “-“ symbol is added.
Formula Breakdown
- SEARCH(“)”,D5): The SEARCH function takes two arguments, namely:find_text and within_text. The find_text argument asks for the text or the character to be searched. The within_text argument takes the text within which it will search as the argument. Here, the SEARCH function will search for the “)” character in the text MP(D)US. The function returns the position of the character within the text in numeric value. In this case, the function returns 5.
- LEFT(D5,SEARCH(“)”,D5)): The Left function extracts a substring from the left side of a text. It has two arguments, namely: text and num_char. The text argument asks for the text from which the function will extract a substring, and the num_char argument asks for the number of characters the substring will contain. Here, as indicated by the SEARCH function we want to extract the first four characters from the left of our D5 cell text. It will return “MP(D)”.
- RIGHT(D5,LEN(D5)-SEARCH(“)”,D5)): The RIGHT function does the same job as the LEFT function except for the fact that it extracts a substring from the right of a text. The LEN function measures the length of text. So in this case, the LEN(D5) will return 7. Here, the RIGHT function took the same arguments as the LEFT function. The RIGHT function has to extract the rest of the string that the LEFT function did not extract. Thus, the num_char argument of the RIGHT function is LEN(D5)-SEARCH(“)“,D5) or 7-5 which equals 2. So the RIGHT function will return the 2 characters from the right of the text. In this case, it’s “US”.
- CONCAT(LEFT(D5,SEARCH(“)”,D5)),”-“,RIGHT(D5,LEN(D5)-SEARCH(“)”,D5))): The CONCAT function will add the texts together. In this case, the texts will be the result of the LEFT function,“MP(D)”, a hash sign “-” and the result of the RIGHT function, “US”.
Step 3:
- Thirdly, go to the cell’s bottom right, where the new text was added.
- A small plus sign will appear.
Step 4:
- Then, lower the plus sign to the last cell of the dataset.
- Consequently, the combined text will automatically fill the remaining cells.
Read More: How to Add Text to Cell Without Deleting in Excel (8 Easy Methods)
5. Utilizing REPLACE Function
In this method, we will add a text to the nth position of another text in Excel spreadsheet by using the REPLACE function. To complete the task, follow the steps provided.
Step 1:
- To begin with, decide which cell you want to add the text to first.
- Write down the following formula.
=REPLACE(D5,2+1,0,”-”)- In this scenario, we will choose the D5
- Then, enter “REPLACE” after the equal sign.
- The screen will display the REPLACE function.
- The first argument it requires is the text from where you want to replace a character.
- In our case, we will choose the text in the D5
- The next argument asks the position where it will replace the text.
- We will choose 2+1 or the 3rd position as our argument.
- Then, we will enter 0.
- As a result, the formula actually adds text to the cell at the designated location without replacing anything.
- Finally, choose the character you want to insert in that position
- In our case, the character is “-”.
- Finally, hit Enter.
Step 2:
- Consequently, the hash sign is added in between the texts.
Step 3:
- Thirdly, go to the cell’s bottom right to the cell of the newly added text.
- There’ll be a tiny plus sign there.
Step 4:
- Then, put the plus sign in the last cell of the dataset.
- Consequently, the added text will automatically fill the remaining cells.
6. Using VBA Code to Add Text
In this method, we will use two VBA codes to add text in Excel spreadsheet. During the process, we will add a prefix and suffix to the texts.
6.1 Add Text at the Beginning of a String
Here, we will add a VBA code to add a prefix to a certain text in Excel spreadsheet. Follow the ensuing steps to do it.
Step 1:
- Firstly, select the cells with text before which you will add a prefix.
- In our case, we will choose cells C5:C10.
Step 2:
- Secondly, go to the Developer tab in the ribbon.
- Then, select the Visual Basic tab.
Step 3:
- Thirdly, in the Visual Basic dialogue box, select Insert.
- Then, choose the Module option.
Step 4:
- After that, write the code on the module.
- Then, paste the following VBA Code here.
Sub TextAtTheBeginning()
'declaring variables'
Dim cellValues As Range
'running the for loop'
For Each cellValues In Application.Selection
'if statement implied'
If cellValues.Value <> "" Then cellValues.Offset(0, 1).Value = "US-" & cellValues.Value
Next
End Sub
VBA Code Breakdown
- The function name is TextAtTheBeginning.
- The variable name is cellValues and it is a range type variable.
- For Each cellValues In Application.Slection:we will run a for loop through each cell that we will select from our dataset.
- If cellValues.Values<> “ “: If the values in the selected cells are not equal to zero. In other words, if the cells are not blank.
- Then cellValues.Offset(0,1).Value= cellValues.Value & “-US”: The code will copy the cell value in the adjacent cell and add “-US” text at the beginning.
Step 5:
- Finally, run the code.
Step 6:
- As a result, we will see the prefix “US-” added in front of the texts.
Read More: How to Add Text in the Middle of a Cell in Excel (5 Easy Methods)
6.2 Add Text at the End of a String
Here, we will add a VBA code to concatenate a suffix to a certain text. To do it, just follow these steps.
Step 1:
- Firstly, choose the text-filled cells in front of which you will add the suffix first.
- In our case, we pick cells C5:C10.
Step 2:
- Secondly, select the Developer tab from the ribbon.
- Then, choose the Visual Basic tab.
Step 3:
- Thirdly, pick Insert in the Visual Basic dialogue box.
- Then, select the Module option next.
Step 4:
- Furthermore, write the code in the module.
- Then, paste the VBA Code here.
Sub TextAtTheEnd()
'declaring variable'
Dim cellValues As Range
'running the for loop'
For Each cellValues In Application.Selection
'if statement implied'
If cellValues.Value <> "" Then cellValues.Offset(0, 1).Value = cellValues.Value & "-US"
Next
End Sub
VBA Code Breakdown
- The function name is TextAtTheEnd.
- The variable name is cellValues and it is a range type variable.
- For Each cellValues In Application.Slection:we will run a for loop through each cell that we will select from our dataset.
- If cellValues.Values<> “ “: If the values in the selected cells are not equal to zero. In other words, if the cells are not blank.
- Then cellValues.Offset(0,1).Value= cellValues.Value & “-US”: The code will copy the cell value in the adjacent cell and add “-US” text at the end.
Step 5:
- Then, run the code to complete the process.
Step 6:
- Finally, we will see the suffix “US-” added at the end of the texts.
Read More: How to Add Text to Cell Value in Excel (4 Easy Ways)
Conclusion
It’s common practice to concatenate text in Excel. In order to effectively manage text data, users frequently need to combine texts. After reading through the article, users will have a better understanding of adding text. They will have ready-made VBA codes to add suffixes and prefixes to their text. If you find it useful, please let us know in the comment section below and share any recommendations and thoughts regarding this or any other content of ours. Thank you for your time. Follow ExcelDemy for more articles like this.
