Number failing ISNUMBER validation

**Phosphonothioic** · 02-11-2011, 04:10 PM

I have a column of numbers that are failing the ISNUMBER validation because, when copied from a different report, there are three spaces including to the right of the number. So ISNUMBER is returning false until I remove those three spaces. Is there a macro I can build to format these cells correctly, ie remove the extra spaces?

I've tried using TRIM to no avail...

NOTE: Sometime is I simply click on the cell and hit enter the validation will go from FALSE to TRUE. I know this has something to do with the import of data, but I can't fix each cell like this, it'll take me hours everyday. I need a way to automate the process.

**Ron Coderre** · 02-11-2011, 04:18 PM

Try this
• Select your single-column range of "numbers"
• Data.Text-to-Columns...Click: Next
• Make sure "Space" is not checked...Click: Finish

Does that help?

**Phosphonothioic** · 02-11-2011, 04:53 PM

Originally Posted by Ron Coderre

Try this
• Select your single-column range of "numbers"
• Data.Text-to-Columns...Click: Next
• Make sure "Space" is not checked...Click: Finish

Does that help?

Yes, it works. But it leaves me with the problem of automating the process.

I've uploaded the file for people to view. I need the macro to pluck all the seven-digit employee ids from sheet2, column B and places them in column A of sheet 1. As it stands, excel does not recognize those IDs as numbers. Your method works, but I have 1000s I need to convert daily. Can a macro do the text to column process?

Thanks!

**Lancewr** · 02-11-2011, 08:36 PM

Mod. edit: CODE tags at all times please

Sub Macro1()

    Range("G4").Select 'blank cell
    Selection.Copy
    Columns("C:C").Select 'your nimber range
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _
        False, Transpose:=False
End Sub

You can also do this manually. Copy a blank cell, select the column with your numbers, click paste special add.

this adds 0 to all your numbers forcing a string to number conversion

**Huron** · 02-11-2011, 10:45 PM

You could add an extra line to your existing macro to remove the spaces

Sub x()

With Sheet2.UsedRange.Columns(2)

    .SpecialCells(xlConstants).Replace What:=Chr(32), _
        Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=True

    .SpecialCells(xlCellTypeConstants, xlNumbers).Copy Sheet1.Range("A1")

End With

End Sub