Text to Column formula

[aggiemarine07]

Howdy!

I just want to put out my supreme appreciation for everyone on this message board, yall have be invaluable to me as I work on a project assigned to me at work. Here goes my problem:

We use a company wide system that tracks a lot of training statistics for each individual person (name, education level, run times, etc) and it easily keeps all of this information in one place and I'm able to export and get statistical analysis from much of it. However, the system formats run times in a text format (02:48) rather than a typical Excel number format (2:48:00). Im trying to extract this information in a way that Excel can read and get average run times for these individuals but because its formatted in that way, I cant get a average.

Below is a an example and what my intended result is:

Row1 02:45
Row2 02:50
Row3 02:55

Get an average of 2:50

Based on my google searches, I think the best method is to extract this text into two separate columns and then figure out a way to get the average run time from there. But I am open to other options, even VBA if necessary.

I found this similar formula below but it only works with word text (to my limited knowledge) and mine needs to work with numbers
http://www.mrexcel.com/forum/excel-questions/587534-text-columns-via-formula.html

Any help would be greatly appreciated! Thank you for your time

 

[bschwartz]

Is this what you are looking for?

Excel 2010
	A	B	C
1	02:45	Average Hours	2.83
2	02:50	Average Time	2:50:00
3	02:55
Sheet76

Cell Formulas
Range	Formula
C1	{=AVERAGE(VALUE(A1:A3))*24}
C2	{=AVERAGE(VALUE(A1:A3))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[bschwartz]

Or non-array formula:

=SUMPRODUCT(VALUE(A1:A3))/COUNTA(A1:A3)

 

[aggiemarine07]

Thank you sir, that has solved my problem.

Is this what you are looking for?

Excel 2010

	A	B	C
02:45	Average Hours
02:50	Average Time
02:55

<colgroup></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]2.83[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2:50:00[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet76​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C1[/TH]
[TD="align: left"]{=AVERAGE(VALUE(A1:A3))*24}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]{=AVERAGE(VALUE(A1:A3))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[bschwartz]

You are very welcome.