Results 1 to 10 of 10

Thread: Extracting Numbers out of a String from MS Project
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extracting Numbers out of a String from MS Project

    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?

  2. #2
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    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)
    "I'm not a perfectionist; I'm just perfect."

  3. #3
    New Member
    Join Date
    Jul 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

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

  4. #4
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    No problem Glad to help Appreciate the feedback!
    "I'm not a perfectionist; I'm just perfect."

  5. #5
    New Member
    Join Date
    Jul 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    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)?

  6. #6
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    Can you post a sample of your data?

  7. #7
    New Member
    Join Date
    Jul 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    Here is a sample of my data
    .
    1 hr
    1 min
    0 days
    0.13 days
    30 mins
    30 mins
    0 days
    0.5 days
    2 hrs
    1 hr

  8. #8
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    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"
    "I'm not a perfectionist; I'm just perfect."

  9. #9
    New Member
    Join Date
    Jul 2008
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    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.

  10. #10
    Board Regular
    The ONLY cool kid on the block
    BenMiller's Avatar
    Join Date
    Nov 2011
    Location
    NJ
    Posts
    1,916
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting Numbers out of a String from MS Project

    No problem Glad I could help Appreciate the feedback!
    "I'm not a perfectionist; I'm just perfect."

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •