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

Galaxy Note 8 vs iPhone X

Awful clip art from 1994 is being tweeted every hour by a bot

iPhone X vs Samsung Galaxy Note 8

Les meilleurs navigateurs internet 2017