Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
extract part of a cell's text
Hello,
New smartsheet user here.
I have cells that contain dimensions of objects. A cylinder, for example, looks like this: Ø120*12. I need a way to extract just the text to the right of the *, in this case 12, to another cell. Another example: Ø18*1000 would return 1000.
How do you do this?
Thanks!
Comments
-
Hi Ty,
Yes, there is.
In my example, I'm using the functions MID and RIGHT.
Try something like this for the first one.
=MID(Dimension@row; 2; 2)
The same version but with the below changes for your and others convenience.
=MID(Dimension@row, 2, 2)
Try something like this for the second one.
=RIGHT(Dimension@row; 4)
The same version but with the below changes for your and others convenience.
=RIGHT(Dimension@row, 4)
Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.
Did it work?
Hope that helps!
Have a fantastic week!
Best,
Andrée Starå
Workflow Consultant / CEO @ WORK BOLD
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E: andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
I suggest using the RIGHT function and automating how many characters to pull so that you don't have to worry about hardcoding whether it is 4, 2, or 23.
=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))
.
The LEN function gives you the total count of characters. The FIND function looking for the asterisk gives you how many characters from the left the asterisk is found.
.
Ø120*12
is 7 characters long. Subtract the position of the asterisk (5), and you tell the RIGHT function to pull 2 digits.
Ø18*1000
is 8 characters long. Subtract the position of the asterisk (4), and you tell the RIGHT function to pull 4 digits.
.
This will maximize your automation by basing it off of the data itself instead of manual calculations.
.
If you were planning on using this data that you are pulling as actual numbers in the future as opposed to just displaying them as a text string, you would need to wrap the entire formula in a VALUE function.
=VALUE(RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row)))
-
@Paul Newcome As always, thank you for this - I was looking for a formula to pull only the text to the right of a character. I'm so thankful that you already had it written out for me!
-
@Paul Newcome I was looking for EXACTLY this solution! THANK YOU!!!!
=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))
-
@Paul Newcome thank you! this is what I needed!!! :)
=RIGHT(Dimention@row, LEN(Dimension@row) - FIND("*", Dimension@row))
-
I need a way to extract just the text MEGA MALL. I have 3,000 rows like this one.
LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410
-
@Marilen.Navarro103391 What are the consistencies in your text strings? Will it always be after the 5th "-"? Will it always follow "Lead-"? Will it always come immediately before the last "-"?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.9K Get Help
- 474 Global Discussions
- 208 Use Cases
- 517 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 84 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!