How do I split Three columns?

Deanna Copello

How do I split the value of a drop down menu into a total of three columns?

The column with the drop down menu I would like the first name only to remain, the second to only contain their email and the third their phone number?

Jeff Reisman

@Deanna Croach In order for Column 260 to still function as a drop down, you'd have to split your selected values into 3 columns (Name, Email, Phone,) not including the drop down column.

To get these values split, we're going to use the FIND, LEFT, & MID functions.

Name: =LEFT([Column260]@row, (FIND("/", [Column260]@row) - 1))

(in English - give me the leftmost characters in Column260, starting from just left of the position of the first slash it finds. The FIND portion translates, in your example, to 15. )

Email: =MID([Column260]@row, (FIND("/", [Column260]@row) + 1), (FIND("/", [Column260]@row, (FIND("/", [Column260]@row) + 1)) - FIND("/", [Column260]@row) + 1))))

(Check the parentheses on that one, make sure they color-code correctly!)

In English, give me the middle characters from Column260, starting at the position just after the first slash, and ending just to the left of the position of the second slash.

Phone: =MID([Column260]@row, (FIND("/", [Column260]@row, (FIND("/", [Column260]@row) + 1)), 12)

(Check the parentheses on this one too, make sure they color-code correctly!)

In English: Give me the characters starting just to the right of the second slash and going for 12 characters (###-###-####).

Deanna Copello

Hi Jeff, I copied your formulas exactly into three separate columns and i'm getting unparseable on two:

Jeff Reisman

https://community.smartsheet.com/discussion/comment/338689#Comment_338689

Can you screenshot each formula as it appears while editing it? (i.e. with the color-coding?)

Deanna Copello

works:

Doesn't work:

cell 2

cell 3

Jeff Reisman

Cell 2: Remove the last two end parentheses. The final end parentheses should be blue, to match the first open parentheses after MID.

Cell 3: Add one more end parentheses here:

Deanna Copello

I feel like i'm VERY close.

Shows me:

i used 13 because it was cutting off the last digit of the phone number. i tried -1 to remove the /, but that didn't work. I messed with increasing/decreasing the 1 and did get rid of the /.

Thoughts? and I SO appreciate your help!

Jeff Reisman

I think this is my mess up. I missed one more +1!

Example: Billy Jones/BJones@email.com/555-555-1212

Here's what this should be doing. The syntax is MID(text, starting position from left, number of characters). The syntax for FIND is FIND(text to search for, text to search in, start position from left). We're using the FIND to determine the starting position for the MID.

For my example above: FIND("/", [LFST List]@row, (FIND("/", [LFST List]@row) + 1)) should equal 29. 29 would tell the MID to start with the slash and get the next 12 characters, which ends up being /555-555-121. We need it to start one position to the right of the second slash. Add a +1 in between the 2nd and 3rd end parentheses, before ", 12)":

=MID([LFST List]@row, (FIND("/", [LFST List]@row, (FIND("/", [LFST List]@row) + 1)) +1), 12)

Deanna Copello

AMAZING! Thank YOU so much for your help. I am so new to creating formulas I sincerely appreciate your help.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/338841#Comment_338841

Absolutely!

BTW, this was just announced yesterday - the new Formula Handbook!

https://community.smartsheet.com/discussion/93946/new-your-survival-guide-to-formulas-is-here-meet-the-formula-handbook#latest