I have an Excel file containing in 4 columns:
hours minutes seconds milliseconds
9 7 51 905
9 7 58 233
How can I put one row of this data into a single cell? My aim is to create a single cell containing hh_mm_ss.000 in order to make calculations.
No VBA, Excel functions preferred. Thanks.
The solution I found
1) concatenate data
=CONCATENATE(H2;":";I2;":";J2;".";K2)
2) convert to milliseconds
=(H2*3600+60*I2+J2)*1000+K2
3) computations
4) result back to seconds (=cell/1000) [format cell as general]
You can concatenate strings using the CONCATENATE() function in Excel:
=CONCATENATE(A1, "_", B1, "_", C1, ".", D1)
If you use a non-English version of Excel, use semicolons instead:
=VERKETTEN(A1; "_"; B1; "_"; C1; "."; D1)
As arguments you can either use static strings or reference cells.
Furthermore, you can provide as many arguments as desired, to concatenate them. You are not limited to two arguments.