removing blank cells

[Jambruins]

Column A looks like this (there are actually 162 cells, not just the
shown here)
@Sea
@Sea
@Sea
Bos
Bos
Bos
@Oak
@Oak
@Oak

I want to get all the cells with an @ symbol in them to be in column
without the blank cells in between and all the cells without an
symbol to be in column B. I have to do this for 30 sheets so I don'
want to have to cut and paste on all 30 sheets. Is there a way to d
this with a formula or formatting? Thanks

 

[Frank Kabel]

Hi
try the following:
1. In B1 enter the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX($A$1:$A$1000,SMALL(IF(LEFT($A$1:$A$1000,1)<>"@",ROW($A$1:$A$1000
),10000),ROW()))
2. in C1 enter the array formula:
=INDEX($A$1:$A$1000,SMALL(IF(LEFT($A$1:$A$1000,1)="@",ROW($A$1:$A$1000)
,10000),ROW()))
copy both formulas down (you'll get a #REF error then you reach the
last row for both columns)

Frank

 

[Jambruins]

I entered the formulas in but on cell C1 is showing what I want. Cell
B1 has a #REF!. The rest are showing #NUM!. What do you mean enter
with ctrl+shift+enter. I typed in the formula into cell B1 and the
other formula into C1 and then dragged them down. Where does the
ctrl+shift+enter come into play?

 

[Jambruins]

nevermind, I figured out the ctrl+shift+enter thing. Thank

 

[Jambruins]

actually, what if I want this all to happen starting in row 2 instead o
row 1? I thought I would just change the $A$1 to $A$2 but it doesn'
work out right then

 

[Frank Kabel]

Hi
try
=INDEX($A$2:$A$1000,SMALL(IF(LEFT($A$2:$A$1000,1)<>"@",ROW($A$2:$A$1000
),10000),ROW())-1)
adapt the othe formula accordingly
Frank

 

[Jambruins]

A B C
@SEA SEA @SEA
@SEA SEA @SEA
@SEA SEA @SEA
@TEX OAK @TEX
@TEX OAK @TEX
@TEX OAK @TEX
@TEX TEX TEX
SEA TEX @OAK
SEA TEX @OAK
SEA DET @OAK
OAK DET @DET
OAK DET @DET
OAK TB @DET
TEX TB @MIN
TEX TB @MIN
TEX TB TB

Column B works great but as you can see column C works great until ro
7 where it stops having the @ symbol. Any idea why

 

[Frank Kabel]

Hi
interesting. Formula should work. If you like, mail me your spreadsheet
and I'll look at it

Frank

 

[Jambruins]

what is your email

 

[Frank Kabel]

frank[dot]kabel[at]freenet[dot]de

Frank