Transpose from rows to column for everytime the "!" is encountered

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,

The max Zcodes are 99.

Here are they:

ZPID=A-E_DEMIN_JEANS
ZPOINTTYP=AI
ZPTSUBTYP=POLY
ZDESC=A THRU E DEMIN JEANS
ZPLTSYS=###BLANKS###
ZSYN1=###BLANKS###
ZSYN2=###BLANKS###
ZSYN3=###BLANKS###
ZFREQ=B
ZPTBCL=NNNNNNNN
ZPTPCL=YYYNNNNN
ZREMARKS1=NOT USED
ZREMARKS2=###BLANKS###
ZREMARKS3=###BLANKS###
ZREMARKS4=###BLANKS###
ZTECHSPEC=###BLANKS###
IDBSECUR=15
IADCAMP=1
IPOLYDG=1
IG2COMP=0
ZOTD=N
ZREFJTP=###BLANKS###
ZSNELUN=V
XCOEFF00=-24.66091
XCOEFF01=24.66091
XSNELLO=1.000000E+00
XSNELHI=5.120000E+00
XRELMLO=1.000000E+00
XRELMHI=5.055000E+00
ZMUXCRD=###BLANKS###
ZLALARM01=NA
ZLALARM02=NA
ZLALARM03=NA
ZLALARM04=NA
ZLALARM05=NA
ZLALARM06=NA
ZLALARM07=NA
ZLALARM08=NA
ZLWARN01=NA
ZLWARN02=NA
ZLWARN03=NA
ZLWARN04=NA
ZLWARN05=NA
ZLWARN06=NA
ZLWARN07=NA
ZLWARN08=NA
ZUALARM01=NA
ZUALARM02=NA
ZUALARM03=NA
ZUALARM04=NA
ZUALARM05=NA
ZUALARM06=NA
ZUALARM07=NA
ZUALARM08=NA
ZUWARN01=NA
ZUWARN02=NA
ZUWARN03=NA
ZUWARN04=NA
ZUWARN05=NA
ZUWARN06=NA
ZUWARN07=NA
ZUWARN08=NA
XCOMPRESS=1.000000E-01
XCLAMP=-1.000000E+08
XALDBND=0.000000E+00
XREALARM=0.000000E+00
ZENGRUN=PPB
IDSFRAC=3
XENRNLO=0.000000E+00
XENRNHI=1.000000E+02
ZHWDCHN=1A360305
IALDESTIN=0
IALMRESP=0
ZAGROUP=###BLANKS###
ZALCTPT=###BLANKS###
ZALMPRI=0
ZSPALPR=###BLANKS###
ITRAPNT=0
ZINPNT01=###BLANKS###
ZINPNT02=###BLANKS###
ZINPNT03=###BLANKS###
ZINPNT04=###BLANKS###
ZINPNT05=###BLANKS###
ZINPNT06=###BLANKS###
ZINPNT07=###BLANKS###
ZINPNT08=###BLANKS###
ZINPNT09=###BLANKS###
ZINPNT10=###BLANKS###
ZINPNT11=###BLANKS###
ZINPNT12=###BLANKS###
ZINPNT13=###BLANKS###
ZINPNT14=###BLANKS###
ZINPNT15=###BLANKS###
ZINPNT16=###BLANKS###
ZINPNT17=###BLANKS###
ZINPNT18=###BLANKS###
ZINPNT19=###BLANKS###
ZINPNT20=###BLANKS###
!

how do i code what you are proposing here

This looks awesome!
thank you so very much, you are a genius!