Contact List - Extract First / Last Name?

Paul Newcome

@Melvin Collins The LEN function simply gives the total count of characters contained within a cell. So the basic logic is that the total number of characters minus the number of characters leading up to the space will give the number of remaining characters which is used to determine how many to pull for the RIGHT function.

Ann Hannan

@Paul Newcome I'm picking up on this thread and have the same question as Lauren Darvesh: How do we modify this formula which extracts the First Name from a Contact list column to account for Contact list columns that include multiple contacts?

I've included a screen shot below. I have my multi-Contact Column as Loan Servicing Contacts and I'm using =[Loan Servicing Contacts]@row to pull the names into the LS Contact Name column. It accurately pulls both names separated by a comma.

If I wanted ONLY the first names in the column LS Contact Fname, what would that formula look like? I'm using the formula in this original post to extract first name, but it only pulls the first contact, not the second. Is there a way to modify this formula to get all of the first names of contacts listed in the Loan Servicing Contacts column:

=LEFT([Loan Servicing Contacts]@row, FIND(" ", [LS Contact Name]@row) - 1)

Thank you so much for your informative answers!!

Ann

Paul Newcome

@Ann Hannan Will it only ever be 2? If not, what is the maximum number of contacts possible?

There is no "simple" or straightforward way of writing out the formula because we will need to account for the highest number of contacts possible within a single cell.

The easiest way to accomplish this requires more structure but definitely simplifies the formulas a bit. Basically you create helper columns for each contact. You would use one of the parsing solutions available here in the community to parse out the names into their own columns and then wrap each of the parsing formulas in your LEFT/FIND combo.

=LEFT(parsing_formula, FIND(" ", parsing_formula) - 1)

Ann Hannan

Thank you, @Paul Newcome. I'll have to discuss with the team to see if we can count on 2 contacts max. I'll refer back to your suggestion. I'm thinking that we may be able to simply the need for the info in the first place.

Karen Webber

@Paul Newcome, picking up on this thread with another question about contact lists.

I have a form that captures the full name (which would be first and last, separated by space), and your formula for separating out the first name into a column works perfectly. However, the MID formula noted in the thread for surnames doesn't always work for me, because some people also enter a middle name.

Any suggestion on how to separate out the last name only (maybe by usage of space from the end of the string)?

Paul Newcome

@Karen Webber Assuming they are using spaces, you can try something like this:

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

It works this way:

We find out how many spaces there are.

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

Then we drop that into a SUBSTITUTE function to replace the last one with an exclamation point.

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

Then we FIND that exclamation point to find out how far from the left it is and add 1 to get the starting number.

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

Subtract that from the total number of characters

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

And that tells us how many characters from the right we need to pull

=RIGHT([Name Column]@row, LEN([Name Column]@row) - (FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))) + 1))

Karen Webber

@Paul Newcome My brain broke about two seconds into trying to follow this! I had to adjust your code to a +0 at the end (it cut off the first letter of the surname), but it works perfectly now - thank you.

Paul Newcome

@Karen Webber Sorry about that. Sometimes we need the +1 and other times we don't. Instead of doing +0, here is a slightly cleaner version (removed the +0 and a set of parenthesis that was no longer needed):

=RIGHT([Name Column]@row, LEN([Name Column]@row) - FIND("!", SUBSTITUTE([Name Column]@row, " ", "!", LEN([Name Column]@row) - LEN(SUBSTITUTE([Name Column]@row, " ", "")))))

Karen Webber

Thank you, @Paul Newcome, your help is much appreciated!

Paul Newcome

@Karen Webber Happy to help. 👍️

Benn

@Paul Newcome If I want to get the First Name and Last Name from this value "John L Doe", what is the formula?

Paul Newcome

@Benn Are you just trying to strip that single middle initial out?

Benn

yes

Paul Newcome

@Benn Try this:

=SUBSTITUTE([Name Column]@row, MID([Name Column]@row, FIND(" ", [Name Column]@row), 2), "")

Scott Orsey

While this thread is a bit old, I thought I'd share a different approach that I settled on to solve this problem. My use case has a limited number of team members (TMs) that can be assigned to a task, and any number of TMs can be assigned to each task. I needed a way to show a list of these assigned TMs without taking up as much space as was needed to show a listing of full names.

I originally attempted a solution like described above to grab the first names using the LEN and MID functions that could accommodate up to 4 TM's being listed. I would have liked more to be safe, but the vast number of helper columns (and the long indecipherable function that I used to avoid so many helper columns) was inelegant and used up columns senselessly in a sheet with many other useful columns and the potential for 1000s of rows. The SS cell limit was a potential concern.

I opted instead to create a separate table where we would list the TM's "System Name" (as would appear in a contact list) and their "Display Name" which could be their first, last or even a preferred nickname. I found other uses for this table to store other TM data that we may also use. The key here (and consider this before you attempt this idea yourself) is that this approach can only be used for a relatively limited number of TMs, perhaps 15 or so people on a team. You'll see why in a sec.

Then, I used the SUBSTITUTE function to look up each TM System Name in the contact column and swap in the TM Display Name. For example, if the list of TMs in the other sheet had only 1 name, then you'd have:

=SUBSTITUTE([Assigned To]@row, INDEX({TM System Names}, 1), INDEX({TM Display Names}, 1))

Obviously, that's not very useful, so I nested 15 of these together...

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Assigned To]@row, INDEX({TM System Names}, 1), INDEX({TM Display Names}, 1)), INDEX({TM System Names}, 2), INDEX({TM Display Names}, 2)), INDEX({TM System Names}, 3), INDEX({TM Display Names}, 3)), INDEX({TM System Names}, 4), INDEX({TM Display Names}, 4)), INDEX({TM System Names}, 5), INDEX({TM Display Names}, 5)), INDEX({TM System Names}, 6), INDEX({TM Display Names}, 6)), INDEX({TM System Names}, 7), INDEX({TM Display Names}, 7)), INDEX({TM System Names}, 8), INDEX({TM Display Names}, 8)), INDEX({TM System Names}, 9), INDEX({TM Display Names}, 9)), INDEX({TM System Names}, 10), INDEX({TM Display Names}, 10)), INDEX({TM System Names}, 11), INDEX({TM Display Names}, 11)), INDEX({TM System Names}, 12), INDEX({TM Display Names}, 12)), INDEX({TM System Names}, 13), INDEX({TM Display Names}, 13)), INDEX({TM System Names}, 14), INDEX({TM Display Names}, 14)), INDEX({TM System Names}, 15), INDEX({TM Display Names}, 15))

While this formula is long, its much easier to decode than the formulas needed to parse out sections of text in the multiselect contact string.

A few notes:

1) In the Team Member sheet, only the first 15 names will be swapped using this formula.

2) Apparently, the SUBSTITUTE formula cannot handle a "null" for the "old_text", so the Team Member sheet must have at least 15 rows of names in it (otherwise it returns an error which I found to be difficult to correct with a simple solution). If your team, like mine, has less than 15 people, I just put filler info in for the remaining rows and anticipate swapping in real names as the team grows.

3) Any name in the [Assigned To] column that isn't found using this lookup, will simply remain as-is using the System Name.

4) BTW... SS needs a function that would return the Nth element of a multiselect (Like INDEX does with a range). There's a function (COUNTM) to count the number of elements in the multiselect, so why not?

5) While I'm dreaming, I can't tell you how often I use nested SUBSTITUTE functions. Maybe SS could tweak the SUBSTITUTE function to accept ranges (in addition to individual values) for the second and third arguments. The SUM function can do it, so why not? Then my formula would reduce to the simple:

=SUBSTITUTE([Assigned To]@row, {TM System Names}, {TM Display Names})

*sigh*