Add Characters Every X Characters in Excel Cells - Easy Methods

When working with product codes, serial numbers, phone numbers, ID numbers, or long text strings in Excel, you may need to insert a specific character every fixed number of characters. For example, you may want to convert ABCDEF123456 into ABC-DEF-123-456, or add spaces every 4 digits to make long numbers easier to read.
Excel does not provide a direct built-in button for this task, but you can easily do it with some easy methods. This article will show you several practical methods to add or insert specific characters every X characters in Excel cells.

Add Characters Every X Characters in Excel

• Method 1: By formula (Excel 365, Excel 2021 and later versions)
• Method 2: By VBA code
• Method 3: By User Defined Function
• Method 4: By Kutools for Excel

Important Notes & Limitations

Conclusion

Add Characters Every X Characters in Excel

The following methods will show you how to quickly insert specific characters every specified number of characters in Excel, helping you easily separate long text, IDs, product codes, or number strings at fixed intervals to make the content more standardized and easier to read.

Method 1: Add Characters Every X Characters with formula

If you are using Excel 365 or Excel 2021, you can use a dynamic array formula to split the text into fixed-length groups and then join them with a specified character.

Enter the following formula into a blank cell:

=TEXTJOIN("-",TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/4,0),,1,4),4))

Then press Enter key. Drag the fill handle down to fill the formula to other cells.

Customize the formula

You can change the character and interval as needed.

=TEXTJOIN(" ",TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/4,0),,1,4),4))

=TEXTJOIN("/",TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/2,0),,1,2),2))

=TEXTJOIN($B$1,TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/$C$1,0),,1,$C$1),$C$1))

Pros and cons

Method 2: Add Characters Every X Characters with VBA code

If you want to insert a specific character every fixed number of characters in multiple cells and directly output the result, VBA is a good option.

1. Hold down the ALT + F11 keys in Excel, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.

Sub InsertCharacter()
'Updateby Extendoffice
Dim Rng As Range
Dim InputRng As Range, OutRng As Range
Dim xRow As Integer
Dim xChar As String
Dim index As Integer
Dim arr As Variant
Dim xValue As String
Dim outValue As String
Dim xNum As Integer
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
xRow = Application.InputBox("Number of characters :", xTitleId, Type:=1)
xChar = Application.InputBox("Specify a character :", xTitleId, Type:=2)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set OutRng = OutRng.Range("A1")
xNum = 1
For Each Rng In InputRng
    xValue = Rng.Value
    outValue = ""
    For index = 1 To VBA.Len(xValue)
        If index Mod xRow = 0 And index <> VBA.Len(xValue) Then
            outValue = outValue + VBA.Mid(xValue, index, 1) + xChar
        Else
            outValue = outValue + VBA.Mid(xValue, index, 1)
        End If
    Next
    OutRng.Cells(xNum, 1).Value = outValue
    xNum = xNum + 1
Next
End Sub

3. Press F5 key to run this code. A prompt box will appear, asking you to select the data range where you want to insert a specific character into the text strings. See the screenshot below:
   
4. And click OK, in the following prompt box, please enter the number that you want to insert certain character every after, see screenshot:
   
5. Go on clicking OK, and in the followed prompt box, please enter the certain character that you want to add to the text, see screenshot:
   
6. Click OK button, and choose one cell where you want to output the result in the prompt box, see screenshot:
   
7. Finally, click OK. Excel will insert the specified character every X characters and place the results in the selected output range.
   

Pros and cons

Method 3: Add characters every X characters with User Defined Function

A User Defined Function, also called a UDF, lets you create your own Excel function with VBA. After adding the function, you can use it like a normal worksheet formula.

1. Press Alt + F11 to open the VBA editor.
2. Click Insert > Module. Copy and paste the following code into the module.

Function InsertEveryX(TextStr As String, InsertChar As String, IntervalNum As Long) As String
    Dim i As Long
    Dim ResultStr As String
    If IntervalNum <= 0 Then
        InsertEveryX = TextStr
        Exit Function
    End If
    For i = 1 To Len(TextStr) Step IntervalNum
        ResultStr = ResultStr & Mid(TextStr, i, IntervalNum) & InsertChar
    Next i
    If Len(ResultStr) > 0 Then
        ResultStr = Left(ResultStr, Len(ResultStr) - Len(InsertChar))
    End If
    InsertEveryX = ResultStr
End Function

3. Close the VBA editor and return to the worksheet. Use the following formula:

=InsertEveryX(A2,"-",4)

Then drag the fill handle down to apply the formula to other cells.

Pros and cons

Method 4: Add characters every X characters with Kutools for Excel

If you do not want to use complex formulas or VBA code, Kutools for Excel provides an easier way to insert characters into cell text. With its text tools, you can quickly add specific characters at fixed positions or apply text-processing operations to multiple cells.

1. Select the cells where you want to insert characters. Then, Click Kutools > Text > Add Text.
   
2. In the Add Text dialog box:
   1. Enter the character you want to insert, such as -, /, or a space into the Text box.
   2. Under the Add Position section, choose Specify position and enter the positions where the character should be inserted.
   3. Click OK.
      

Now, the specified character has been inserted into the cell values at the specified intervals.

Important Notes & Limitations

=IF(A2="","",TEXTJOIN("-",TRUE,MID(A2,SEQUENCE(ROUNDUP(LEN(A2)/4,0),,1,4),4)))

Conclusion

Adding characters every X characters in Excel can make long text strings easier to read, format, and manage. For Excel 365 or Excel 2021 users, the formula method is a flexible and dynamic solution. If you need to process many cells at once, VBA can help you complete the task efficiently. If you want a reusable custom formula, a User Defined Function is a good choice. For users who prefer a simple, visual, and no-code method, Kutools for Excel provides a faster way to handle this type of text formatting task.

In general, use formulas when you want dynamic results, use VBA or UDFs when you need more control, and use Kutools when you want the easiest workflow with fewer manual steps.