# How to retrieve substring of another field using Calculated field

• ### Question

• Hi Friends,

i have a sharepoint List. In this List there is two field (Name Field & DisplayName Field). When user add item to the list and put Name Field value Beck, Jonathan S(LastName, FirstName Middle Initial), so in DisplayName field should automatically populate accordingly Jonathan S Beck(FirstName Middle Initial LastName) without comma. Using following formula i am getting only First name and MI but not getting Last name.

`=RIGHT([Name],LEN([Name])-INT(FIND(",",[Name])))`

using following formula getting Last Name also but problem is how can i join both formula together, so that it can disply Full Name in Calculate field.

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

• Edited by Friday, November 2, 2012 1:31 AM
Thursday, November 1, 2012 10:37 PM

• Have you tried concatenating the two formulas?

=concatenate(`RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))),`LEFT([Name],INT(FIND(",",[Name])-1)))

something like that.  see if it works.

Friday, November 2, 2012 1:39 AM
• Hi,

Please use the following fomular to conver the four scenarios you mentioned:
=IF([Name]="", "", IF(ISERROR(INT(FIND(",", [Name]))), [Name], IF(ISERROR(INT(FIND("(", [Name]))), concatenate(concatenate(RIGHT([Name], LEN([Name])-INT(FIND(",",[Name]))), " "),LEFT([Name],INT(FIND(",",[Name])-1))), concatenate(LEFT(RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))), INT(FIND("(", RIGHT([Name],LEN([Name])-INT(FIND(",",[Name])))) - 1)), LEFT([Name],INT(FIND(",",[Name])-1)), concatenate(" ", RIGHT([Name], LEN([Name]) - INT(FIND("(", [Name])) + 1))))))

Thanks,
Jinchun Chen

Jinchun Chen(JC)
TechNet Community Support

• Marked as answer by Thursday, December 6, 2012 12:32 AM
Thursday, November 29, 2012 9:18 AM

### All replies

• Have you tried concatenating the two formulas?

=concatenate(`RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))),`LEFT([Name],INT(FIND(",",[Name])-1)))

something like that.  see if it works.

Friday, November 2, 2012 1:39 AM
• Hi Laura,

Thanks for your reply. The following formula is working fine if my name field value is Johnson, Michael M and its giving me Michael M Johnson.

=concatenate(`RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))),`LEFT([Name],INT(FIND(",",[Name])-1)))

1. Johnson, Michael M------> Michael M Johnson (This is working fine)

But what i need to modify the above formula if i want to achieve following patterns.

2. Johnson, Michael M (OH)         i want to get---> Michael M Johnson (OH)

3. Ali, Zubin (MX200)------>         Zubin Ali (MX200)

4. 287 Park 3rd AV Control Rm-----> 287 Park 3rd AV Control Rm  (want to display in calculated field as it is in Name field)

Please let me know if you have any suggestions that will be very appreciated.

• Edited by Monday, November 5, 2012 8:43 PM
Monday, November 5, 2012 8:41 PM
• Hi,

Please use the following fomular to conver the four scenarios you mentioned:
=IF([Name]="", "", IF(ISERROR(INT(FIND(",", [Name]))), [Name], IF(ISERROR(INT(FIND("(", [Name]))), concatenate(concatenate(RIGHT([Name], LEN([Name])-INT(FIND(",",[Name]))), " "),LEFT([Name],INT(FIND(",",[Name])-1))), concatenate(LEFT(RIGHT([Name],LEN([Name])-INT(FIND(",",[Name]))), INT(FIND("(", RIGHT([Name],LEN([Name])-INT(FIND(",",[Name])))) - 1)), LEFT([Name],INT(FIND(",",[Name])-1)), concatenate(" ", RIGHT([Name], LEN([Name]) - INT(FIND("(", [Name])) + 1))))))

Thanks,
Jinchun Chen

Jinchun Chen(JC)
TechNet Community Support

• Marked as answer by Thursday, December 6, 2012 12:32 AM
Thursday, November 29, 2012 9:18 AM