If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
deleting numbers in a column
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? |
#2
|
|||
|
|||
deleting numbers in a column
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).ClearCo ntents End With .AutoFilter .Offset(, 1).EntireColumn.Delete Debug.Print .Parent.UsedRange.Address End With End Sub hth, hector. |
#3
|
|||
|
|||
deleting numbers in a column
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 "Calvinator" wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|