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 5.1.2.2 and it worked. Process:
- 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.
- File opens, create new column to the right of AM/PM, use concatenate to combine Hour-Min-AM/PM into HH:MM PM.
- Drag formula for all cells in new column.
- Copy results.
- 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.
- Highlight results. Use find .* and replace with & making sure current selection and regular expressions are checked.
- 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.