Auto create incident number based on last name + number

Alyssa Steinruck

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

Alyssa Steinruck

@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

Mark Cronk

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

Alyssa Steinruck

I have double checked there are no extra spaces in the Salesman column; still not working.

Mark Cronk

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

Alyssa Steinruck

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!

Mark Cronk

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

Alyssa Steinruck

@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

Mark Cronk

Glad you found the solution. Thanks for using the Community. Please accept an answer to close the discussion.

Mark