Insert a comma before first number

This tutorial shows how to insert a comma (,) before the first number in a cell through the use of an Excel formula, with the REPLACE, MIN, MID and FIND functions or VBA

EXCEL FORMULA 1. Insert a comma before first number in a cell using the REPLACE, MIN, MID and FIND functions

EXCEL

Hard coded formula
Insert a comma before first number
Cell reference formula
Insert a comma before first number
=REPLACE(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789")),1,","&MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789")),1))
=REPLACE(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789")),1,C5&MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789")),1))
GENERIC FORMULA

=REPLACE(string,MIN(FIND({0,1,2,3,4,5,6,7,8,9},string&"0123456789")),1,","&MID(string,MIN(FIND({0,1,2,3,4,5,6,7,8,9},string&"0123456789")),1))

ARGUMENTS
string: A string that contains numbers before which you want to insert a comma (,).

GENERIC FORMULA

=REPLACE(string,MIN(FIND({0,1,2,3,4,5,6,7,8,9},string&"0123456789")),1,comma&MID(string,MIN(FIND({0,1,2,3,4,5,6,7,8,9},string&"0123456789")),1))

ARGUMENTS
string: A string that contains numbers before which you want to insert a comma (,).
comma: A cell that contains the comma sign.

EXPLANATION

This formula uses the REPLACE, MIN, MID and FIND functions to insert a comma in front of a first number in a cell.
Click on either the Hard Coded or Cell Reference button to view the formula that either has the comma directly entered in the formula or referenced to a cell that captures the value that is to be inserted before a first number in string.

In this example we are adding a comma in front of the first number captured in cell B5, which is number 4.

VBA CODE 1. Insert a comma before first number using VBA formula function

VBA

Hard coded against single cell
Sub Insert_comma_before_first_number()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert comma before the first number in a cell
ws.Range("C5").Formula = "=REPLACE(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&""0123456789"")),1,"",""&MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&""0123456789"")),1))"

End Sub

Cell reference against single cell
Sub Insert_comma_before_first_number()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'insert comma before the first number in a cell
ws.Range("D5").Formula = "=REPLACE(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&""0123456789"")),1,C5&MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&""0123456789"")),1))"

End Sub

Hard coded against range of cells
Sub Insert_comma_before_first_number()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert comma before the first number in a cell by looping through each cell in a specified range

For x = 5 To 8

ws.Range("C" & x).Formula = "=REPLACE(B" & x & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},B" & x & "&""0123456789"")),1,"",""&MID(B" & x & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},B" & x & "&""0123456789"")),1))"

Next

End Sub

Cell reference against range of cells
Sub Insert_comma_before_first_number()
'declare variables
Dim ws As Worksheet
Set ws = Worksheets("Analysis")

'insert comma before the first number in a cell by looping through each cell in a specified range

For x = 5 To 8

ws.Range("D" & x).Formula = "=REPLACE(B" & x & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},B" & x & "&""0123456789"")),1,C5&MID(B" & x & ",MIN(FIND({0,1,2,3,4,5,6,7,8,9},B" & x & "&""0123456789"")),1))"

Next

End Sub

RELATED TOPICS

Related Topic Description Related Topic and Description
Insert comma to end of a cell How to add a comma (,) to the end of a cell through the use of an Excel formula or VBA
Insert a comma after first word How to insert a comma after the first word in a string through the use of an Excel formula or VBA
Insert a comma before each word How to insert a comma before each word through the use of an Excel formula or VBA

RELATED FUNCTIONS

Related Functions Description Related Functions and Description
MIN Function The Excel MIN function returns the smallest value from a specified range of numeric values
MID Function The Excel MID function returns the specified number of characters from a selected string, starting at a specified position
FIND Function The Excel FIND function returns the position of a specific sub-string within a string