Splitting cells based on content

rholdren

Board Regular
Joined
Aug 25, 2016
Messages
140
Office Version
  1. 365
  2. 2019
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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top