Solved: Calc: Concatenate does not carry over leading zero

I am combining data from three fields into one. The first column is “Hour”, the Second “Minute”, and the third “AM/PM”. Data in the first two columns is numeric, and for the third column it is either blank, A, or P (text). In the minutes column, 00 is represented as 0, and 05 is 5, and so on. I revised the format for this column so it would have leading zeros, so became 00. When I use concatenate (along with a : between them) the leading zeros disappear, so for example 8:00 is displayed as 8:0. I’ve read I can format the minutes as text, however won’t they need to be recognized as numbers for when I convert the time to 24 hour format? The end result of these cells is that I will have them not as 8:00 PM but rather 20:00.

I think I have solved this, tested on and it worked. Process:

  1. When opening the csv file, in the drop-down box that is above the preview labeled “Fields Column Type”, select text for the hour and then the minute column.
  2. File opens, create new column to the right of AM/PM, use concatenate to combine Hour-Min-AM/PM into HH:MM PM.
  3. Drag formula for all cells in new column.
  4. Copy results.
  5. Right-click in highlighted column and paste-special results making sure “Formulas” is unchecked in the pop up that appears. Results are now text flagged by an apostrophe.
  6. Highlight results. Use find .* and replace with & making sure current selection and regular expressions are checked.
  7. Format cells as HH:MM.

Forcing the cells to be Text mode doesn’t work if the values are the result of a calculation. In those cases, use =TEXT(A1, "00") to format an arbitrary number with leading zeros.