Are you able to pull emails from a Contact column?
I have a few sheets where I am needing to have the data Presented with the individuals name, then the email address in the column next to it.
I have come across a few articles that have suggested having a helper 'Contacts Sheet' to reference w/ =INDEX({Contacts Sheet Email}, MATCH{Name@row, {Contacts Sheet Name}, 0)) for other sheets, however, managing a Contacts Sheet is not realistic, or even possible in this instance.
I wasn't sure if there is a function that would be able to do this?
Thank you!
Best Answers
-
@RobbyW How dynamic is the data in the sheet? If it is static.... you can have another contact column, set it equal to the first, save the sheet, then convert the 2nd contact column to a text/number and it will show the name and email in the format NAME <EMAIL>, then you have to parse them into separate columns from there. As you probably already know, simply setting a text column equal to a contact column on brings in the name.
I'm not sure if this helps any... but you can go in the reverse direction easily with separate name and email columns into a contact column.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
There is currently no function to be able to pull an email from a name in a contact type column. You would need to use the API or some other 3rd party app.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
@RobbyW How dynamic is the data in the sheet? If it is static.... you can have another contact column, set it equal to the first, save the sheet, then convert the 2nd contact column to a text/number and it will show the name and email in the format NAME <EMAIL>, then you have to parse them into separate columns from there. As you probably already know, simply setting a text column equal to a contact column on brings in the name.
I'm not sure if this helps any... but you can go in the reverse direction easily with separate name and email columns into a contact column.
Darren Mullen, smartsheetguru.com
Get my 7 Smartsheet tips here
Author of: Smartsheet Architecture Solutions
-
There is currently no function to be able to pull an email from a name in a contact type column. You would need to use the API or some other 3rd party app.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
My (not ideal) way around this is- in your sheet have the email be a text field instead of a contact column, then create a second column where you use a formula =email@row (whatever the original column is called) and the new contact column will just have the email.
Not perfect but works for my use case. Then you can use formulas to parse out first name, last name, domain name in separate columns if needed.
The more manual way is to double click on the cell of the contact column to "Edit Contact" that says the name, edit the cell and delete their name but not the email. (This has problems associated so only use it if you really have to and have the time)
-
I've actually done this before, but it requires creating three helper columns.
First, create a duplicate of your contact column (let's call it "Helper-Contact"). Then switch the properties from contact to text. All of the contacts would then change to NAME <EMAIL> format.
Then, create a second helper column (we'll call it "Email") with text column properties.
Then, you'd create a third helper column (the times I've used this formula, I just call it "marker").
You'd then use the below formulas to pull over only the email from the Helper-Contact column as it's written within the <> into the Email column as text.
Formula for the marker column: =MAX(FIND("<", [Helper-Contact]@row), 2)
Formula for the Email column: =IFERROR(MID([Helper-Contact]@row, marker@row + 1, (LEN([Helper-Contact]@row) - (FIND("<", [Helper-Contact]@row))) - 1), "")
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
-
@Lauren Dominique Does this work on a recurring basis? Are you able to set this up and let it run as the sheet gets edited, or does it only work for the one time you do this and then you have to redo all of this every time a contact gets changed or a new row gets added?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It works for me on a recurring basis!
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
-
@Lauren Dominique How do you get it to continue working without having to reset anything? I tried using your method, and it doesn't pull an email address unless I manually copy/paste the contact into a second contact type column then manually change the column type to text/number.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Oh, shoot. I now see what you mean... I can use it on a recurring basis because I have a separate "Contact" sheet that I maintain where I manually add a person's name and email each time they join the team (which only happens once in a while), which then feeds a helper column that combines their name and email in the NAME <EMAIL> format. And then I copy and paste that list of NAME <EMAIL>s into a single-select drop-down column in my sheet where I have these formulas I shared above.
It's been a while since we've had a new person join the team, so I forgot that key component. So sorry for misleading... I saw this thread and got excited thinking I could help.
Also, the reason I immediately said it works on a recurring basis is because sheet receives content from a form submission. So, every time someone selects a name on the form and it enters the sheet, it's already formatted the way it needs to be for the formula to separate out the email.
So... I guess I don't actually have a universal solution for this problem, but rather a workaround for a very specific/unique situation. Sorry again.
If this comment helped you, please help me and help others by using the buttons below if you found it 💡 Insightful or ❤️ Awesome!
-
@Lauren Dominique No worries. I got excited too and was hoping I had just missed a piece.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!