How would you extract a number from a cell that contains text and numbers?
Best Answer

Hi @Jerell Parker ,
There are several ways to do this. If the number is always the same number of characters and in the same position you can do:
=VALUE(LEFT([textnum]@row, 6)) where [textnum] is the name of your column with mixed entry.
If the numbers are always at the beginning of the string but their length varies you can do:
=VALUE(LEFT([textnum]@row, FIND(" ", [textnum]@row)1))
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Answers

Hi @Jerell Parker ,
There are several ways to do this. If the number is always the same number of characters and in the same position you can do:
=VALUE(LEFT([textnum]@row, 6)) where [textnum] is the name of your column with mixed entry.
If the numbers are always at the beginning of the string but their length varies you can do:
=VALUE(LEFT([textnum]@row, FIND(" ", [textnum]@row)1))
Help?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Thank you, Mark! This is exactly what I was looking for!

Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

How could you convert this to searching from the right end? I can change "left" to "right" and get the results on the right, but the searching for the space and that value count is still occurring from left to right.

Found this one that worked for me on the right side vs the left.
https://community.smartsheet.com/discussion/5324/extractnumbersfromtextandconverttotext

@Mark Cronk I was trying to leverage this formula to extract a domain name from an email address field but I think attempting to use "@" as the delineator is causing an issue either that or I am fat fingering something. Any ideas would be helpful, I was without smartsheet for a year and knocking the dust off my skills is going slower than I would like. LOL
Thanks so much in advance!
Cat

Hi Cat,
If your email addresses are entered as text you can use:
=RIGHT({insert column name with email]@row, LEN([insert column name with email]@row)  FIND("@", [insert column name with email]@row))
If your emails are stored in a contact column you have to use a different approach.
Work for you?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
Check out the Formula Handbook template!