Insert value in middle of a cell

This tutorial shows how to add a value in the middle of a cell through the use of an Excel formula, with the REPLACE and LEN functions or VBA

Excel

Insert value in middle of a cell using the REPLACE and LEN functions

VBA

Insert value in middle of a cell using VBA formula

EXCEL FORMULA 1. Insert value in middle of a cell using the REPLACE and LEN functions

EXCEL

Hard coded formula

Cell reference formula

=REPLACE(B5,(LEN(B5)/2)+1,0,"x")

=REPLACE(B5,(LEN(B5)/2)+1,0,C5)

GENERIC FORMULA

=REPLACE(string,(LEN(string)/2)+1,0,"x")

ARGUMENTS
string: The string in the middle of which you want to insert a value.

GENERIC FORMULA

=REPLACE(string,(LEN(string)/2)+1,0,value)

ARGUMENTS
string: The string in the middle of which you want to insert a value.
zero: A cell that captures the value that you want to insert in the middle of a string.

EXPLANATION

This formula uses the REPLACE and LEN functions to insert a specific value in the middle of a string captured in a cell.

Click on either the Hard Coded or Cell Reference button to view the formula that either has the value that you want to insert in the middle of a cell entered directly in the formula or referenced to a cell.

In this example we are adding the value x in the middle of a string captured in cell B5.

VBA CODE 1. Insert value in middle of a cell using VBA formula

VBA

Hard coded against single cell

Sub Insert_value_in_middle_of_a_cell()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert value in middle of a cell using VBA formula
ws.Range("C5").Formula = "=REPLACE(B5,(LEN(B5)/2)+1,0,""x"")"

End Sub

Cell reference against single cell

Sub Insert_value_in_middle_of_a_cell()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert value in middle of a cell using VBA formula
ws.Range("D5").Formula = "=REPLACE(B5,(LEN(B5)/2)+1,0,C5)"

End Sub

Hard coded against range of cells

Sub Insert_value_in_middle_of_a_cell()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert value in middle of a cell using VBA formula

For i = 5 To 8

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

End Sub

Cell reference against range of cells

Sub Insert_value_in_middle_of_a_cell()

'declare variables
Dim ws As Worksheet

Set ws = Worksheets("Analysis")

'insert value in middle of a cell using VBA formula

For i = 5 To 8

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

End Sub

Related Topic	Description	Related Topic and Description
Add leading zero to a number	How add a leading zero (0) to a number through the use of an Excel formula or VBA
Add trailing zero to a number	How to add trailing zero to a number through the use of an Excel formula or VBA
Add trailing zeros to a number to make number certain length	How add trailing zeros to a number to make number certain length through the use of an Excel formula or VBA