Extract the 2nd name from 3 names when delimeted by ,

Bruce Johnson

Hi Smartsheet Gurus

How do I extract the 2nd name from a column that has 3 names that has a delimiter of , (comma)

Mickey Mouse, Donald Duck, Minnie Mouse - I want to extract Donald Duck

thanks much, Bruce

Paul Newcome

@Naeem Ejaz That will only work if the second name always starts a the 14th character and is always 11 characters long.

@Bruce Johnson Give this a try for something more dynamic:

=MID("," + [Column Name]@row + ",", FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 2) + 2, FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 3) - (FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 2) + 2))

While the above is a little overworked for your exact request, it does provide the ability to easily adjust this formula to pull the first, third, 25th, etc. based on the "comma" delimiter.

To adjust the above, you would adjust three pieces. The first and third piece indicate the entry number you want to pull out, and the second piece is basically the entry number plus one.

=MID(…………………….., "~", 2) + ……………………………………., "~", 3) - ……………………………………, "~", 2) + ……..))

To pull the first you would adjust these three numbers to be 1, 2, 1. To pull the third: 3, 4, 3. To pull the 25th: 25, 26, 25. So on and so forth.

Bruce Johnson

Hi Paul

@Paul Newcome - Thank you very much for the guidance. I was getting an error - #Incorrect Argument - looking at the formula and direction you gave I changed it to -

=(MID(AssigneeName@row + ", ", FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 1)) + 2, FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 2)) - FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 1)) - 2)

Which works and as you directed I changed the 1,2,1 to 2,3,2 for the 3rd column and subsequent numbering for additional columns.

thanks so much for the help and guidance.

Naeem Ejaz

Paul Newcome

@Naeem Ejaz That will only work if the second name always starts a the 14th character and is always 11 characters long.

@Bruce Johnson Give this a try for something more dynamic:

=MID("," + [Column Name]@row + ",", FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 2) + 2, FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 3) - (FIND("~", SUBSTITUTE("," + [Column Name]@row + ",", ",", "~", 2) + 2))

While the above is a little overworked for your exact request, it does provide the ability to easily adjust this formula to pull the first, third, 25th, etc. based on the "comma" delimiter.

To adjust the above, you would adjust three pieces. The first and third piece indicate the entry number you want to pull out, and the second piece is basically the entry number plus one.

=MID(…………………….., "~", 2) + ……………………………………., "~", 3) - ……………………………………, "~", 2) + ……..))

To pull the first you would adjust these three numbers to be 1, 2, 1. To pull the third: 3, 4, 3. To pull the 25th: 25, 26, 25. So on and so forth.

Bruce Johnson

Hi Paul

@Paul Newcome - Thank you very much for the guidance. I was getting an error - #Incorrect Argument - looking at the formula and direction you gave I changed it to -

=(MID(AssigneeName@row + ", ", FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 1)) + 2, FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 2)) - FIND("~", SUBSTITUTE(AssigneeName@row + ", ", ", ", "~", 1)) - 2)

Which works and as you directed I changed the 1,2,1 to 2,3,2 for the 3rd column and subsequent numbering for additional columns.

thanks so much for the help and guidance.

Paul Newcome

Happy to help. 👍️