none
Formula for Custom field RRS feed

  • Question

  • 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

    Wednesday, June 6, 2012 5:10 PM

Answers

  • 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...

    Monday, June 11, 2012 3:22 PM

All replies

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

    Wednesday, June 6, 2012 8:33 PM
    Moderator
  • 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"

    Thursday, June 7, 2012 12:45 AM
    Moderator
  • HI Glen,

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


    kirtesh

    Monday, June 11, 2012 10:55 AM
  • 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, June 11, 2012 11:01 AM
  • Hi,

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

    Greetings,

    Monday, June 11, 2012 2:10 PM
    Moderator
  • 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...

    Monday, June 11, 2012 3:22 PM