Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft >
> 
> 
> 
Formula for Custom field

Answered Formula for Custom field

  • Wednesday, 6 June 2012 5:10 PM
     
     
    Sign In to Vote
    0

    I have EPM 2010 environment .

    I want to create formula based enterprise Custom field "first name" and "last name" in which i want data from resource name field .

    i.e.

    Resource name                  First Name               Last Name

    Kirtesh Tiwari                     Kirtesh                     Tiwari

    How it is possible please suggest.


    kirtesh

     

All Replies

  • Wednesday, 6 June 2012 8:33 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi kirtesh,

    This doesn't sound like a Microsoft Project problem - if it is, please tell us the version you're using.  Otherwise you'll have to search for a forum for the program you're using.

    Mike Glen
    MS Project MVP (97-11)

     
  • Thursday, 7 June 2012 12:45 AM
    Moderator
     
     Proposed Answer
    Sign In to Vote
    0
    kirtesh --
     
    If you are trying to write a formula to break the names found in the Resource Name field into two other fields, named First Name and Last name, you are in for a rough ride, my friend.  There is no simple way to do this.  Many years ago I attempted to write a macro in the VBA programming language to do something similar to what you want.  What I ran into is that not everyone�??s name is simply two words.  For example, what would you do with the following names?
    • Abdul Akbar Haqq
    • Simon bar Joseph
    • Ramona Sue Clute
    • Leonardo da Vinci
    • Chuck de Graff
    • Kay Sue Summ
    • Oscar de la Hoya
    • Dennis di Concini
    • Sharon St. Louis
    • Megan Van Den Bosch
    Where would you break the names above into first names and last names?  And how would you identify where the first name ends and the last name begins?  It�??s not as easy as you think.  So, even writing a macro instead of using a formula would be very very difficult.  Hope this helps.
     

    Dale A. Howard [MVP]
    VP of Educational Services
    msProjectExperts
    http://www.msprojectexperts.com
    http://www.projectserverexperts.com
    "We write the books on Project Server"

     
  • Monday, 11 June 2012 10:55 AM
     
     
    Sign In to Vote
    0

    HI Glen,

    I am Using Project Server 2010 and Microsoft Project professional 2010 and i believe this is the correct forum.

    kirtesh

     
  • Monday, 11 June 2012 11:01 AM
     
     
    Sign In to Vote
    0

    Hi Dale,

    Thanks for your response.

    I am think i can take Space character as an index. In my system If name is "Simon bar Joseph " we write it "Simon Barjoseph". I think We can take Simon as last name and Barjoseph as First name. It means we need to calculate the position of the "Space" Dynamically and then break it in First name and Last Name.

    kirtesh

     
  • Monday, 11 June 2012 2:10 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi,

    Instr(" ") gives the position of the blank.

    Greetings,

     
  • Monday, 11 June 2012 3:22 PM
     
     Answered
    Sign In to Vote
    0

    Hello Kirteshtiw,

    • the formula to find the position of the first "space" is: InStr( <string>, " ")
    • To get a substring from a string you can use: Mid( <string>, <beginning position>, <length>)

    Consequently you can get the first name (from the beginning until the first space) via: Mid([Resource Name], 1, InStr([Resource Name], " ")-1)

    and the last name (from the first space on til the end): Mid([Resource Name], InStr([Resource Name], " ")+1)

    I havent tested the formulas above so they may contain one or two syntax errors...