Need to remove cells with variable data

C

cthomas

Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.

thanks!
Cheney
 
G

Gary''s Student

Assuming your 7 digit numbers are true numbers, enter in a helper column:

=(LEN(A1)=7)*(ISNUMBER(A1))

and copy down. Switch on autofilter and remove rows with value 1
 
B

Bernie Deitrick

Cheney,

Select a cell in the column with the numbers, and run the macro below. This assumes that your data
is contiguous....

HTH,
Bernie
MS Excel MVP

Sub CheneyDelete7()
Dim myR As Range
Set myR = Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion)
myR.Offset(0, 1).EntireColumn.Insert
myR(1, 2).Value = "Length"
myR(2, 2).Resize(myR.Rows.Count - 1, 1).FormulaR1C1 = "=LEN(RC[-1])"
myR.Offset(0, 1).AutoFilter Field:=1, Criteria1:="7"
myR.Offset(1, 1).Resize(myR.Rows.Count - 1, 1). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
myR.Offset(0, 1).EntireColumn.Delete
End Sub
 
C

cthomas

Cheney,

Select a cell in the column with the numbers, and run the macro below. This assumes that your data
is contiguous....

HTH,
Bernie
MS Excel MVP

Sub CheneyDelete7()
Dim myR As Range
Set myR = Intersect(ActiveCell.EntireColumn, ActiveCell.CurrentRegion)
myR.Offset(0, 1).EntireColumn.Insert
myR(1, 2).Value = "Length"
myR(2, 2).Resize(myR.Rows.Count - 1, 1).FormulaR1C1 = "=LEN(RC[-1])"
myR.Offset(0, 1).AutoFilter Field:=1, Criteria1:="7"
myR.Offset(1, 1).Resize(myR.Rows.Count - 1, 1). _
SpecialCells(xlCellTypeVisible).EntireRow.Delete
myR.Offset(0, 1).EntireColumn.Delete
End Sub



Hi All!
I have a worksheet containing a column for phone numbers. However,
some of the numbers are missing area codes and are listed in 7 digit
format(1234567) How do I remove the rows that contain the cells in the
phone number column that only contain 7 numeric characters. And
sometimes the imported file will have dashes or paranthesis. I can
remove blank rows, or just blank cells, or cells with specific
characters, but how do you remove the data when each digit can be
different? I was hoping the good ol' "*" key would work, but it only
took out cells that actually contain the "*" character. The only
constant is the number of characters, 7. And be gentle, this is only
my third day working with macros.
thanks!
Cheney- Hide quoted text -

- Show quoted text -

You guys Rock! I entered in your entire macro and it worked like a
charm! thank you soooooo much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top