Splitting cells based on content

[rholdren]

Hi everyone hope it's an excellent day.

I did find something similar to what I was looking for on the site but don't quite understand what they were doing.
https://www.mrexcel.com/forum/excel...t.html?highlight=splitting+cell+based+content

This is my problem. I get a usage report from a vendor and in column C is the time a user spent logged on. The problem is sometimes
Column D has 1 hr 12 min 23 sec and sometimes it has 14 minutes and sometimes it's just 50 sec. The times vary. When the file is downloaded as a SCV the format of the column is general. I have tried converting text to columns but that sometimes puts hrs min and sec in the same column so if I tried to add the time up for each department it would make 1 hr 1 min 1 sec look like 3 hours. Here is how it looks

A B C D
User Name User Full Name Usage Dept
JoeScnmoe@123.com Joe Schmoe 1hr 15 min 32 sec ACCT
JillSchmoe@123.com Jill Schmoe 38 min FIN
BillieBobb@123.com Billie Bob 55 sec ACCT

If I convert text to columns I would have 94 hrs 15 min 32 sec So far I have tried a combination of text to columns before and after using find and replace with replacing the hr min sec with : I've got roughly 600 users in this system and I have to calculate each departments usage from a variety of internal and external users. Also formatting seems to be an issue. If I format the cells with hh:mm:ss it won't show anything larger than 24 hours correctly and if I format with d:hh:mm:ss it would show 50 hours as 2 days but will not show the rest of the time correctly. I think if the cells could be split correctly the rest of the issues would fall into place. Any help would be greatly appreciated.

Thanks.

 

[jtakw]

Hi,

I'm not sure exactly what you're asking, but if it's a matter of formatting the result cell to show hours, minutes, seconds, then use [h]:mm:ss
It will show hours greater than 24.

 

[rholdren]

In the first example the time is all in one column but if you split it out (convert text to columns) minutes and seconds will line up under hours

Username	User's Name	Usage	Dept
JoeScnmoe@123.com	Joe Schmoe	1hr 15 min 32 sec	ACCT
JillSchmoe@123.com	Jill Schmoe	38 min	FIN
BillieBobb@123.com	Billie Bob	55 sec	ACCT

<tbody>

</tbody>

Username	User's Name	Usage							Dept
JoeScnmoe@123.com	Joe Schmoe	1hr 15 min 32 sec	1	hr	15	min	32	sec	ACCT
JillSchmoe@123.com	Jill Schmoe	38 min	38	min					FIN
BillieBobb@123.com	Billie Bob	55 sec	55	sec					ACCT

<tbody>

</tbody>

 

[iggy_]

You can try this type of a formula even if your data is not split into columns. Result is just the number of hours. And cut it down further if your have time in one column.

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	User Name	User Full Name	Result
2​	JoeScnmoe@123.com	Joe Schmoe 1hr 15 min 32 sec ACCT	1.258889​
3​	JillSchmoe@123.com	Jill Schmoe 38 min FIN	0.633333​
4​	BillieBobb@123.com	Billie Bob 55 sec ACCT	0.015278​

Sheet: Sheet3

Excel 2016 (Windows) 32 bit

	C​
2​	=IFERROR(MID(B2,FIND("hr",B2,1)-2,2),0)+IFERROR(MID(B2,FIND("min",B2,1)-3,2),0)/60+IFERROR(MID(B2,FIND("sec",B2,1)-3,2),0)/3600​

Sheet: Sheet3

 

[rholdren]

I will give that a try. Thanks

 

[rholdren]

That worked well. How would you adjust it if sometimes there is say 1 hr but sometimes in the range it might be double digits like 11 hrs. By the way I really appreciated this. I would have never thought of that. I was trying everything from using find and replace then converting text to column to lookups and finds and I just reached an end. Thank you

 

[jtakw]

Hi,

The division numbers in the formula in Post #4 is incorrect, and will not give you correct results.

Below is corrected version, and also will accommodate Single and/or Double digit Hours, Minutes, Seconds:

Book1
	A	B	C	D	E
1	Username	User's Name	Usage	Dept	formatted hh:mm:ss
2	JoeScnmoe@123.com	Joe Schmoe	1hr 15 min 32 sec	ACCT	01:15:32
3	JillSchmoe@123.com	Jill Schmoe	38 min	FIN	00:38:00
4	BillieBobb@123.com	Billie Bob	55 sec	ACCT	00:00:55
5	JoeScnmoe@123.com	Joe Schmoe	11hr 5 min 2 sec	ACCT	11:05:02
6	JillSchmoe@123.com	Jill Schmoe	8 min	FIN	00:08:00
7	BillieBobb@123.com	Billie Bob	5 sec	ACCT	00:00:05
8	JillSchmoe@123.com	Jill Schmoe	38 min 3 sec	ACCT	00:38:03
9	BillieBobb@123.com	Billie Bob	1hr 55 sec	FIN	01:00:55
10	JoeScnmoe@123.com	Joe Schmoe	1hr 15 min	ACCT	01:15:00
Sheet35

Cell Formulas
Range	Formula
E2	=IFERROR(MID(0&C2,SEARCH("hr",0&C2,1)-2,2)/24,0)+IFERROR(MID(0&C2,SEARCH("min",0&C2,1)-3,2),0)/1440+IFERROR(MID(0&C2,SEARCH("sec",0&C2,1)-3,2),0)/86400

E2 formula copied down.

 

[rholdren]

it works really well but it only shows this 5:15:32
with this 15 hr 15 min 32 sec
Single digits and the format come out perfect - I really appreciate the help.
(you guys are amazing) I learn more and more on this BB. One of these days I hope to be able to answer questions to help others.

 

[jtakw]

You have a space there in front of "hr", this should fix that problem:

Book1
	A	B	C	D	E
1	Username	User's Name	Usage	Dept	formatted hh:mm:ss
2	JoeScnmoe@123.com	Joe Schmoe	1hr 15 min 32 sec	ACCT	01:15:32
3	JillSchmoe@123.com	Jill Schmoe	38 min	FIN	00:38:00
4	BillieBobb@123.com	Billie Bob	55 sec	ACCT	00:00:55
5	JoeScnmoe@123.com	Joe Schmoe	11hr 5 min 2 sec	ACCT	11:05:02
6	JillSchmoe@123.com	Jill Schmoe	8 min	FIN	00:08:00
7	BillieBobb@123.com	Billie Bob	5 sec	ACCT	00:00:05
8	JillSchmoe@123.com	Jill Schmoe	38 min 3 sec	ACCT	00:38:03
9	BillieBobb@123.com	Billie Bob	1hr 55 sec	FIN	01:00:55
10	JoeScnmoe@123.com	Joe Schmoe	1hr 15 min	ACCT	01:15:00
11			15 hr 15 min 32 sec		15:15:32
Sheet35

Cell Formulas
Range	Formula
E2	=IFERROR(MID(0&C2,SEARCH("hr",0&SUBSTITUTE(C2," ",""))-2,2)/24,0)+IFERROR(MID(0&C2,SEARCH("min",0&C2)-3,2),0)/1440+IFERROR(MID(0&C2,SEARCH("sec",0&C2)-3,2),0)/86400

Edit: Removed "start number" (1) in formula, not needed, makes for slightly shorter formula.

 

[rholdren]

That did the job. Thanks Greatly appreciated.