i have an excel data . the 3rd collumn contains phone numbers . i have to delete rows whose phone numbers have less than 10 digits . as the data is very large and not even one mistake is acceptable. i want to use pivot tables or automation script. pivot tables is better because the number of digits is variable and the collumn number is variable. where im stuck. - whenever i use pivot tables to do this the original tabular format is lost . i get some cross tabular format which i dont want. here is the sample data.
date time number count
1-Sep-09 15:29:44 9800000005 1
2-Sep-09 10:07:03 333333 1
3-Sep-09 9:53:46 9800000004 1
7-Sep-09 14:47:31 9800000005 1
10-Sep-09 10:51:39 9800000001 1
12-Sep-09 14:52:50 9800000002 1
13-Sep开发者_如何学C-09 8:28:28 333333 1
17-Sep-09 10:32:13 9800000001 1
18-Sep-09 9:01:42 9800000005 1
I don't think a cube or code is necessary.
Try adding a calculation to show the length of the phone number to cell E2
with the formula
=len(C2)
(assuming that number
appears in C2
) - then copy this formula down to the rest of column E
.
You can then apply an auto-filter to the table, and use a custom filter on column E
to show all rows where the length is greater than or equal to 10.
精彩评论