Counting MAX number of consecutive blank cells

[voitoosh]

Hi All,
I have an Excel 2010-related problem.
I need some tips on how to solve this. I have tried some formulas but it only puzzled me :/

I have a row of data, e.g.

WORK WORK WORK WORK __ __ __ __ __ OFF OFF OFF OFF __ __ __ LVE LVE __ WORK WORK WORK. -> this should return 5, as it is the biggest "block" of consecutive blank cells.

I need to calculate what is the MAX number of blank cells in this row (I have tried countifs, MAX etc) but there is probably one long formula to do so. Also, these (blanks) appear in a different places throughout the row (it is basically a roster of a person).

Any ideas how to tackle this without VBA (in which I am rubbish)?

Help much appreciated!

Thanks a lot!

 

[Aladin Akyurek]

Mr. Aladin How are you,
Can you help me again, I have this problem, I have a thousand of files on my excel
MIAP:352BSZG0800C10 Flange SO-Cu/Ni 2" EEMUA 145 Integral Type 16 Bar FF Cu-Ni 90-10
I want to separate the size of a pipe 2" from this data. I want to separate the 2" as per above word, can you please help me sir?
Thanks

A thread of its own for this question would have been a lot better...

Do you always have just one such substring in a given string? If the answer is no, try to provide more examples.

 

[nazarene]

good day Mr. Aladin, yesterday I saw one trend similar to my query, while I am searching through this site, but anyway thank you again Mr. Aladin, next time again, God Bless us

 

[Aladin Akyurek]

good day Mr. Aladin, yesterday I saw one trend similar to my query, while I am searching through this site, but anyway thank you again Mr. Aladin, next time again, God Bless us

Post what you got to close this subthread though...

 

[nazarene]

I saw one trend which is exactly what I need, and I follow the formula he mentioned, as below
this formula take out the number/s from text, up to 25 digits

=SUM(MID(0&M9,LARGE(ISNUMBER(--MID(M9,ROW(INDIRECT("1:"&LEN(M9))),1))*ROW(INDIRECT("1:"&LEN(M9))),ROW(INDIRECT("1:"&LEN(M9))))+1,1)*10^ROW(INDIRECT("1:"&LEN(M9)))/10)

 

[stred]

Ok i am new here and i am sure you can help me, based on the 1st question i have this problem:
i have these data to cells A1:T1 :
1,1, blank, blank, blank, blank, blank, blank, blank, blank, blank, 1,1,blank, blank,blank,1,1,blank,blank

and i use this formula of the 1st post to count the max consecutive blanks =MAX(FREQUENCY(IF(A1:T1="",COLUMN(A1:T1)),IF(A1:T1<>"",COLUMN(A1:T1)))) which give correct the number 9.

now i must find a way to count all the consecutive blanks to this row that obey a condition..

for example 9 consecutive blanks=1 or greater of 8 consecutive blanks = 1 and so on..

plz help!!

 

[Aladin Akyurek]

@stred


Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:T1="",COLUMN(A1:T1)),IF(A1:T1<>"",COLUMN(A1:T1)))>=9,1))

 

[stred]

@stred


Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(A1:T1="",COLUMN(A1:T1)),IF(A1:T1<>"",COLUMN(A1:T1)))>=9,1))

fast and perfect answer! thanks so much!

 

[Aladin Akyurek]

fast and perfect answer! thanks so much!

You are welcome.

 

[tkraft]

Hi Aladin,

Thank you for your solution to this problem. Do you know of a way to count max number of blank cells in a range with the condition of occurring after a condition of a 1st cell (with a number) and before the last cell in that range (with a number)? Does this make sense?

Thanks in advance!

 

[Aladin Akyurek]

@ tkraft

Would you post a small sample along with the desired result for that sample?