Splitting columns in Excel

  johnincrete 07:41 16 Apr 2010
Locked

I have downloaded text that comprises a series of phrases followed by a variable number of references. The phrases are, actually, names given to Jesus in the Old Testament and the references are the Book and verse. The delimiter of the set is a full stop. So we have:
Name,Ref1;Ref2 and so on ending with Refn. The reference is abreviated book name (2, 3 or 4 Characters) followed by a space then the chapter number (one or two digits) a colon and then the verse list that might be a one or two digit number followed by a comma and then another two digit number.
For example:
Angel, Gen 48:16; Ex 23:20,21.
Anointed, Psa 2:2.
Bright and morning star, Rev 22:16.
I eliminated the full stop that ended an abreviation i.e. Gen. to Gen space) and converted text to single column table using the full stop as delimiter. What I want to do is have the first column the book and each reference in its own column eg:
Angel ! Gen 48:16 ! Ex 23:20,21
Anointed ! Psa 2:2 !
I don't see how to do it in Word so I put it into a single column Excel 2003 spreadsheet. What I need is a formula to split the column.
Maybe it has to be in two stages: split into name & references using the comma and then split the references using the semi-colon.
My ideal would be to have just two columns: name and a single reference: thus
Angel ! Gen 48:16
Angel ! Ex 23:20,21
but that seems to be asking a bit too much.

  eedcam 08:28 16 Apr 2010

Have you looked here
click here

  VoG II 08:36 16 Apr 2010

Data > Text to Columns might work, using the Delimited option.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone