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

Jerell Parker

Mark Cronk

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

Mark Cronk

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

https://community.smartsheet.com/discussion/comment/278513#Comment_278513

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

Mark Cronk

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

Mark

Speigel

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

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

@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

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