Home » Excel Formulas » How to Add Text to an Excel Spreadsheet – 6 Methods

How to Add Text to an Excel Spreadsheet – 6 Methods

Avatar photo
Written by Adnan Masruf
Last updated: Aug 2, 2024

 

This is the sample dataset.

Sample Dataset

Method 1- Using the Ampersand Operator to Add Text in Excel

1.1 Using the Ampersand Operator to Add Text Without a Space

Step 1:

  • Select the cell to add the text. Here, D5.
  • Enter the formula below
=B5&C5

 

  • Enter the equal sign(“=”) in that cell.
  • Choose the text you want to add. Here, text in B5 cell.
  • Enter the concatenation operator (“&”).
  • Select the next text  you want to add. Here, C5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Hover down to the bottom right corner of the cell containing the added text.
  • A small plus sign will be displayed.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag the cursor down.
  • The rest of the cells will be auto-filled with the added text.

6 Handy Approaches to Add Text in Excel Spreadsheet

1.2 Utilizing the Ampersand Operator to Add Text with Space

Step 1:

  • Select the cell to add the text. Here, D5.
  • Enter the formula below
=B5&” “&C5

 

  • Enter the equal sign (“=”).
  • Select the text. Here, text in B5.
  • Enter the concatenation operator (“&”) after it.
  • Enter  ” “. This means a space.
  • Choose the text to add. Here, in C5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the dataset’s final cell.

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

1.3 Using the Ampersand Operator to Add Text at the End of a String

Step 1:

  • Select the cell to add the text. Here, D5.
  • Enter the formula below.
=C5&”-US”
  • Enter the equal sign (“=”) in that cell.
  • Choose the first text. Here, in B5.
  • Enter the “&” sign.
  • Enter the text that you want to add at the end of the previous string.
  • Here “-US”.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Select the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the last cell of the dataset.

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

1.4 Applying the Ampersand Operator to Add Text After Nth Character

Step 1:

  • Select the cell to add the text. Here, E5.
  • Enter this formula.
=LEFT(D5,2)&"-"&RIGHT(D5,LEN(D5)-2)
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

 Formula Breakdown

  • LEFT(D5,2): The Left function extracts a substring from the left side of a text. It has two arguments: 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 contains. It will return “MP”.
  • RIGHT(D5,LEN(D5)-2): The RIGHT function works like 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. The LEN(D5) returns 4. The RIGHT function took the same arguments as the LEFT function. The RIGHT function extracts the rest of the string. The num_char argument of the RIGHT function is LEN(D5)-2 or 4-2 which equals 2. The RIGHT function returns the 2 characters from the right of the text. Here “US”.
  • LEFT(D5,2)&”-“&RIGHT(D5, LEN(D5)-2): The ampersand sign or the concatenation operator adds the texts together. The texts will be the result of the LEFT function, “MP”, a dash sign “-” and the result of the RIGHT function, “US”.

Step 2:

  • The “-” sign is added between the strings.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Drag down the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

1.5 Using the Ampersand Operator to Combine Text from Two or More Cells

Step 1:

  • Choose the cell to add text. Here, D5.
  • Enter the formula below.
=C5&B5
  • Enter the equal sign (“=”) .
  • Select the first text to add. Here, C5.
  • Enter “&”.
  • Select the text you want to add after the first one. Here, in B5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The cell will display the concatenated text.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the last cell of the dataset.

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

Read More: Add Text and Formula in the Same Cell in Excel

Method 2 – Applying the CONCAT Function to Add Text in Excel

2.1 Add Text Without Spaces

Step 1:

  • Choose the cell to add the text. Here, D5.
  • Use the following formula with the CONCAT function.
=CONCAT(B5,C5)
  • Enter the equal sign (“=”).
  • Enter “CONCAT”, and the CONCAT function will appear.
  • Select the first text. Here, B5.
  • Enter a comma.
  • Select the text you want to add by clicking it. Here, C5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The cell will display the concatenated text.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the final cell.

This is the output.

6 Handy Approaches to Add Text in Excel Spreadsheet

2.2 Add Text with Space

Step 1:

  • Select the cell to add the text. Here, D5.
  • Use this formula.
=CONCAT(B5,” “,C5)

 

  • Enter the equal sign (“=”).
  • Enter “CONCAT”, and the CONCAT function will appear.
  • Select the first text. Here, B5.
  • Enter a comma.
  • Add a double quotation(“ “) with a space.
  • Select the text you want to add. Here, in C5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The concatenated text will be displayed.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the final cell.

6 Handy Approaches to Add Text in Excel Spreadsheet

2.3 Add Text at the End of a String

Step 1:

  • Select the cell to add the text. Here, D5.
  • Use this formula.
=CONCAT(C5,”-US”)

 

  • Enter the equal sign (“=”).
  • Enter “CONCAT”, and the CONCAT function will appear.
  • Select the first text. Here, B5.
  • Enter a comma.
  • Add a double quotation(“ “) with a space.
  • Enter the text you want to add. Here “-US”.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The text will be added at the end of the string.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the final cell.

6 Handy Approaches to Add Text in Excel Spreadsheet

2.4 Add Text After Nth Character

Step 1:

  • Select the cell to add the text. Here, E5.
  • Use this formula.
=CONCAT(LEFT(D5,2),"-",RIGHT(D5,LEN(D5)-2))
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

 Formula Breakdown

  • LEFT(D5,2): The Left function extracts a substring from the left side of a text. It has two arguments: 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. It will return “MP”.
  • RIGHT(D5,LEN(D5)-2): The RIGHT function works like 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. It returns 4. The RIGHT function takes the same arguments as the LEFT function. The RIGHT function extracts the rest of the string left by the LEFT function. The num_char argument of the RIGHT function is LEN(D5)-2 or 4-2 which equals 2. The RIGHT function returns the 2 characters from the right of the text. Here, “US”.
  • CONCAT(LEFT(D5,2),”-“,RIGHT(D5,LEN(D5)-2)): The CONCAT function adds the texts. The texts will be the result of the LEFT function, “MP”, a dash sign “-” and the result of the RIGHT function, “US”.

Step 2:

  •  A “-“ symbol is added.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the final cell.

6 Handy Approaches to Add Text in Excel Spreadsheet

Read More: How to Add a Word in All Rows in Excel

2.5 Combine Text from Two or More Cells

Step 1:

  • Select the cell to add the text. Here, D5.
  • Use this formula.
=CONCAT(C5,B5)
  • Enter the equal sign (“=”).
  • Enter “CONCAT”, and the CONCAT function will appear.
  • Select the text to add. Here, C5.
  • Enter a comma.
  • Select the text you want to add after the first one. Here, B5.
  • Press Enter.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The combined text will be displayed.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 3:

  • Click the plus sign.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 4:

  • Drag it down to the final cell.

6 Handy Approaches to Add Text in Excel Spreadsheet

Method 3 –  Applying the TEXTJOIN Function to Add Text in Excel

Step 1:

  • Select the cell to add the text. Here, D5.
  • Use this formula.
=TEXTJOIN(“-”,TRUE,B5,C5,D5)
  • Enter the equal sign followed by ”TEXTJOIN”. The TEXTJOIN function will appear.
  • The first argument requires is a delimiter to separate the texts. Here, the dash sign.
  • The next argument asks if it will ignore empty text. Choose TRUE .
  • Choose the cells that you want to add separated by commas. Here, B5, C5, and D5 cells.

6 Handy Approaches to Add Text in Excel Spreadsheet

Step 2:

The combined text will be displayed, separated by a dash.

Sample Dataset

Step 3:

  • Click the plus sign.

Sample Dataset

Step 4:

  • Drag it down to the final cell.

Sample Dataset

Read More: How to Add Text to Multiple Cells in Excel

Method 4 – Using the SEARCH Function to Add Text in Excel

Step 1:

  • Select the cell to add the text. Here, E5.
  • Use this formula.
=CONCAT(LEFT(D5,SEARCH(")",D5)),"-",RIGHT(D5,LEN(D5)-SEARCH(")",D5)))
  • Press Enter.

Sample Dataset

Step 2:

  • Between the strings, a “-“ symbol is added.

Sample Dataset

Formula Breakdown 

  • SEARCH(“)”,D5): The SEARCH function takes two arguments :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. 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: 5.
  • LEFT(D5,SEARCH(“)”,D5)): The Left function extracts a substring from the left side of a text. It has two arguments: 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 contains. It returns “MP(D)”.
  • RIGHT(D5,LEN(D5)-SEARCH(“)”,D5)): The RIGHT function works like 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. The LEN(D5) returns 7. The RIGHT function takes the same arguments as the LEFT function. The RIGHT function extracts the rest of the string left by the LEFT function. The num_char argument of the RIGHT function is LEN(D5)-SEARCH(“)“,D5) or 7-5 which equals 2. The RIGHT function returns the 2 characters: “US”.
  • CONCAT(LEFT(D5,SEARCH(“)”,D5)),”-“,RIGHT(D5,LEN(D5)-SEARCH(“)”,D5))): The CONCAT function adds the texts. The texts will be the result of the LEFT function,“MP(D)”, a dash sign “-” and the result of the RIGHT function, “US”.

Step 3:

  • Click the plus sign.

Sample Dataset

Step 4:

  • Drag it down to the final cell.

Sample Dataset

Read More: How to Add Text to Cell Without Deleting in Excel

Method 5 – Utilizing the REPLACE Function

Step 1:

  • Select the cell to add the text. Here, D5.
  • Use this formula.
=REPLACE(D5,2+1,0,”-”)

 

  • Enter “REPLACE” after the equal sign.
  • The first argument is the text from which you want to replace a character with. Here, D5
  • The next argument asks the position to replace the text. Here,  2+1 or the 3rd position.
  • Enter 0. The formula adds text to the cell in the selected location without replacing.
  • Choose the character you want to insert in that position. Here, “-”.
  • Press Enter.

Sample Dataset

Step 2:

  • The dash sign is added between the texts.

Sample Dataset

Step 3:

  • Click the plus sign.

Sample Dataset

Step 4:

  • Drag it down to the final cell.

Sample Dataset

Method 6 – Using a VBA Code to Add Text

6.1 Add Text at the Beginning of a String

Step 1:

  • Select the cells to which you will add a prefix. Here, C5:C10.

Sample Dataset

Step 2:

  • Go to the Developer.
  • Select Visual Basic.

Sample Dataset

Step 3:

  • Select Insert.
  • Choose Module.

Sample Dataset

Step 4:

  • Enter the code in the module.
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

Sample Dataset

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:  will run a for loop through each selected cell.
  • If cellValues.Values<> “ “: If the values in the selected cells are not equal to zero.
  • Then cellValues.Offset(0,1).Value= cellValues.Value & “-US”: The code will copy the cell value in the adjacent cell and add “-US” at the beginning.

Step 5:

  • Run the code.

Sample Dataset

Step 6:

The prefix “US-”  is added.

Sample Dataset

Read More: How to Add Text in the Middle of a Cell in Excel

6.2 Add Text at the End of a String

Step 1:

  • Choose the texts in front of which you will add the suffix. Here, C5:C10.

Sample Dataset

Step 2:

  • Select the Developer tab.
  • Choose Visual Basic.

Sample Dataset

Step 3:

  • Select Insert.
  • Choose Module.

Sample Dataset

Step 4:

  • Enter the code in the module.
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

Sample Dataset

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: will run a for loop through each selected cell.
  • If cellValues.Values<> “ “: If the values in the selected cells are not equal to zero.
  • Then cellValues.Offset(0,1).Value= cellValues.Value & “-US”: The code will copy the cell value in the adjacent cell and add “-US” at the end.

Step 5:

  • Run the code to complete the process.

Sample Dataset

Step 6:

The suffix “US-” is added.

Sample Dataset

Read More: How to Add Text to Cell Value in Excel

Download the Practice Workbook

Related Articles

<< Go Back to Excel Add Text to Cell Value | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Adnan Masruf
Adnan Masruf

Adnan Masruf, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a pivotal role as an Excel & VBA Content Developer at ExcelDemy. His deep passion for research and innovation seamlessly aligns with his dedication to Excel. In this capacity, Masruf not only skillfully addresses challenging issues but also exhibits enthusiasm and expertise in gracefully navigating intricate situations, underscoring his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

See Our Reviews at

trustpilot review

ExcelDemy Consulting Services

Submit Excel Task

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo