Exclude values within brackets from my formula.

Good Evening. I am working on a project where I need to tally the number of times "31" appears in a cell. An example of my cell data is as follows.

Column C

31\31\7\6\31\31 [1]
31\5\1\6\31\31 [21]
31\31\7\6\31\31 [658]
31\31\5\6\31\16 [1731]

The formula I have created to count the occurrences is as follows:

=(LEN(C1)-LEN(SUBSTITUTE(C1,"31","")))/2

I am sure I coded this wrong, because it would always double the actual occurrences of 31. I divided it by 2 and it worked, but I am sure there is a better way to do it.

However my main problem is what happens if "31" appears within the brackets (EXAMPLE 4), as I do not want that to be a part of my calculations. What modification to my formula would enable me to ignore the values within brackets. I appreciate any help that could be given. Thank you for reading.

Thank you for your reply. To answer your questions:
1: That is a good point. I am still pretty new at excel.
2: The values within the brackets can be from 0 to 4999. In theory, I could exclude every number above and below exactly 31 within that range.
3: I with for the numbers in the brackets to be ignored completely from the formula. I only want to count the instances of 31 that appear next to the \'s

I have attached a sample worksheet as requested. Thanks again for your attention.
31List.xlsx

Originally Posted by FDibbins

This got a bit messy, but it works...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"\31","xx"),"31x","xx"),"31\","xx")

I tried this but it did not work. With the data C1 being:

31\31\15\25\31\31 [1947]

I received this output:

xxx\15\25xxxx [1947]

Originally Posted by Tony Valko

Try this...

Please Login or Register  to view this content.

EDIT: I had to put code tags around the formula to prevent the forum software from trimming out the extra space characters!

This worked like a charm! Thank you!

try this one
=(LEN(LEFT(C1,FIND("[",C1)-1))-LEN(SUBSTITUTE(LEFT(C1,FIND("[",C1)-1),31,"")))/2

Originally Posted by Ghozi Alkatiri

try this one
=(LEN(LEFT(C1,FIND("[",C1)-1))-LEN(SUBSTITUTE(LEFT(C1,FIND("[",C1)-1),31,"")))/2

This one also works, but produces an error if the cell it is referring to is blank. Thanks!

Originally Posted by FDibbins

LOL sorry, I forgot to do the count part...
=LEN(C1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"\31","xx"),"31x","xx"),"31\","xx"))

This one worked also, thanks. Amazing how everyone's code differs but still has the same effect!

Originally Posted by dredwolf

So, you JUST wnat the 31's with no numbers before or after, just the "\31\"'s counted (and if at the start or end ie "31\...", "....\31") ?

if so this nasty one will work:

Formula:

Please Login or Register  to view this content.

Hope this helps :

I tried this one, but all it came back with were 0's for every value. I appreciate the time you took in this matter however, thank you very much