Splitting Text String

[mouse88]

I am pulling data in from another spreadsheet which is call statistics for a call centre.

The call centre staff have an option to out their phone on "Not Ready" and when they do this they have to enter an activity code, 1, 2 or 3. When a report is produced for the phone data and I read it in to my sheet it comes formatted like this:

1. 00:00:00, 2. 00:00:00, 3. 00:00:00

I need to split this value in to three different colums so I only take the individual time values.

The problem is, If the employee doesnt use activity code 2 then the data may be as follows:

1. 00:00:00, 3. 00:00:00

Therefore I also need to do some sort of lookup before pulling the value out for 2 and if it doesn't exist default the column to 00:00:00.

Can anyone point me in the right direction?

 

[hipnotode]

you could use the following code =IF(A1="","00:00:00",A1)
presuming that A1 is where your looking.

 

[mouse88]

Can you explain this code as I dont quite understand?

I need a way of checking if there is a value of 1. 2. or 3. there and grab the relevant time from the string. I the user hasen't used one of the options then stupidly instead of still including it with a value of 00:00:00 it just doesnt show it at all.

Thanks

 

[mouse88]

Can anyone help with this?

 

[Scott Huish]

How many columns is your data in exactly?

How did the suggested formula not help?

That formula looks at the cell A1. If it is blank, it returns the string "00:00:00", otherwise returns the value of A1. You could instead return the value 0 and format as time, if the times are in their own cells.

 

[mouse88]

Hopefuly this image will help show you what I want:

The raw data comes from a report generated by a call centre system. The data comes back as a single string and could like like any of the above scenarios. Basically I need to test the string for each option, if it is there then grab the relevant time value and if it's not there default the cell to "00:00:00".

Hope this helps

 

[bertie]

Here is a formula solution:

Excel Workbook
	A	B	C	D
1	Input String	1	2	3
2	1. 02:50:02, 2. 05:04:36 3. 04:06:25	02:50:02	05:04:36	04:06:25
3	1. 02:50:02 2. 04:06:25	02:50:02	04:06:25	00:00:00
4	2. 05:04:36, 3. 04:06:25	00:00:00	05:04:36	04:06:25
Sheet1

 

[mouse88]

Works perfect.

Thanks

 

[Peter_SSs]

I understand that you have a solution but if ..
a) you have Excel 2007+ (your screen shot looks like that could be the case), and
b) you want a single formula that will work for all three columns,
.. then you could try this one, copied across and down.

Excel Workbook
	A	B	C	D
2	1. 02:50:02, 2. 05:04:36 3. 04:06:25	2:50:02	5:04:36	4:06:25
3	1. 02:50:02 2. 04:06:25	2:50:02	4:06:25	0:00:00
4	2. 05:04:36, 3. 04:06:25	0:00:00	5:04:36	4:06:25
5	1. 02:50:02 3. 04:06:25	2:50:02	0:00:00	4:06:25
Split out Times