Excel Dealing with Bible citation reference

Question

&nbsp;Having some difficulties in dealing with Bible Citation Reference (Book Chapter: Verses) in Excel;would appreciate help/comments in dealing with copying the data into Excel and thereafter.&nbsp;Col 1&nbsp; &nbsp; &nbsp;Col 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Col 3----------------------------------Acts&nbsp; &nbsp; &nbsp; 1:12-23Acts&nbsp; &nbsp; &nbsp; &nbsp;1:12-5:12Acts&nbsp; &nbsp; &nbsp; &nbsp; 1-3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Acts 1-3&nbsp;1. in Excel&nbsp; &nbsp;2 columns:&nbsp; column 1 Acts (book Name)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; column 2 contains the reference number&nbsp; A.&nbsp; How to get a clean worksheet with the cells presenting the info as is after pasting the data into Excel;&nbsp;&nbsp;&nbsp; &nbsp;Not hh:mm:ss etc&nbsp; &nbsp; &nbsp; (i tried various ways in formatting the cells; for Text; and&nbsp; Paste Special : Text,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Any other easier way in handling a bulk of 1500 rows?)&nbsp;2.&nbsp; CONCATENATE&nbsp; Col 1 &amp; Col 2 properly into Col 3 =&gt; Acts 1-3 etc; not something else?&nbsp; &nbsp; &nbsp;i am using&nbsp; &nbsp; &nbsp; &amp; Text(A3,"0")&nbsp; &nbsp; for 1-3,&nbsp; but got 43103 in the result .&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;but 21-20&nbsp; was Ok.&nbsp; &nbsp;&nbsp;&nbsp;[Some cells in the range have the green error note [e.g. 22; 25]&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; as number stored as text, they are OK]&nbsp;&nbsp;Why are the various results of pasting?&nbsp; &nbsp;&nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp;&nbsp;

sergeibaklan · Answer

Hi Li,
1) Select entire column 2, Ctrl+1, format as text, paste into it after that
2) Like
=A3 &amp; " " &amp; B3
if both columns formatted as text

li chen · Answer

Thx. It worked.&nbsp;

Forum Discussion

Excel Dealing with Bible citation reference

Share

Resources