Extracting partial data from one cell to put in another
I am trying to extract data from one cell and put it in another.
For example:
So I need everything to the right of the hyphen to end up in the Other Column.
Answers
-
Hi Tabitha,
What you'll need to do is read the contents of the cell looking for the hyphen and then use the LEFT() or RIGHT() function to grab the part of the string you want. In the example below I am using the FIND() function to identify where in the the string the "-" is located. then I use the LEFT function to pull only those characters that I want. (Notice that I subtract 1 from the Find result so it doesn't include the "-" in the string.)
[Full Name]@row = "Bob-Johnson"
=LEFT([Full Name]@row, FIND("-", [Full Name]@row) - 1)
So first I use FIND() to determine where in "Bob-Johnson" the "-" is. The answer is 4 as it counts the characters from left to right. Then I subtract one so the answer is 3.
Then that answer becomes the num_chars value in the LEFT function:
LEFT( text [ num_chars ])
Going to the right is a little trickier as you need to know the length of the string in total in order to know how many characters you need.
Using the LEN() function tells us the total string lengh is 11 characters. so if I subtract the first 4 I get from the FIND() I'm left with the second half of the string, i.e. "Johnson"
=RIGHT([Full Name]@row, LEN([Full Name]@row) - FIND("-", [Full Name]@row))
Hope that makes sense. :)
-
Thank you very much!
-
I am wanting to copy only the first 50 characters of one cell. I am using this formula to capture the beginning of the cell: =RIGHT([Order Status Comments]@row, LEN([Order Status Comments]@row) - FIND("", [Order Status Comments]@row) + 2)
This works perfectly. However, I want to limit the characters to 50. How would I identify that?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!