Hi everyone ,

I have a raw data dump out of a system. The data is as below:

ZPID=JOY
ZPOINTTYP=1
ZPTSUBTYP=0
ZDESC=JOY 2
ZPLTSYS=###BLANKS###
ZSYN1=###BLANKS###
ZSYN2=###BLANKS###
ZSYN3=###BLANKS###
ZFREQ=B
ZPTBCL=NNNNNNNN
!
ZPID=JOY2
ZPOINTTYP=1
ZPTSUBTYP=0
ZDESC=JOY 2A
ZPLTSYS=###BLANKS###
ZSYN1=###BLANKS###
ZSYN2=###BLANKS###
ZSYN3=###BLANKS###
ZFREQ=B
ZPTBCL=NNNNNNNN
ZREMARKS1=###BLANKS###
ZREMARKS2=###BLANKS###
ZREMARKS3=###BLANKS###
ZREMARKS4=###BLANKS###
ZTECHSPEC=###BLANKS###
!
ZPID=JOY3
ZPOINTTYP=1
ZPTSUBTYP=0
ZDESC=JOY 3
ZPLTSYS=###BLANKS###
ZSYN1=###BLANKS###
ZSYN2=###BLANKS###
ZSYN3=###BLANKS###
ZFREQ=B
ZPTBCL=NNNNNNNN
ZREMARKS1=###BLANKS###
ZREMARKS2=###BLANKS###
ZREMARKS3=###BLANKS###
ZREMARKS4=###BLANKS###
ZTECHSPEC=###BLANKS###
ZAGROUP=###BLANKS###
ZALCTPT=###BLANKS###
!


Each record set is separated by the "!" character and the number of rows for each set is not the same.

I found a formula from this forum that shows how to do it if the number of rows for each set is fixed, below is 99 rows for each set
=IF(COLUMNS($A:A)>99,"",INDEX($A:$A,COLUMNS($A:A)+99*(ROWS($1:1)-1),1))

How do i modify this to transpose every time you see "!" character. Thanks for any help you can give,