deleting numbers in a column

[Calvinator]

One of my columns should be only text. I converted pdf file of 30,000
records to excel. One of my columns ended with some student id #s in the
ethnicity column. Can I do something to cause each cell in the column to go
blank if it contains a number?

 

[Héctor Miguel]

hi, !

One of my columns should be only text.
I converted pdf file of 30,000 records to excel.
One of my columns ended with some student id #s in the ethnicity column.
Can I do something to cause each cell in the column to go blank if it contains a number?

you might give a try a macro like following one ?
(assuming "the column" is B and row1 has titles, so data begins in [B2] and NO blank cells between)

Sub Clear_If_Number()
With Range("b2") ' <= modify if real data begins in a different cell '
.Offset(, 1).EntireColumn.Insert
With Range(.Offset, .End(xlDown))
.Offset(, 1).Formula = _
"=sumproduct(--isnumber(search({0;1;2;3;4;5;6;7;8;9}," & .Cells(1).Address(0, 0) & ")))"
.Offset(-1).Resize(.Rows.Count + 1, 2).AutoFilter Field:=2, Criteria1:=">0"
.Offset(1).SpecialCells(xlCellTypeVisible).ClearContents
End With
.AutoFilter
.Offset(, 1).EntireColumn.Delete
Debug.Print .Parent.UsedRange.Address
End With
End Sub

hth,
hector.

 

[Ashish Mathur]

Hi,

Select the column and press Ctrl+G > Special > Constants > Numbers and then
click on OK. This will select all the numbers. Now simply hot the delete
button

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com