Total Empty column between two characters

bdtran

New Member
Joined
Oct 11, 2018
Messages
33
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


ABCDFGHIJKLMNOPQRSTUVWXYZAA
ROW#112172
ROW#2AAAAAAAAAAA

<tbody>
</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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),"")
 
Upvote 0
Hi,

Here's another way:


Book1
ABCDEFGHIJKLM
1 121
2AAAAAAAAA
Sheet688
Cell Formulas
RangeFormula
A1=IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("A",$A2:A2,COLUMN($A2:A2)),0),"")


A1 formula copied across.
 
Last edited:
Upvote 0
Aryatect,

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

Thanks much for your help.
BDT
 
Upvote 0
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.

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1157107
AAAAAAAAAAAAAA

<tbody>
</tbody>

I should get

S1 = 1
V1 = 2
Y1 = 2

Thanks,
BDT
 
Upvote 0
Not sure why, but just swap out the "A" with "zzz" like below:


Book1
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1 115122
2AAAAAAAAAAAAAA
Sheet688
Cell Formulas
RangeFormula
A1=IF(AND(A2="",B2="A"),COLUMN()-IFERROR(LOOKUP("zzz",$A2:A2,COLUMN($A2:A2)),0),"")


A1 formula copied across.
 
Upvote 0
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),"")
 
Upvote 0
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


ABCDEFGHIJKLMNOPQRSTUVWXYZ
12128
2A10AAA0AAAAA11A11010110A

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top