Ask Your Question
0

Solved: Calc: Concatenate does not carry over leading zero

asked 2016-04-23 03:31:49 +0200

Crash16 gravatar image

updated 2016-04-23 06:51:17 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-04-23 06:31:42 +0200

Crash16 gravatar image

updated 2016-04-23 06:51:59 +0200

I think I have solved this, tested on 5.1.2.2 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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-04-23 03:31:49 +0200

Seen: 504 times

Last updated: Apr 23 '16