Split SharePoint column into 2 calculated columns

Andrews Dumith 41 Reputation points
2022-06-24T17:19:32.277+00:00

In a SharePoint custom list, I have the column FullName which is entered by a user.

The user will enter data as First Name and Last Name. For example, Pedro Perez. I want 2 calculated populated - FirstName and LastName in my example the values will be Pedro, Perez in respective columns.

I got the formula from a previous question:

LEFT([FullName],FIND(" ",[Fullname])-1)  

This one working great, but when I try to use the second formula I get an error value:

LEFT(RIGHT(FullName,LEN(FullName)-FIND(" ",FullName)),FIND(" ",RIGHT(FullName,LEN(FullName)-FIND(" ",FullName)))-1)  

Any ide where is the error?

Thank you in advance,

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
11,166 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
3,259 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Micca 161 Reputation points
    2022-06-27T08:13:04.12+00:00

    Hi

    for SharePoint online the following examples might work for you as well:

    result:
    215294-image.png

    Calculated column FIRST

    =TRIM(MID(FullName,1,FIND(", ",FullName,1)-1))

    • Start at position one from FullName
    • end at position
      • where the following string is found in column FullName , starting at position 1: ", "
      • Minus 1
      • Trim the result

    Calculated column LastName

    =TRIM(RIGHT(FullName,LEN(FullName)-FIND(", ",FullName,1)))

    • Get right from Fullname
    • starting at
      • Length of FullName
      • minus position of string ", " within FullName
        -- starting to look from 1st position within FullName
      • Trim the result
    0 comments No comments

  2. Yanli Jiang - MSFT 28,786 Reputation points Microsoft Vendor
    2022-06-27T08:56:25.74+00:00

    Hi @Andrews Dumith ,
    For the column of LastName, use the following function:

      RIGHT(FullName,LEN(FullName)-FIND(" ",FullName))  
    

    And the result is:
    215343-06271.png

    Thanks,
    Yanli Jiang

    ===========================================

    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.