Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Thumbs up Unanswered: comma problem (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? I have over a thousand rows to do if i have to do it manually. please help.


    Thanks

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    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
    ~

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •