How do I split Three columns?
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?
Answers
-
@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 (###-###-####).
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff, I copied your formulas exactly into three separate columns and i'm getting unparseable on two:
-
Can you screenshot each formula as it appears while editing it? (i.e. with the color-coding?)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
works:
Doesn't work:
cell 2
cell 3
-
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:
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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!
-
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)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
AMAZING! Thank YOU so much for your help. I am so new to creating formulas I sincerely appreciate your help.
-
Absolutely!
BTW, this was just announced yesterday - the new Formula Handbook!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!