Excel - making a new row/line using the last character of a column

[fbm2themex]

I have a column, with about 669 cells filled with info and 719 empty...total of 1388 cells...im trying to make a new empty row to separate them...separated by the next value...
not sure to use what formula, either =left or =len or what to use.
heres an example
col 1 col 2
BTC Y
BTC
BTC Y
BTC Y
BTC

trying to do this
col 1 col 2
BTC Y
BTC

BTC Y

BTC Y
BTC

is this possible in excel?

 

[Gavona]

How about using the inbuilt subtotalling functionality to do the work for you?:

Assuming that the first cell in col2 is B2 and that you have column headings in row 1:
insert a temporary helper column, C.
In C1 type "Temp" as your column heading
Select C2:C719
Type
=if(isblank(b2),"A","B")
Then hold down Ctrl and press enter

You should now have:[tt]
Col1 Col2 Temp
BTC Y B
BTC A
BTC Y B
BTC Y B
BTC A[/tt]

Data,Subtotals:
At each change in temp
use function count
add subtotal to Col1
summary below data

The Grouping and Outlining symbols will appear to the left of your window. Click on the "2" and only the inserted rows containing subtotals will be displayed.

Select all these added cells eg:
Select cell A1, Shft-Ctrl-End
Edit,Goto,Special,Visible cells only

press the [delete] key to clear them.

click on the "3" in the group and outlining display and all rows will be displayed .

You have now achieved your objective of inserting blank rows. To clean up:
Delete column C
Data,Group&Outline,ClearOutline


Gavin

 

[SkipVought]

Hi,

Empty rows or columns in tables is a plague, if you understand how Excel tables work.

Is there a design requirement for these empty rows, or are these empty rows to provide visual emphasis?

Visual emphasis can and ought to be applied in a way that will not destroy the inherent functionality of tables.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]