Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,513
    Thanks
    137
    Thanked 8 Times in 8 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,838
    Thanks
    411
    Thanked 1,570 Times in 1,421 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 15: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,847
    Thanks
    146
    Thanked 723 Times in 657 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 15:58.

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,847
    Thanks
    146
    Thanked 723 Times in 657 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,513
    Thanks
    137
    Thanked 8 Times in 8 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
  •