开发者

removing some rows based on criteria - solving this by pivot tables

开发者 https://www.devze.com 2022-12-10 11:33 出处:网络
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

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消