How to combine multiple cells to create one cell with Excel

Question

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]

How about using ``CONCATENATE(a, b)``? – user1438038 Oct 27 '17 at 11:21 — user1438038, Oct 27 '17 at 11:21

user1438038 · Accepted Answer · 2017-10-27T11:34:36.603

1

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.

edited Oct 27 '17 at 11:34

answered Oct 27 '17 at 11:24

user1438038

5,821
6
60
94

1

Quick note: This may be for non-English versions of Excel. Semicolons should be replaced with commas, and last semicolon after end of formula is not required. – ImaginaryHuman072889 Oct 27 '17 at 11:27
Thanks that helps a lot, however I'm not able to make calculation with such results :( – Luke Oct 27 '17 at 11:28
If I try to compute the difference between two values obtained with the concatenate command I get and error (#VALUE!). I have formatted the cell as time. – Luke Oct 27 '17 at 11:29
@Luke He provided an answer in the format exactly as you described in your question. – ImaginaryHuman072889 Oct 27 '17 at 11:29
Actually there is written that I need to make calculations @ImaginaryHuman072889 – Luke Oct 27 '17 at 11:30
By the way my Excel is an english version, but thanks for the info @ImaginaryHuman072889 – Luke Oct 27 '17 at 11:31
@ImaginaryHuman072889: Thanks, I was not aware of the fact, that the English version of Excel uses commas instead. I edited my answer accordingly. – user1438038 Oct 27 '17 at 11:35
@user1438038 No problem. Otherwise, I agreed with your answer and I gave you an upvote. – ImaginaryHuman072889 Oct 27 '17 at 11:36
Nono you misunderstood .. Excel in english DOES ACCEPT semicolons! You were right from the beginning! – Luke Oct 27 '17 at 11:37
@Luke If you type `CONCATENATE(A1; "_"; B1; "_"; C1; "."; D1)` into English Excel, you will get an error. – ImaginaryHuman072889 Oct 27 '17 at 11:38
@ImaginaryHuman072889 The solution I found is: =CONCATENATE(A1; ":"; B2; ":"; C2; "."; D2) – Luke Oct 27 '17 at 11:43

score 0 · Answer 2 · answered Oct 27 '17 at 11:32

If calculations such as adding/subtracting times are required, probably easiest solution would be to convert all times to seconds and add them up.

For example,

= 3600*A1+60*B1+C1+D1/1000

I would say this is required to do manually because Excel's built in time system only has a resolution down to the second (not millisecond).

How to combine multiple cells to create one cell with Excel

2 Answers2