Sorting lines in a cell into columns of data

[frustin]

My cells are vcards which i'm trying to split out into separated columns (transpose): Name, Organisation, Title, Telephone, Address, Email, Notes

example of cell A1 content (A2, A3, An+1 have the same data types):

BEGIN:VCARD
N:Joe Bloggs
ORG:Microstaff
TITLE:IT executive
TEL:0234522555
ADR:;;48 rue de Tomato;75032;;Paris;France
EMAIL:joe.bloggs@microstaff.com
NOTE:Contact from Cool Event London 2014
END:VCARD

As you can see the delimiters are a bit odd (especially the address field), i've imported this from an iPhone app called QRReader, but it didnt port to CSV very well.

I've tried all sorts of ways such as: =IF(SEARCH("N:",A2),SUBSTITUTE(A2,"N:",""),"") for the name field and so on but this does not work very well because it pulls in all the stuff after "bloggs".

Any help appreciated thanks.

 

[mfexcel]

Do you mean you have all those information in one cell?

 

[frustin]

No, all the info you see there is sitting in one cell at the moment. I'd like to transpose the cells out into separate columns with the headers: Name, Organisation, Title, Telephone, Address, Email, Notes.

 

[frustin]

Not really a fix but this is what i did in the end:

find/replace "BEGIN:VCARD" and "END:VCARD" with ;
find/replace everything that ends with a : with a ; e.g. N: and TITLE:
=SUBSTITUTE(A1, Char(13), "") this removes all the line breaks/returns (i'm using Mac Excel 2011. In Windows i'd have used find/replace ctrl-J).
Now everything is semi-colon delimited. I then used text-to-column to put everything in columns.