Right Formula assistance

Jags0829

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

Paul Newcome

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)), ","))

Carson Penticuff

@Jags0829

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

Jags0829

@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?

Paul Newcome

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?

Jags0829

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.

Paul Newcome

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))))

Jags0829

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

Paul Newcome

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 stand-alone 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?

Jags0829

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.

Paul Newcome

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 back-end and can throw off counts.

Paul Newcome

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...

Jags0829

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.

Jags0829

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

Paul Newcome

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.

Jags0829

@Paul Newcome

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