Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to remove cells with variable data
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to remove cells with variable data
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 -- Gary''s Student - gsnu200731 " wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to remove cells with variable data
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 wrote in message ups.com... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need to remove cells with variable data
On Jun 20, 2:34 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: 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 wrote in oglegroups.com... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reading data from another sheet where data is in variable cells | Excel Discussion (Misc queries) | |||
Lookup variable data on a spreadsheet, and remove duplicates from another | Excel Worksheet Functions | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
Remove variable text in cells | Excel Worksheet Functions | |||
Lookup with two variable data list cells | Excel Worksheet Functions |