Quote:
|
Originally Posted by tau
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?
|
EAsy Way:
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.
Harder Way:
Code:
' 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
Sub AddComma()
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
Exit Sub
End If
End If
oCell = "," & curVal
Next
End Sub
Function GetLastRow(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
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
Exit Function
End If
Next i
End Function