Extracting Numbers out of a String from MS Project

Eric1414

New Member
Joined
Jul 14, 2008
Messages
7
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?
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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)
 
Upvote 0
No problem :) Glad to help :beerchug: Appreciate the feedback!
 
Upvote 0
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)?
 
Upvote 0
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]
 
Upvote 0
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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,919
Latest member
krismmmitch

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