1

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? – Ramana Viswanadha Jun 27 '16 at 19:14
  • Your second formula is reading from the calculated colum? – Amal Hashim Jun 28 '16 at 10:16
  • @Ramana - Its auto populated from AD. – Shaji Jun 29 '16 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 '16 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? – Ramana Viswanadha Jun 29 '16 at 17:28
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 '16 at 17:06
  • Updated the answer, please check and tell – Nagarajan Muthukumar Jun 30 '16 at 5:20
  • Merci! Works like a charm. – Shaji Jun 30 '16 at 14:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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