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([email protected]; 2; 2)
The same version but with the below changes for your and others convenience.
=MID([email protected], 2, 2)
Try something like this for the second one.
=RIGHT([email protected]; 4)
The same version but with the below changes for your and others convenience.
=RIGHT([email protected], 4)
Depending on your country you’ll need to exchange the comma to a period and the semicolon 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: [email protected]  P: +46 (0)  72  510 99 35
Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

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([email protected], LEN([email protected])  FIND("*", [email protected]))
.
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([email protected], LEN([email protected])  FIND("*", [email protected])))
thinkspi.com

@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!
Help Article Resources
Categories
Check out the Formula Handbook template!