Auto create incident number based on last name + number
I am trying to create a formula to take the first 4 letters from the Salesman Name column and add "-001", etc. to the end of the first 4 letters to create an Incident Number. My coworker created this formula and it's almost working, but isn't always taking the first 4 letters from the last name (see line 3 (incorrect) vs line 4 (correct). Any ideas why this is not working all the time?
Formula being used: =UPPER(LEFT(RIGHT([Salesman Name]@row, FIND(" ", [Salesman Name]@row)), 4)) + "-" + [Auto Number]@row
Best Answer
-
@Mark Cronk Good news! I called Smartsheet support and the formula was corrected by using MID. Just in case you need this in the future, it looks like this:
=UPPER(MID([Salesman Name]@row, FIND(" ", [Salesman Name]@row), 5)) + "-" + [Auto Number]
Thanks for your help, I appreciate it!
Alyssa
Answers
-
Hi @Alyssa Steinruck ,
I suspect it's your contact list. For this to work the contacts used in your Saleman column will need to have their name and email entered. The name has to to be FIRST LAST with a space.
Is that the problem?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
I have double checked there are no extra spaces in the Salesman column; still not working.
-
Hi @Alyssa Steinruck ,
Odd that some work. Click on the contact name in the cell of one that works. It should pop up a box with the name and email. Compare that with one that doesn't work. Are both the name and email populated? Are the emails a standard format without spaces? I'll think through it some more but don't have any other suggestions.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Hi @Mark Cronk,
Both the cells that work and don't work pop up with the name and email and they're both the same. Email addresses are standard formats without spaces.
I've switched a contact in a cell that doesn't work with a contact in a cell that does work and the results are the same; if it originally worked for that contact, it works for that contact in another cell and vice versa. I have no idea what's going on here, I can't find any sort of reason for the ones that don't work.
Thanks for your help!
-
Hi @Alyssa Steinruck ,
Let's try a couple more things, change the Salesman Name column from Contact to Text. I suspect you'll see that the rows that work look different than the rows that don't. If they look the same then the issue is the formula. Convert it back to a contact column.
Then try this formula in your incident number column:
=UPPER(MID([Salesman Name]@row, FIND(" ", [Salesman Name]@row)), 5)) + "-" + [Auto Number]
Thanks for letting me help.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
@Mark Cronk Good news! I called Smartsheet support and the formula was corrected by using MID. Just in case you need this in the future, it looks like this:
=UPPER(MID([Salesman Name]@row, FIND(" ", [Salesman Name]@row), 5)) + "-" + [Auto Number]
Thanks for your help, I appreciate it!
Alyssa
-
Glad you found the solution. Thanks for using the Community. Please accept an answer to close the discussion.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!