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 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
Insert a value before last nth character
=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)

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.

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 TOPICS

Related Topic Description Related Topic and Description
How to insert a value after the first nth character through the use of an Excel formula or VBA
How to insert a value before the first nth character through the use of an Excel formula or VBA
How to repeat a value n number of times in a cell through the use of an Excel formula or VBA

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
The Excel LEN function returns the number of characters in a specified string