# Total Empty column between two characters

#### bdtran

##### New Member
Hello Team,

I need your help with a formula to calculate how many empty column between of the letter “A” as in the row#2. The result that I am looking for should be the same as in row#1 as shown below.

Any help would be greatly appreciated.
BDT

 A B C D F G H I J K L M N O P Q R S T U V W X Y Z AA ROW#1 1 2 1 7 2 ROW#2 A A A A A A A A A A A

<tbody>
</tbody>

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Special-K99

##### Well-known Member
in A1
=IF(AND(A2="",B2="A"),COLUMN()-MAX(IF(\$A2:A2="A",COLUMN((\$A2:A2)))),"")
Array formula, use Ctrl-Shift-Enter

copy along as far as AA1

#### Aryatect

##### Active Member
Hi,

Below is same fundamental logic as Special-K99 but without invoking Ctrl+Shift+Enter:

Code:
``=IF(AND(C2="A",B2=""),COLUMN()-AGGREGATE(14,6,(\$A2:B2<>"")*COLUMN(\$A2:B2),1),"")``

#### jtakw

##### Well-known Member
Hi,

Here's another way:

A1 formula copied across.

Last edited:

#### bdtran

##### New Member
Special-K99,

I tested your formula and it worked.

Thank you so much!
BDT

#### bdtran

##### New Member
Aryatect,

I used for your formula and got the result I am looking for.

BDT

#### bdtran

##### New Member
Hi,

Here's another way:

ABCDEFGHIJKLM
1121
2AAAAAAAAA

<tbody>
</tbody>
Sheet688

Worksheet Formulas
CellFormula
A1=IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("A",\$A2:A2,COLUMN(\$A2:A2)),0),"")

<tbody>
</tbody>

<tbody>
</tbody>

A1 formula copied across.

JTAKW,

I tested your formula and somehow I got the unexpected result as below. Not sure it was on my end error.

I copied to the A1 as instructed.

 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 1 1 5 7 10 7 A A A A A A A A A A A A A A

<tbody>
</tbody>

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT

#### jtakw

##### Well-known Member
Not sure why, but just swap out the "A" with "zzz" like below:

A1 formula copied across.

#### Rick Rothstein

##### MrExcel MVP
Here is another normally entered solution...

If it is possible for cell A2 to be blank, then put this formula in cell A1, otherwise leave cell A1 blank...

=IF(AND(A2="",B2="A"),1,"")

Now put his formula in cell B1 and copy it across to the end of your data...

=IF(AND(B2="",C2="A"),COUNTIF(\$A2:B2,"")-SUM(\$A1:A1),"")

#### bdtran

##### New Member
All,

While checking the data, I encountered a sheet containing the sample data as in the table below. Similarly, I need a formula for the result in row#1 with total of columns (not empty) between the letter "A".

Thank you so much for all the help.
BDT

 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 1 2 1 2 8 2 A 1 0 A A A 0 A A A A A 1 1 A 1 1 0 1 0 1 1 0 A

<tbody>
</tbody>

1,102,736
Messages
5,488,566
Members
407,646
Latest member
utl1095

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...