I have a column of number on my excel sheet that i would like to add a comma in front of each value. Is their an easy way to do this after the numbers are inputed? I have over a thousand rows to do if i have to do it manually. please help.
I have a column of number on my excel sheet that i would like to add a comma in front of each value. Is their an easy way to do this after the numbers are inputed?
If you have a column of numbers in column D, (1) add a new column after 'D' and enter this formula then Fill it down the column.
="," & D1
The formula will produce a new column of nums with the comma in front.
(2) Copy the new column with the formulas in it.
(3) Paste with 'Edit->Paste Special->Paste Values' to paste the values only without the formula.
(4) Delete the original column after confirming ok results.
' Create a new workbook
' open the vb editor, add a module, add this code
' Attach a new toolbar button to Sub AddComma()
' Return to your target workbook
' select cell in the column you want commas
' click the button
intCol = ActiveCell.Column
intLRow = GetLastRow(ActiveCell)
For i = 1 To intLRow
Set oCell = Cells(i, intCol)
curVal = oCell
If curVal Like ",*" Then
msg = "The value in Cell " & oCell.Address & " already has a comma"
Response = MsgBox(msg, vbOKCancel)
If Response = vbCancel Then
oCell = "," & curVal
Function GetLastRow(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
GetLastRow = WorkRange(i).Row