some numbers formatted as text

**peterhw** · 01-30-2012, 05:24 AM

I have had a problem with some data not appearing to be what I thought it was. A column that appeared to be all numbers contained some text fields.

I'd been looking for a solution and with the help of members (of another forum) I think I now have a solution.

If I have a character 1 in a cell - then that cell is 1 character long and has the ASCII code 49 but when I apply isnumeric then I may get TRUE for some cells and FALSE for other cells.

Not having control of the data source I wanted to be able to validate the data - prior to use.
So simple but just select the range then multiply the source by 1 and replace

    Private Sub CommandButton2_Click()
    Set myRng = Sheets(ActiveSheet.Name).Range("A29:C34")
    For Each rngCell In myRng
    rngCell.Value = rngCell.Value * 1
    Next rngCell
    End Sub

Hope this is useful

**Pepe Le Mokko** · 01-30-2012, 05:44 AM

Thanks for the macro
Adding 0 also does the trick and it can be done with Paste Special Values - Multiply ( after copying 1) or add ( after copying 0)
If the values are involved in calculations they are coerced automatically within the calcualtion