How to find if cell adjacent to a blank cell is also blank?

Badunit wrote:

I'm not sure there is a function that can determine if two adjacent cells are totally blank (those where ISBLANK would return TRUE). A formula that checks for "" (null string) will work for cells that are blank or have the formula result of "".

IF(COUNTIFS(B,"",C,"")>0,"Adjacent Blanks","No Adjacent Blanks")

I'm going to be honest, I don't know why I didn't try this when you posted it... I believe this is exactly what I'm looking for. I think I just didn't understand how it worked. Does it essentially act like an AND function, counting only if ALL conditions are met?

Badunit wrote:

Also, as with the COUNTIFS formula I provided, TEXTJOIN with the FALSE parameter gives the same result for "" or blank cells. If you need them to be absolutely blank, you may need to use a column of formulas to check each row individually with AND(ISBLANK(A),ISBLANK(B)).

I notice that TEXTJOIN with the TRUE parameter will skip over blank cells but will include cells with "".

This is an interesting observation. I was playing around with this and one way I found to be able to detect this and prevent this confusion in results would be to use a combination of =COUNTA and =COUNTIFS with regex to 'check' the data.

As a test, I input "" in cell B10. If I use your initial suggestion with the formula: "COUNTIFS(Table 1::A,"",Table 1::B,"")" I only get a result of 3 double blanks instead of 4 (since cell B10 is technically not blank), as you pointed out would happen. However, if I use "COUNTA(Table 1::A:B)" (which is the formula under "COUNTA") and "COUNTIFS(Table 1::A:B,REGEX("\d{1,3}((\,\d{3})+)*\.\d{2}",case-sensitive))" (which is the formula under "REGEX MATCH"), I can have another formula that checks those two numbers to make sure they are the same number. If so, there should be no cells with ""(null strings/fake blanks). It is essentially making sure the total number of cells that are not blank = all the cells that are formatted as they're supposed to be for my needs (as defined by my regex). (For anyone else, I suppose the regex could be changed to fit your needs or a different function might be used to compare to COUNTA).

Please correct me if I'm wrong but from here, by using this in addition to "COUNTIFS(Table 1::A,"",Table 1::B,"")" this should work flawlessly to detect two adjacent blanks while also making sure that null strings ("") do not throw off the data, correct?

Badunit wrote:

See my post on bugs.

Are you referring to what you said earlier (quoted below)?

Also, as with the COUNTIFS formula I provided, TEXTJOIN with the FALSE parameter gives the same result for "" or blank cells. If you need them to be absolutely blank, you may need to use a column of formulas to check each row individually with AND(ISBLANK(A),ISBLANK(B)).

I notice that TEXTJOIN with the TRUE parameter will skip over blank cells but will include cells with "". Maybe that can be used...

...but I think that is a bug in the TEXTJOIN function so I wouldn't try to exploit it.

If so, I would agree with stfflspl here and I don't see how that's a bug.

If not, could you please link your post?

SGIII wrote:

I'm thinking there may be an easier solution if you add a third column and fill it with a character you wouldn't be using otherwise (perhaps a ^) and then apply TEXTJOIN to A:C

Aha! This works well! Using this, I get the following result:

2.99~~^~4,000.00~~^~2.99~~^~~51.99~^~0.01~~^~~60.00~^~43.39~~^~~25.00~^~257.69~~^~~~^~~~^~~50.00~^~1.00~40.00~^~~~^~942.96~~^

From here, I'd imagine a simple COUNTMATCHES, like you proposed initially, would get us to the solution.

It sounds like using "COUNTIFS(Table 1::A,"",Table 1::B,"")" in addition to COUNTA and a regex check, as I described in response to Badunit earlier, would also work. I'm curious which of these two methods is more efficient.

PuddleOfFat wrote:

Please correct me if I'm wrong but from here, by using this in addition to "COUNTIFS(Table 1::A,"",Table 1::B,"")" this should work flawlessly to detect two adjacent blanks while also making sure that null strings ("") do not throw off the data, correct?

Expanding on this: here is a formula I came up with that incorporates all of these ideas above. It seems to work exactly as intended:

=IF(COUNTA(A:B)=COUNTIFS(A:B,REGEX("\d{1,3}((\,\d{3})+)*\.\d{2}",case-sensitive)),IF(COUNTIFS(A,"",B,"")>0,"Error: there are adjacent blanks","Perfect: NO adjacent blanks"),"Error: there is a hidden null string or incorrectly formatted number")

SGIII’s idea of TEXTJOIN and COUNTMATCHES also works. I’m curious which of these two approaches would be more efficient and reliable.

This is brilliant! I have been playing around with this and I discovered a flaw, though: the regex can trigger a false error. If there is a value in Column A and a value in Column B in the row below that, the regex will incorrectly result in an error, thinking there are two adjacent blanks. This is because, for example, A1=has_value, B1=blank, A2=blank, B2=has_value. Thus, the regex finds two blanks consecutively, thinking they're adjacent. However, if there is a value in Column B and two adjacent blank cells in the row below that, then a value in Column A in the row below those two blanks, the regex would also result in "~~~" which correctly identifies two blanks.

The formula used on the table above:

=TEXTJOIN("~",0,Table 1::A:B)

which results in:

2.99~~4,000.00~~2.99~~~51.99~0.01~~~60.00~43.39~~~25.00~257.69~~~~~~~50.00~1.00~40.00~~~942.96~

In the table above, the two blank cells in B3 & A4 will result in "~~~" which would incorrectly trigger an error:

2.99~~4,000.00~~2.99~~~51.99~0.01~~~60.00~43.39~~~25.00~257.69~~~~~~~50.00~1.00~40.00~~~942.96~

From B13-A15, though, the regex would correctly trigger an error from the two adjacent blanks:

2.99~~4,000.00~~2.99~~~51.99~0.01~~~60.00~43.39~~~25.00~257.69~~~~~~~50.00~1.00~40.00~~~942.96~

So I have been playing around with different ways of trying to fix this.

I tried creating a regex that knows where each row ends- in this case, every second "~". I came up with the following (bulky but working) regex:

(?:\d{1,3}(?:(?:\,\d{3})+)*\.\d{2})?~(?:\d{1,3}(?:(?:\,\d{3})+)*\.\d{2})?(~)

I should specify that the numerical data in my table will always have the same format: 1.00 / 100.00 / 1,000.00 / etc. - hence why I am using "(?:\d{1,3}(?:(?:\,\d{3})+)*\.\d{2})" in my regex to match the number values. Anyways, the regex matches every 2nd "~" and groups them into Group 1. I'd like to try to replace all the matched "~"s in Group 1 with a comma, so that I could ultimately have a joined text that joins the two values in every row in the table with a "~" between them but a comma that separates each row. It would look like this: "[A1]~[B1],[A2]~[B2]," etc... From there, I can use a regex to easily check if there are two adjacent blanks in the same row, since the regex would know where the rows begin and end. This is where I got stumped and couldn't figure out how to achieve that, whether through regex or formulas...

I also tried using TEXTJOIN on the individual columns to get two separate strings:

For Column A: 2.99~4,000.00~2.99~~0.01~~43.39~~257.69~~~~1.00~~942.96

For Column B: ~~~51.99~~60.00~~25.00~~~~50.00~40.00~~

From there, I was thinking I could somehow group together the Nth value from each, with each row separated by a comma. Effectively trying to achieve the same thing as above but by a different means. Again, I was stumped by this as well.

I also tried some other methods that I don't think will lead to a solution, so I will spare you the reading time lol. All in all, I'm not sure if I'm approaching this problem correctly but here is where I'm at.

The problem with this is that, for my purposes, I'm trying to achieve finding two adjacent blanks using only formulas in the header column(s), not in each individual row. Otherwise, I could just use =ISBLANK for the two cells in every row as well as your approach.

It seems something is bugged still. When using a null string ("") compared to a regular cell, COUNTBLANK counts the null string as blank, whereas ISBLANK identifies it as not blank. But by that logic, COUNTBLANK should therefore result in 0 or ISBLANK should result in TRUE. They should be in sync, right?

Badunit wrote:

Now I am wondering how to count cells that have a null string.

If COUNTA is not bugged, you could use the following formula to find null strings:

=IF(AND(COUNTA(A1)=1,LEN(A1)=0),"1","0")

This formula would have to be copied into every row of the table, and then a sum of those matches could count the total null strings.