How to Insert Character Between Text in Excel (5 Easy Methods)

If you want to insert character between text in Excel, this article is for you. Here, we will walk you through 5 easy and effective methods to do the task effortlessly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


5 Methods to Insert Character Between Text in Excel

The following dataset has the State and Number columns. Using this dataset we will demonstrate to you 5 easy and effective methods to insert character between text in Excel. Here, we used Excel 365. You can use any available Excel version.

How to Insert Character Between Text in Excel


1. Use of LEFT and MID Functions with Ampersand Operator

Here, in the Number column, we want to add a Hyphen() between the state abbreviation and numbers. To do so,  we will use the LEFT and MID functions along with the Ampersand (&) operator.

Let’s go through the following steps to do the task.

  • First of all, type the following formula in cell D5.
=LEFT(C5,2) & "-" & MID(C5,3,100)

Formula Breakdown

  • LEFT(C5,2) → the LEFT function returns the character or characters from the beginning position in a number or text string of a cell. The returned characters are based on the number we specify.
  • LEFT(C5,2) → becomes
    • Output: NY
  • MID(C5,3,100) → the MID function returns characters from a text string. It begins from the position we specify and returns the number of characters that we specify.
  • MID(C5,3,100) → becomes
    • Output: 019186
  • NY& “-” &019186 → the Ampersand operator connects NY with Hyphen(-) and 019186.
  • NY& “-” &019186 → becomes
    • Output: NY-019186
    • Explanation: a Hyphen () is added between the abbreviation NY and the numbers 019186 in cell D5.
  • After that, press ENTER.

Then, you can see the result in cell D5.

v

As a result, in the Result column, you can see the inserted character between text.

Read More: How to Add a Character in Excel to Multiple Cells (5 Easy Ways)


2. Applying REPLACE Function to Insert Character Between Text

In this method, we will add a number code (+889) between the state abbreviation and the numbers of the Number column. We will apply the REPLACE function to do the task.

Let’s go through the following steps to do the task.

  • First, type the following formula in cell D5.
=REPLACE(C5,3,0,"(+889)")

Formula Breakdown

  • REPLACE(C5,3,0,”(+889)”) → the REPLACE function replaces a portion in the text string with another number or text we specify.
  • REPLACE(C5,3,0,”(+889)”) → becomes
    • Output: NY(+889)019186
    • Explanation: here, (+889) is added between NY and the numbers 019186 in cell D5.
  • After that, press ENTER.

Therefore, you can see the result in cell D5.

  • Moreover, drag down the formula with the Fill Handle tool.

How to Insert Character Between Text in Excel

Therefore,  in the Result column, you can see the inserted character between text.


3. Using LEFT, SEARCH, RIGHT & LEN Functions

In the following dataset, you can see in the Number column that there is a Hash (#) sign between the state abbreviation and numbers. Next, we will add a number code (+889) after the Hash (#) sign. To so do, we will use the combination of LEFT, SEARCH, RIGHT, and LEN functions.

Let’s go through the following steps to do the task.

  • In the beginning, type the following formula in cell D5.
=LEFT(C5, SEARCH("#", C5)) &"(+889)"& RIGHT(C5, LEN(C5) - SEARCH("#", C5))

How to Insert Character Between Text in Excel

Formula Breakdown

  • SEARCH(“#”, C5) → the SEARCH function returns the number of characters at which a specific character or text string is first found, reading from left to right. Here, the SEARCH function finds out the position of the Hash(#) in cell C5.
    • Output: 3
  • LEN(C5) → the LEN function returns the total number of characters in cell C5.
    • Output: 9
  • RIGHT(C5, LEN(C5) – SEARCH(“#”, C5)) → the RIGHT function returns the character or characters from the end position in a number or text string of a cell. The returned characters are based on the number we specify.
  • RIGHT(C5, 9- 3) becomes
    • Output: 019186
  • SEARCH(“#”, C5)) &”(+889)”& RIGHT(C5, LEN(C5) – SEARCH(“#”, C5)) → the Ampersand “&” operator connects 3 with (+889) and 019186.
  • 3 &”(+889)”& 019186 → becomes
    • Output: 3(+889)019186
  • LEFT(C5, SEARCH(“#”, C5)) &”(+889)”& RIGHT(C5, LEN(C5) – SEARCH(“#”, C5)) → the LEFT function returns the character or characters from the beginning position in a number or text string of a cell. The returned characters are based on the number we specify.
  • LEFT(C5,3(+889)019186) As a result, it becomes
    • Output: NY#(+889)019186
    • Explanation: here, (+889) is added between NY# and the numbers 019186 in cell D5.
  •  Afterward, press ENTER.

Hence, you can see the result in cell D5.

  • Along with that, drag down the formula with the Fill Handle tool.

As a result,  in the Result column, you can see the inserted character between text.

Read More: How to Add Characters in Excel Formula (4 Simple Methods)


Similar Readings


4. Applying Combined Functions to Insert Character Between Text

In the following dataset, you can see in the Number column that there is a space (” “) between the state abbreviation and numbers. here, we will add a number code (+889) after the space(” “). To do the task, we will use the combination of CONCATENATE, LEFT, SEARCH, RIGHT, and LEN functions.

Let’s go through the following steps to do the task.

  • First of all, type the following formula in cell D5.
=CONCATENATE(LEFT(C5, SEARCH(" ", C5)), "(+889)", RIGHT(C5, LEN(C5) -SEARCH(" ", C5)))

How to Insert Character Between Text in Excel

Formula Breakdown

  • SEARCH(” “, C5) → the SEARCH function returns the number of characters at which a specific character or text string is first found, reading from left to right. Here, the SEARCH function finds out the position of the space(” “) in cell C5.
    • Output: 3
  • LEN(C5) → the LEN function returns the total number of characters in cell C5.
    • Output: 9
  • RIGHT(C5, LEN(C5) -SEARCH(” “, C5)) → the RIGHT function returns the character or characters from the end position in a number or text string of a cell. The returned characters are based on the number we specify.
  • RIGHT(C5, 9-3) → becomes
    • Output: 019186
  • LEFT(C5, SEARCH(” “, C5))→ the LEFT function returns the character or characters from the beginning position in a number or text string of a cell. The returned characters are based on the number we specify.
  • LEFT(C5, SEARCH(” “, C5)) → becomes
    • Output: NY
  • CONCATENATE(LEFT(C5, SEARCH(” “, C5)), “(+889)”, RIGHT(C5, LEN(C5) -SEARCH(” “, C5))) → the CONCATENATE function connects or joins the characters into one single text string.
  • CONCATENATE(NY , “(+889)”, 019186)) Then, it becomes
    • Output: NY (+889)019186
    • Explanation: here, (+889) is added between NY and the numbers 019186 in cell D5.
  •  Next, press ENTER.

Therefore, you can see the result in cell D5.

  • Furthermore, drag down the formula with the Fill Handle tool.

Hence,  in the Result column, you can see the inserted character between text.

Read More: Character Code for Check Mark in Excel (2 Applications)


5. Using VBA to Insert Character Between Text

In this method, we will use the VBA code to insert character between text in Excel.

Let’s go through the following steps to do the task.

  • First, we will go to the Developer tab.
  • Then, select Visual Basic.

How to Insert Character Between Text in Excel

At this point, a VBA editor window will appear.

  • Afterward, from the Insert tab >> select Module.

Next, a VBA Module will appear.

At this point, type the following code in the Module.

Sub INSERT_CHARACTER_BETWEEN_CELLS()
Dim Cells As Range
Dim Cell_Range As Range
Set Cell_Range = Application.Selection
Set Cell_Range = Application.InputBox _
("Select Range of Cells to Insert Character", _
"Insert Character Between Cells", Cell_Range.Address, Type:=8)
For Each Cells In Cell_Range
Cells.Value = VBA.Left(Cells.Value, 2) & "(+889)" & _
VBA.Mid(Cells.Value, 3, VBA.Len(Cells.Value) - 1)
Next
End Sub

Code Breakdown

  • We declare INSERT_CHARACTER_BETWEN_CELLS as our Sub.
  • We take Cells and Cells_Range as variables for Range.
  • We use the Left, VBA.Mid, and VBA.Len functions for inserting (+889) between selected cells.
  • We use the For loop to continue the task unless it finds the last cell.
  • Then, we will close the VBA editor window.
  • After that, we will return to our worksheet.
  • Along with that, we will press ALT+F8 to bring out the Macro dialog box so that we can run the code.

Besides pressing ALT+F8, you can go to the Developer tab and select Macros from the Code group to bring out the Macro dialog box,

At this point, a MACRO dialog box will appear.

Make sure the Macro Name contains the Sub of your code.

  • Then, click on Run.

How to Insert Character Between Text in Excel

Later, an Input Box of Insert Character Between Cells will appear.

  • After that, in the Select Range of Cells to Insert Character box, we will select the cells C5:C9.
  • Then, click OK.

Therefore,  in the Result column, you can see the inserted character between text.

How to Insert Character Between Text in Excel


Practice Section

You can download the above Excel file to practice the explained methods.

How to Insert Character Between Text in Excel


Conclusion

Here, we tried to show you 5 methods to insert character between text in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website ExcelDemy to explore more.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo