A SharePoint list contatins three columns,

  • Requestor name (Single Line of Text)
  • First name (Calculated column)
  • Last Name (Calculated column)

I want to extract First Name and Last Name from the Reequestor Name. The Requestor Name might be in either of the below formats

Sachin Tendulkar or Sachin R Tendulkar

   Sachin  | Tendulkar
First Name | Last Name

   Sachin  |      R      | Tendulkar
First Name | Middle Name | Last Name

In the First name column, am able to get the required result using below formula,

(LEFT( [Requester Name],INT(FIND(" ",[Requester Name])-1))

For Last name, I am running into issue here because of the inconsistency in the middle initial. My formula is

RIGHT([Calc Column Last Name],LEN([Calc Column Last Name])-INT(FIND(" ",[Calc Column Last Name])))

Doesnt like the fact that if there is a middle initial. If there is a middle initial, I get a #VALUE! error.

How can I just extract the last name?

Thank you!

  • How does the name field get populated, does it auto fill from User Profile or do users actually enter in their name? Jun 27, 2016 at 19:14
  • Your second formula is reading from the calculated colum? Jun 28, 2016 at 10:16
  • @Ramana - Its auto populated from AD.
    – Shaji
    Jun 29, 2016 at 17:07
  • @Amal - yes. I have a typo in my question. The issue is when there is a middle name, I get #VALUE! error.
    – Shaji
    Jun 29, 2016 at 17:08
  • @Shaji So you have a Full Name field in your form that gets auto populated by AD? Do you have some sort of Javascript that does this currently? Jun 29, 2016 at 17:28

1 Answer 1


Consider Requester Name field contais value as Nagarajan Muthukumar

Formula for First name: (LEFT([Requester Name],INT(FIND(" ",[Requester Name])-1)))

Result in First Name: Nagarajan

Formula for Last name: RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name])))

Result in Last Name: Muthukumar

If the Name is Nagarajan M Muthukumar (with initial), by the above formula am getting Last Name as M Muthukumar

The below formula will be a overall consolidated formula for both name with and without initial,

  • It gives the Last Name as Muthukumar if the Name is Nagarajan M Muthukumar (With Initial)
  • It also gives the Last Name as Muthukumar if the Name is Nagarajan Muthukumar (Without Initial)

Is this what expected?

=IF(IFERROR(FIND(" ",RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name])))),"0")="0",RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name]))),RIGHT(RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name]))),LEN(RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name]))))-INT(FIND(" ",RIGHT([Requester Name],LEN([Requester Name])-INT(FIND(" ",[Requester Name])))))))

  • I am using the exact same query. The problem is: what if your name has a middle initial?
    – Shaji
    Jun 29, 2016 at 17:06
  • Updated the answer, please check and tell Jun 30, 2016 at 5:20
  • Merci! Works like a charm.
    – Shaji
    Jun 30, 2016 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.