Right Formula assistance
Hi all,
I am having trouble creating a text based formula to grab everything to the right of a ")", which is user's name. I also have various cases where, unfortunately, the names populate in duplicate from the source file I am shuttling in. And lastly, I have cases where the name is in the incorrect order of "First Last". Below are three examples of what I see come through and the goal is just have a "Last, First" name formatting:
Exit Associate (DEAG) Jaggers, Jared
Exit Associate (DEAG) Jared Jaggers
Exit Associate (DEAG) Jaggers, JaredJaggers, Jared
End goal would be for all of these to return just "Jaggers, Jared"
I am even willing to have separate column formulas if that is needed.
Thanks in advance.
Jared
Answers

Lets give these a shot. Try putting each in its own column for now so we can do some troubleshooting if something isn't working, but once we get all three working, we should be able to combine them. The below are in order of your three examples.
=IF(LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, ",", "")) = 1, RIGHT([Column Name]@row, LEN([Column Name]@row)  (FIND(")", [Column Name]@row) + 2)))
=IF(LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, ",", "")) = 0, RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))))) + ", " + MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))  1), FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))))  FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))  1)))
=IF(LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, ",", "")) = 2, SUBSTITUTE(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  2)), MID([Column name]@row, FIND(",", [Column Name]@row), FIND(",", [Column name]@row, FIND(",", [Column Name]@row) + 1)  FIND(",", [Column Name]@row)), ","))

Will there be a list of "Exit Associates" that you could potentially match against?

@Carson Penticuff , I have a list of Last, First name in another sheet that I am looking to bump these Exit Associate (DEAG) line items up against.
@Paul Newcome Thanks for these! We have progress with some interesting results.
What can I provide to assist with troubleshooting?

Ok. Looks like [Name] is working properly. The issue there for Lisa Haney is that it is actually a fourth format.
Let me do some testing on [Name2] and [Name3] formulas and get back to you. I just threw them all together here, so now I am going to put them into a sheet to try to do some troubleshooting.
Do we need to accommodate that 4th format, and are there any other formats we need to worry about?

Yes, that fourth format slipped by me. I am not seeing any other unique formats, just the original three and now this fourth one.
Impressive formulas to just through together and thanks for you help. No rush on my end and I look forward to what you come back with.

Here we go...
First
Exit Associate (DEAG) Last, First:
=IF((LEN(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row))))  LEN(SUBSTITUTE(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row)), " ", "")) = 1, RIGHT([Column Name]@row, LEN([Column Name]@row)  (FIND(")", [Column Name]@row) + 1)))
Second
Exit Associate (DEAG) First Last:
=IF(FIND(",", [Column Name]@row) = 0, RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))))) + ", " + MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1)))
Third & Fourth
Exit Associate (DEAG) Last, FirstLast, First & Exit Associate (DEAG) Last, FirstFirst Last:
=IF((LEN(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row))))  LEN(SUBSTITUTE(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row)), " ", "")) = 2, MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  2)), (FIND(",", [Column Name]@row)  1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  2))  1)) + ", " + LEFT(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1)), (LEN(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1)))  1) / 2))
All combined into a single column:
=IF((LEN(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row))))  LEN(SUBSTITUTE(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row)), " ", "")) = 2, MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  2)), (FIND(",", [Column Name]@row)  1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  2))  1)) + ", " + LEFT(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1)), (LEN(MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1)))  1) / 2), IF((LEN(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row))))  LEN(SUBSTITUTE(RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND(",", [Column Name]@row)), " ", "")) = 1, RIGHT([Column Name]@row, LEN([Column Name]@row)  (FIND(")", [Column Name]@row) + 1)), RIGHT([Column Name]@row, LEN([Column Name]@row)  FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))))) + ", " + MID([Column Name]@row, FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1, (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", "")))) + 1)  (FIND("~", SUBSTITUTE([Column Name]@row, " ", "~", LEN([Column Name]@row)  LEN(SUBSTITUTE([Column Name]@row, " ", ""))  1)) + 1))))

Wow, @Paul Nelson , you're a magician.
I do have a small subset now that are spitting back #Invalid Operation. They look to be a mix of scenarios 1 and 3 and I am guessing the reason scenario 1 is still an issue is because some users have a middle name. Apologies as I didn't think of that as a separate scenario but it probably is for your formula.
Exit Associate (DEAG) Rosales, Christine Angelique
There's also one annoying outlier that may just not get captured in this formula due to uniqueness. Both Will and William...
Exit Associate (DEAG) Vanluvanee, WillWilliam Vanluvanee

Those ones that go "last comma space first last comma space first" should be grabbed by the one above that is for the "Third & Fourth" scenarios. Can you try plugging that formula in as a standalone for those entries? Is it working for some but not others?
The other two scenarios I will need to dig in to them and get back to you.
Exit Associate (DEAG) Rosales, Christine Angelique
Exit Associate (DEAG) Vanluvanee, WillWilliam Vanluvanee
In regards to that last one... Would you want "Will" or "William" populated for the first name portion?

Paul, I am still returning all Invalid Operation for this subset when I plug in the "Third & Fourth" scenario in it's own column (first image). But, when I back out to all of the rows, there are some where it is pulling a name correctly (second image). Let's use William for that unique case.

When you double click into one of the cells in the Summary column being referenced that outputs the error... Do you maybe notice any extra spaces tucked in anywhere? Smartsheet will only display single spaces, but double spaces are stored on the backend and can throw off counts.

And looking at it closer, I think there may be an easier way to do this as well. We may end up with some very different formulas...

Very different formulas is ok, I will just pull results from the various column formulas into one main Name column once we are done.
The only place I am seeing an extra space is after (DEAG) but that is consistent in all rows. I went into my source Excel file as well and only see the extra space there as well. Nothing at the end of the names or around the commas.

This one is interesting. I would expect the first name to just be Donald without the 'B'

Lets try this:
Insert a helper column called "Base" and use this column formula:
=SUBSTITUTE(Summary@row, LEFT(Summary@row, FIND(")", Summary@row) + 1), "")
Then a text/number column called "Manual Override".
Then in the column used to generate the name, try this:
=IFERROR(IF(FIND(",", Base@row) > 0, LEFT(Base@row, FIND(",", Base@row)  1), RIGHT(Base@row, LEN(Base@row)  FIND(" ", Base@row))) + ", " + IF(LEN(Base@row)  LEN(SUBSTITUTE(Base@row, ",", "")) = 1, IF(FIND(LEFT(Base@row, FIND(",", Base@row)  1), SUBSTITUTE(Base@row, LEFT(Base@row, FIND(",", Base@row)  1), "", 1)) > 0, LEFT(MID(Base@row, FIND(" ", Base@row) + 1, FIND(" ", Base@row, FIND(" ", Base@row) + 1)  (FIND(" ", Base@row) + 1)), LEN(MID(Base@row, FIND(" ", Base@row) + 1, FIND(" ", Base@row, FIND(" ", Base@row) + 1)  (FIND(" ", Base@row) + 1))) / 2), RIGHT(Base@row, LEN(Base@row)  FIND(" ", Base@row))), IF(LEN(Base@row)  LEN(SUBSTITUTE(Base@row, ",", "")) = 2, RIGHT(Base@row, LEN(Base@row)  FIND("!", SUBSTITUTE(Base@row, ",", "!", 2))), LEFT(Base@row, FIND(" ", Base@row)  1))), [Manual Override]@row)
I tried combining them into a single column formula, but for whatever reason I keep getting an error that just doesn't make sense for what I am doing.
I THINK I may be onto something for that last scenario, but for now you should be able to apply a filter to the sheet to only show rows where the name column is blank and then manually enter the correct name in the override column. It sounds like it is not a common occurrence, so it shouldn't be too much to manage.

I have what I need at this point. I was able to find some better fields for my source data to weed out some of these outliers. Thanks for your help here as I am still using a couple of these!
Jared
Help Article Resources
Categories
Check out the Formula Handbook template!