I needed to import beaucoupBible verses for a lesson. Once I got done concatenating the series, Calc decided every chapter and verse citation was time format. By that, 1:35 became 01:35:00 AM. Itried creating a custom format - #:## but returned just the colon.
What am I missing?
Interesting problem.
…
For next time: Hindsight as 20/20 you should have pasted using Ctrl+Shift+v then selected Use text import dialog.
…
For this time: create a column just after the messed up column that should be citations. Enter this formula in the first cell adjusting the E14 reference to the cell just left of it, the bad citation. Once that looks okay, pull down to fill this formula down the column, to have your new column de-convert from time back to chapter:verse.
=IFERROR(NUMBERVALUE(LEFT(TEXT(E14,"H:M"),FIND(":",TEXT(E14,"H:M")) -1))+24*TRUNC(DAYS(E14,0),0)&":"&RIGHT(TEXT(E14,"H:M"),LEN(TEXT(E14,"H:M"))-FIND(":",TEXT(E14,"H:M"))),E14)
Chapter Verse Corrector.ods (20.8 KB)