Insert a value before last nth character

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

Excel

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

VBA

Insert a value before last nth character using VBA

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

EXCEL

Hard coded formula

Insert a value before last nth character

Cell reference formula

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

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

GENERIC FORMULA

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

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

GENERIC FORMULA

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

EXPLANATION

This formula uses the REPLACE and LEN functions to insert a specific value before 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 before 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' before the third (3rd) character from the right in a string captured in cell B5.

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

VBA

Hard coded against single cell

Sub Insert_value_before_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Cell reference against single cell

Sub Insert_value_before_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

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

End Sub

Hard coded against range of cells

Sub Insert_value_before_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value before the last nth character

For i = 5 To 8

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

End Sub

Cell reference against range of cells

Sub Insert_value_before_last_nth_character()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert a specific value before the last nth character

For i = 5 To 8

ws.Range("E" & i).Formula = "=REPLACE(B" & i & ",LEN(B" & i & ")-C" & i & "+1,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
Repeat a value n times in a cell	How to repeat a value n number of times in a cell through the use of an Excel formula or VBA