Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Converting valuesin proper number

    I have a spreadsheet that was imported and need to convert the values into number so they can be added. I tried for eg = substitute(E5," ",""). It converts the number but I cannot use it to add a row or column of numbers. Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Howard,

    If you select the numbers to be converted and then run this macro:
    Code:
    Sub FixNumbers()
    
       Dim rngCell  As Range
       
       For Each rngCell In Selection
          rngCell.Formula = "=0" & Replace(rngCell.Value, " ", "")
       Next rngCell
       
    End Sub
    It will convert then numbers properly. However, be warned if there are any text values in the selection like "Index" or blank cells in your example it will convert them to zero values. HTH
    Last edited by RetiredGeek; 2013-12-18 at 16:43.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Or an alternative:

    Code:
    Public Sub ConvertNums()
    Dim rng As Range
    Set rng = Range("E5:J21")
    For Each cell In rng
       cell.Value = Val(cell.Value)
    Next cell
    End Sub
    ConvertNums.png

    Same warning as RG
    Last edited by Maudibe; 2013-12-18 at 16:58.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    You could add an if statement to skip anything that will covert to a zero. I will skip text and blanks, however, it will also skip converting a text zero to a number zero.

    Code:
    If Val(cell.Value) <> 0 Then cell.Value = Val(cell.Value)

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Guys

    Thanks for help, much appreciated

    Regards

    Howard

Posting Permissions

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