I accomplished this by having some helper columns that I hid afterwards.
So one column would be: (Let's say this new column is called "Helper1")
=RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(", ", [Full Name]@row))
Then the next column would be "Title"
=RIGHT([Helper1]@row, LEN([Helper1]) - FIND(", ", [Helper1]))
Does this work?
Essentially it's grabbing everything after the second comma.
Formula to Extract a Full Name and Title, into Separate Columns "First" "Last" "Title"
=Chris Palmer
Community Champion
I have a long list of full names in the "Full Name" Column.
They need to be broken out into 3 separate columns "First Name" "Last Name" and "Title"
Example shown in the screen shot below.
Is there a formula I can use to extract this for each column?
"First Name" I've been using:
=MID([Full Name]@row, FIND(" ", [Full Name]@row) + 1, FIND(" ", [Full Name]@row, FIND(" ", [Full Name]@row) + 1) - FIND(" ", [Full Name]@row) - 2)
"Last Name" I'm using:
=LEFT([Full Name]@row, FIND(",", [Full Name]@row) - 1)
These formulas work ok, but some errors still appear.
I'm struggling most with creating a formula for the "Title" column.
Perhaps something that extracts everything to the right after the second comma?
Any thoughts on a better way to do this?
https://www.linkedin.com/in/zchrispalmer/
Tags:
Best Answer
-
I accomplished this by having some helper columns that I hid afterwards.
So one column would be: (Let's say this new column is called "Helper1")
=RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(", ", [Full Name]@row))
Then the next column would be "Title"
=RIGHT([Helper1]@row, LEN([Helper1]) - FIND(", ", [Helper1]))
Does this work?
Essentially it's grabbing everything after the second comma.
Answers
-
I accomplished this by having some helper columns that I hid afterwards.
So one column would be: (Let's say this new column is called "Helper1")
=RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND(", ", [Full Name]@row))
Then the next column would be "Title"
=RIGHT([Helper1]@row, LEN([Helper1]) - FIND(", ", [Helper1]))
Does this work?
Essentially it's grabbing everything after the second comma.
-
@Mr. Chris You'll have to do some Smartsheet gymnastics and use the SUBSTITUTE() or REPLACE() functions.
At a high level you'll want to substitute Firstname, and Lastname, with "" then you'll be left with the title.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
@Mr. Chris This video of mine might help you some.
Darren Mullen, join the Smartsheet Guru Elite
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
Thank you @Michael Culley this worked! I was struggling on this for hours.
https://www.linkedin.com/in/zchrispalmer/
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Want to practice working with formulas directly in Smartsheet?
Check out the Formula Handbook template!
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …