Concatenate

[lehy2002]

I have the following data and want to make it 20:05 (20 hours and 5 minutes) However, there are some cells that are double digits such as 58 minutes. How do I create a calculation to combine the two cells and if one is a single digit add the leading 0 and if not, do not add it. In the cases below, I would want 20:05, 18:04, 14:58.

20 hours, 5 minutes

18 hours, 4 minutes

14 hours, 58 minutes

 

[Rick Rothstein]

Your post is not very clear about your setup. Using the first example, it sounds like you have "20 hours" (as text) in one cell and "5 minutes" (as text) in the other cell. If that is the case, and if those two cells are A1 and B1, then...

=LEFT(A1,FIND(" ",A1)-1)&TEXT(LEFT(B1,FIND(" ",B1)-1),"\:00")

The above is a text value. If you wanted it to be a real time value, then use this formula...

=0+(LEFT(A1,FIND(" ",A1)-1)&TEXT(LEFT(B1,FIND(" ",B1)-1),"\:00"))

and Custom Format the cell using this Type pattern... [h]:mm

 

[kweaver]

Not elegant, but here's one way:

=RIGHT("0"&LEFT(A2,FIND(" ",A2)-1),2)&":"&RIGHT("0"&TRIM(MID(A2,FIND(",",A2)+2,2)),2)

assuming your data starts in A2, then fill this down.

 

[JoeMo]

Another possibility. Format Col B as hh:mm.

Book1
	A	B
1	20 hours, 5 minutes	20:05
2	18 hours, 4 minutes	18:04
3	14 hours, 58 minutes	14:58
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"minutes",""),"hours","")," , ",":"))

 

[Rick Rothstein]

Another possibility. Format Col B as hh:mm.

Book1
	A	B
1	20 hours, 5 minutes	20:05
2	18 hours, 4 minutes	18:04
3	14 hours, 58 minutes	14:58
Sheet1

Cell Formulas
Range	Formula
B1:B3	B1	=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"minutes",""),"hours","")," , ",":"))

Hopefully, if the value was 1 that the plural of hour or minute was still used.?

 

[JoeMo]

Hopefully, if the value was 1 that the plural of hour or minute was still used.?

Hopefully, but one can never count on consistency, so thanks for the heads up on this one!