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

Jerell Parker · 02/05/21

Mark Cronk · 02/06/21

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([text-num]@row, 6)) where [text-num] 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([text-num]@row, FIND(" ", [text-num]@row)-1))

Help?

Mark

Mark Cronk · 02/06/21

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([text-num]@row, 6)) where [text-num] 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([text-num]@row, FIND(" ", [text-num]@row)-1))

Help?

Mark

Jerell Parker · 02/08/21

Mark Cronk

Feb 6, 2021

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([text-num]@row, 6)) where [text-num] 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([text-num]@row, FIND(" ", [text-num]@row)-1))
Help?
Mark

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

Mark Cronk · 02/09/21

Glad you found a solution. Thank you for contributing to the Community.

Mark

Speigel · 08/19/21

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.

Speigel · 08/19/21

Found this one that worked for me on the right side vs the left.

https://community.smartsheet.com/discussion/5324/extract-numbers-from-text-and-convert-to-text

catmarczan · 07/06/22

@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

Mark Cronk · 07/19/22

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

Formulas and Functions

Formulas and Functions

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

Best Answer

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Finding if a date falls in a range in a referenced sheet

CountIF contains name from a contact list help needed

Formula for averaging days