how to remove numeric with text in a column in excel?

Question

how to remove text i a given  column and only keep number?

Answer 1

Select the column.

Press Ctrl+G or F5 to display the Go To dialog.

Click Special...

Select the Constants radio button.

Clear all check boxes except the Text check box.

Click OK.

Only the text cells should be selected.

Press Delete to clear the cells.

Answer 2

You could use a macro.

Select the range then run.

Sub RemoveAlphas()

Dim intI As Integer

Dim rngR As Range, rngRR As Range

Dim strNotNum As String, strTemp As String

    Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _

            xlTextValues)

    For Each rngR In rngRR

        strTemp = ""

        For intI = 1 To Len(rngR.Value)

            If Not Mid(rngR.Value, intI, 1) Like "[A-Z,a-z]" Then

                strNotNum = Mid(rngR.Value, intI, 1)

            Else: strNotNum = ""

            End If

            strTemp = strTemp & strNotNum

        Next intI

        rngR.Value = strTemp

    Next rngR

End Sub

Gord

Answer 3

no sir , this is not working.. it deleting  entire column..

Answer 4

That means that the "number" values are actually text values that look like numbers.

You could run this macro:

Sub DeleteText()

    Application.ScreenUpdating = False

    Selection.NumberFormat = "General"

    Selection.Value = Selection.Value

    Selection.SpecialCells(xlCellTypeConstants, _

        xlTextValues).ClearContents

    Application.ScreenUpdating = True

End Sub

Answer 5

Hi,

Select both columns and get the filter icon.  Filter the second column on Blanks.  Now delete those rows.