For a table like:

ATOM 11 C ASN 2 35.233 -19.675 10.91 1 21.57 1L25 247
ATOM 12 O ASN 2 36.364 -19.365 10.571 1 14.91 1L25 248
ATOM 13 CB ASN 2 34.556 -21.688 12.275 1 15.39 1L25 249
ATOM 14 CG ASN 2 35.728 -21.575 13.192 1 20.37 1L25 250
ATOM 15 OD1 ASN 2 36.27 -20.496 13.363 1 22.53 1L25 251
ATOM 16 ND2 ASN 2 36.076 -22.668 13.856 1 18.63 1L25 252
ATOM 17 N ILE 3 34.359 -18.814 11.359 1 19.04 1L25 253
ATOM 18 CA ILE 3 34.608 -17.394 11.367 1 16.26 1L25 254
ATOM 19 C ILE 3 35.827 -16.967 12.139 1 21.3 1L25 255
ATOM 20 O ILE 3 36.582 -16.036 11.741 1 17.38 1L25 256
ATOM 21 CB ILE 3 33.34 -16.614 11.714 1 15.86 1L25 257
ATOM 22 CG1 ILE 3 33.51 -15.116 11.408 1 13.4 1L25 258
ATOM 23 CG2 ILE 3 32.909 -16.932 13.181 1 11.39 1L25 259
ATOM 24 CD1 ILE 3 33.843 -14.688 9.937 1 8.45 1L25 260
ATOM 25 N PHE 4 36.028 -17.586 13.265 1 13.57 1L25 261
ATOM 26 CA PHE 4 37.184 -17.252 14.047 1 8.75 1L25 262


i would like to extract rows with the third column with specific string,
e.g. "CA"
I write something like =VLOOKUP("CA",$C$1:$C$1536,8, FALSE) and
=IF(FIND("CA",C237),J237,N237)

the latter one leaves me space which i don't prefer. Is there any way to
extract the rows and stack them one by one obeying their original order?

At P1 and P2 respectively:
=MATCH("CA",C:C,0)
=MATCH("CA",INDEX(C:C,P1+1):INDEX(C:C,1536),0)+P1
Copy down P2 until you get #N/A
Enter at Q1 and copy down:
=INDEX(J:J,P1)