Insert a value after last nth character

This tutorial shows how to insert a value after the last nth character through the use of an Excel formula, with the REPLACE and LEN functions or VBA

Excel

Insert a value after last nth character using the REPLACE and LEN functions

VBA

Insert a value after last nth character using VBA

EXCEL FORMULA 1. Insert a value after last nth character using the REPLACE and LEN functions

EXCEL

Hard coded formula

Cell reference formula

=REPLACE(B5,LEN(B5)-3+2,0,"x")

=REPLACE(B5,LEN(B5)-C5+2,0,D5)

GENERIC FORMULA

=REPLACE(string,LEN(string)-nth_char+2,0,value)

ARGUMENTS
string: The string in which you want to insert a value after the last nth character.
nth_char: A number which represents after which character in the string you want to insert a specific value.
value: A value that you want to insert after the last nth character in a string.

GENERIC FORMULA

=REPLACE(string,LEN(string)-nth_char+2,0,value)

EXPLANATION

This formula uses the REPLACE and LEN functions to insert a specific value after the last nth character in a string.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the value and the number that represents after which character from the right to insert the value entered directly in the formula or referenced to cells.

In this example we are inserting a value of 'x' as the third (3rd) character from the right in a string captured in cell B5.

VBA CODE 1. Insert a value after last nth character using VBA

VBA

Hard coded against single cell

Sub Insert_value_after_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value after the last nth character
ws.Range("C5").Formula = "=REPLACE(B5,LEN(B5)-3+2,0,""x"")"

End Sub

Cell reference against single cell

Sub Insert_value_after_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value after the last nth character
ws.Range("E5").Formula = "=REPLACE(B5,LEN(B5)-C5+2,0,D5)"

End Sub

Hard coded against range of cells

Sub Insert_value_after_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value after the last nth character

For i = 5 To 8

ws.Range("C" & i).Formula = "=REPLACE(B" & i & ",LEN(B" & i & ")-3+2,0,""x"")"
Next i

End Sub

Cell reference against range of cells

Sub Insert_value_after_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value after the last nth character

For i = 5 To 8

ws.Range("E" & i).Formula = "=REPLACE(B" & i & ",LEN(B" & i & ")-C" & i & "+2,0,D" & i & ")"
Next i

End Sub

Related Topic	Description	Related Topic and Description
Insert a value after first nth character	How to insert a value after the first nth character through the use of an Excel formula or VBA
Insert a value before first nth character	How to insert a value before the first nth character through the use of an Excel formula or VBA
Insert a value before last nth character	How to insert a value before the last nth character through the use of an Excel formula or VBA