Extracting Numbers out of a String from MS Project

[Eric1414]

Hello All,
I’ve been trying to collect some data from a consolidated MS Project file. When I export to Excel all of the durations are in the format (4.5 days, 6 days, 18 days, etc.). In order to perform statistical analysis in Excel I need just the numbers. I’ve tried looking into MID and Trunc functions but they seem to only work with numbers at the end of a string or if the number always has the same number of digits, which in this case they don’t.

Since I can’t find a way in MS Project to export the duration as just numbers my only option is to find a way to do it in Excel after the information has already been exported.

Does anyone have any tips for Excel or even Project?

 

[BenMiller]

Is each value in a separate cell?

If yes, either do a find/replace on " days" (without the quotes) and replace it with nothing.

Or for a formula, try

=--LEFT(A1,find(" ",A1)-1)

 

[Eric1414]

The formula worked perfectly! Thank you so much Ben for your quick reply!

 

[BenMiller]

No problem Glad to help Appreciate the feedback!

 

[Eric1414]

Hello Ben,

I realized I have a new problem. I just realized that not all of my data is in the same units, i.e. days. Since the data is in days, weeks, hrs, etc. removing just the text and leaving the numbers leaves me with incorrect data since 4 hrs is not equivalent to 4 days. Is there a formula that can be written for excel to recognize a specific text string such as "hrs" and then I could convert it to days (i.e. .5 days)?

 

[BenMiller]

Can you post a sample of your data?

 

[Eric1414]

Here is a sample of my data
.
[TABLE="width: 71"]
<TBODY>[TR]
[TD]1 hr</SPAN>[/TD]
[/TR]
[TR]
[TD]1 min</SPAN>[/TD]
[/TR]
[TR]
[TD]0 days</SPAN>[/TD]
[/TR]
[TR]
[TD]0.13 days</SPAN>[/TD]
[/TR]
[TR]
[TD]30 mins</SPAN>[/TD]
[/TR]
[TR]
[TD]30 mins</SPAN>[/TD]
[/TR]
[TR]
[TD]0 days</SPAN>[/TD]
[/TR]
[TR]
[TD]0.5 days</SPAN>[/TD]
[/TR]
[TR]
[TD]2 hrs</SPAN>[/TD]
[/TR]
[TR]
[TD]1 hr</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[BenMiller]

This will convert hours, minutes, and days to days.

=LEFT(A1,FIND(" ",A1)-1)/LOOKUP(MID(A1,FIND(" ",A1)+1,1),{"d";"h";"m"},{1;24;1440})&" days"

 

[Eric1414]

I changed the conversions to {1;8;480} for working time and I removed the & " days" at the end so that I can copy and paste the data into Minitab for analysis. It works great! Thanks again Ben.

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!