Ask Your Question

Count occurrences of specific strings meeting a reg expression

asked 2018-04-24 10:44:40 +0100

mhsiggers gravatar image

updated 2018-04-24 22:12:55 +0100

I have a spread sheet in which in one of the columns the cells contain (maybe several) strings of the form 11C23, 05C15, 46B21. ( They are all of the form [0-9][0-9][A-Z][0-9][0-9], and are separated by spaces or commas. )

Of the possible 260000 codes only about 200 of them occur.

I would like first, to get a list of the strings that occur, and then count the number of times each such string occurs.

I can count the number of times a particular string occurs with


where my string is in C2, so, I guess, if I could get these individual strings into column C, I could copy this command into each row. But how would I get a list of these strings.

Any help would be appreciated.

edit retag flag offensive close merge delete


Quoting @mhsiggers: "... in which in one of the columns the cells contain (maybe several) strings of the form 11C23, 05C15, 46B21."
This needs clarification, in specific: In what way are dispositions made against counting an intersection for two cases of acceptance. E.g.: ?11C23Z99! might be counted for two acceptable strings: 11C23 and 23Z99.
You find a partly related thread here.

Lupp gravatar imageLupp ( 2018-04-24 11:06:45 +0100 )edit

Sorry. They occur as these five character strings and are separated by spaces or commas.

mhsiggers gravatar imagemhsiggers ( 2018-04-24 22:14:09 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-04-24 11:32:09 +0100

JohnSUN gravatar image

Do you look for something like this?

=TEXT(MOD(ROW()-1;10000);"00""" & CHAR(INT((ROW()-1)/10000)+65) & """00")
edit flag offensive delete link more


This isn't exactly what I was looking for, but it works. This generates all possible codes, and so allows me to count them. I was asking to find a list of those codes that occur.
The difference is that this solution blows up my spreadsheet from 400 rows to 260000. (2600 actually, as I settled for counting the occurrences of the the initial three characters of each string,)

Thank you very much for the help.

mhsiggers gravatar imagemhsiggers ( 2018-04-24 22:55:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-04-24 10:44:40 +0100

Seen: 49 times

Last updated: Apr 24 '18