0

I have close to one million data in a excel column. I need to identify the formats of all data and count how many data falls under each format.

For example:

Example

  • Data with 10 numeric digits are to be considered as one format and count number of times it has repeated.

  • Data with 3 numeric ditigs are to be considered as one format and count number of times it has repeated.

  • And continue finding the format of data and count it.

    Could anyone help ?

Thanks

Community
  • 1
  • 1
Dhanabalan
  • 23
  • 1
  • 8
  • To give you a great answer, it might help us if you have a glance at [ask] if you haven't already. It might be also useful if you could provide a [mcve]. – Mat Jan 27 '17 at 12:23
  • @Mat Thanks for the suggestion. I have edited it. Hope it is clear now. – Dhanabalan Jan 27 '17 at 13:49

1 Answers1

0

I suggest the following approach:

  1. Follow the instructions here to enable regex matching as an in-cell formula.
  2. Manually cast an eye over the data and come up with a regex that will match a good number of your values. In a new column, set this regex up as a filter condition. Filter those values out of your view.
  3. Repeat step 2 until you've got a set of regexes that covers every format of data in your worksheet.
Community
  • 1
  • 1
jsheeran
  • 2,912
  • 2
  • 17
  • 32