sorting noncontiguous data with exceptions

[al drah]

I have imported vcf files into excel 2003 and the column looks like
this:
BEGIN:VCARD
VERSION:3.0
N: (some data)
FN: (some data)
MN: (some data)
X-VOIP-USERNAME: (some data)
X-VOIP-PSTNNUMBERsome data)
X-VOIP-DISPLAYNAMEsome data)
X-VOIP-SEXsome data)
X-VOIP-COUNTRYsome data)
BDAYsome data)
ADRsome data)
TEL;CELLsome data)
END:VCARD

BEGIN:VCARD
VERSION:3.0
N: (some data)
FN: (some data)
LN: (some data)
X-VOIP-USERNAME: (some data)
X-VOIP-PSTNNUMBERsome data)
X-VOIP-DISPLAYNAMEsome data)
X-VOIP-SEXsome data)
X-VOIP-COUNTRYsome data)
ADRsome data)
TEL;CELLsome data)
END:VCARD
Each person's data is bounded by the BEGIN:VCARD and the END:VCARD.
I want to turn this into a spreadsheet with all the categories (e.g.
FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation
won't work, as some the entries don't contain all the data (for
example, some have FN, MN,LN but others have only FN and LN) and if
transposed to columns, the info won't match up across the rows.
I've made some headway by highlighting a certain category (e.g. CITY
-- there were 37 out of 879 out of 879 begin--end groups, then
changing an all unneeded category like COUNTRY to CITY:00, then
manually deleting 37 extraneous CITY:00 from the files which
originally had had a CITY category. Time consuming to say the least,
and I've discovered there is a category I wish to keep, but it shows
up in 175 of the files --- there's got to a better way.
If anyone has any suggestions, I'd be very grateful.
Thank you

 

[plinius]

Il 21/11/2012 22:29, al drah ha scritto:

I have imported vcf files into excel 2003 and the column looks like
this:
BEGIN:VCARD
VERSION:3.0
N: (some data)
FN: (some data)
MN: (some data)
X-VOIP-USERNAME: (some data)
X-VOIP-PSTNNUMBERsome data)
X-VOIP-DISPLAYNAMEsome data)
X-VOIP-SEXsome data)
X-VOIP-COUNTRYsome data)
BDAYsome data)
ADRsome data)
TEL;CELLsome data)
END:VCARD

BEGIN:VCARD
VERSION:3.0
N: (some data)
FN: (some data)
LN: (some data)
X-VOIP-USERNAME: (some data)
X-VOIP-PSTNNUMBERsome data)
X-VOIP-DISPLAYNAMEsome data)
X-VOIP-SEXsome data)
X-VOIP-COUNTRYsome data)
ADRsome data)
TEL;CELLsome data)
END:VCARD
Each person's data is bounded by the BEGIN:VCARD and the END:VCARD.
I want to turn this into a spreadsheet with all the categories (e.g.
FN, LN, N, ADR, etc) as columns. A simple text-to-columns operation
won't work, as some the entries don't contain all the data (for
example, some have FN, MN,LN but others have only FN and LN) and if
transposed to columns, the info won't match up across the rows.
I've made some headway by highlighting a certain category (e.g. CITY
-- there were 37 out of 879 out of 879 begin--end groups, then
changing an all unneeded category like COUNTRY to CITY:00, then
manually deleting 37 extraneous CITY:00 from the files which
originally had had a CITY category. Time consuming to say the least,
and I've discovered there is a category I wish to keep, but it shows
up in 175 of the files --- there's got to a better way.
If anyone has any suggestions, I'd be very grateful.
Thank you

Your data in column A

F1 VERSION
G1 N
H1 FN
I1 LN
J1 MN
K1 X-VOIP-USERNAME
L1 X-VOIP-PSTNNUMBER
M1 X-VOIP-DISPLAYNAME
N1 X-VOIP-SEX
O1 X-VOIP-COUNTRY
P1 BDAY
Q1 ADR
R1 TEL;CELL

Put cells as follow:
B2 ="A"&D1+1&":A"&65000
C2 =MATCH("BEGIN:VCARD",INDIRECT($B2),)+D1
D2 =MATCH("END:VCARD",INDIRECT($B2),)+D1
E2 ="A"&C2&":A"&D2

F2
=IF(ISNUMBER(MATCH(F$1&"*",INDIRECT($E2),)),SUBSTITUTE(INDEX(INDIRECT($E2),MATCH(F$1&"*",INDIRECT($E2),)),F$1&":",""),"")

copy F2 in G2:R2
copy B2:R2 down as necessary.

Hi,
E.

 

[al drah]

Il 21/11/2012 22:29, al drah ha scritto:











Your data in column A

F1      VERSION
G1      N
H1      FN
I1      LN
J1      MN
K1      X-VOIP-USERNAME
L1      X-VOIP-PSTNNUMBER
M1      X-VOIP-DISPLAYNAME
N1      X-VOIP-SEX
O1      X-VOIP-COUNTRY
P1      BDAY
Q1      ADR
R1      TEL;CELL

Put cells as follow:
B2      ="A"&D1+1&":A"&65000
C2      =MATCH("BEGIN:VCARD",INDIRECT($B2),)+D1
D2      =MATCH("END:VCARD",INDIRECT($B2),)+D1
E2      ="A"&C2&":A"&D2

F2
=IF(ISNUMBER(MATCH(F$1&"*",INDIRECT($E2),)),SUBSTITUTE(INDEX(INDIRECT($E2),MATCH(F$1&"*",INDIRECT($E2),)),F$1&":",""),"")

copy F2 in G2:R2
copy B2:R2 down as necessary.

Hi,
E.

Signore Plinius ---
MOLTO GRAZIE! !!! That worked perfectly!
Thank you SO much for your quick and helpful reply. You have saved me
hours of work.
con molta gratitudine,
Al

 

[plinius]

Il 22/11/2012 05:45, al drah ha scritto:

Signore Plinius ---
MOLTO GRAZIE! !!! That worked perfectly!
Thank you SO much for your quick and helpful reply. You have saved me
hours of work.
con molta gratitudine,
Al

Felice di esserti stato di aiuto