# Can numbers as TEXT be turned into a NUMBER value?

Options
✭✭✭✭

I got a TEXT excerpt (having only numbers) extracted from a larger Text and I want to turn/use it as a number.

Is there a Function available to do this?

Thanks

• ✭✭✭✭✭✭
Options

Try the VALUE function. Any numbers inserted as text (0001 for example) will be changed to their numerical value (1). You can wrap the value function around the formula you are using to extract the numerals from the text field.

ex.

Blue 462

Using =RIGHT(text@row, 3) would get you '462 (the apostrophe indicates text.)

=VALUE(RIGHT(text@row, 3)) turns '462 text value to 462 numeric value, which then can be summed, etc.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭✭✭
Options

Try the VALUE function. Any numbers inserted as text (0001 for example) will be changed to their numerical value (1). You can wrap the value function around the formula you are using to extract the numerals from the text field.

ex.

Blue 462

Using =RIGHT(text@row, 3) would get you '462 (the apostrophe indicates text.)

=VALUE(RIGHT(text@row, 3)) turns '462 text value to 462 numeric value, which then can be summed, etc.

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• ✭✭✭✭
Options

Thank you Jeff!!!

• Options

Hi Jeff

I have used the formula that you used, however that only works on the specific number of characters you state. What if you are trying to convert text to numbers that range from 3 characters to 10 or 12? do you know a way to incorporate this (see snip)?

• ✭✭✭✭✭✭
Options

So in this case, the \$ is stored as text, so the VALUE function can't convert it, yes?

You can use VALUE with the MID function, start at position 2, and have it go for, say, 30 characters. It will only consider the characters it finds, so it doesn't matter that you specify far more than you're going to have:

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

• Options

Absolutely brilliant! Worked like a charm.

Just so you know that using the original formula, I still got it to work, but had to use 12 columns to extract the numbers and another column using IFERROR to get the final result.

Seriously appreciate your help and expertise.

• Options

How do I mark it as accepted and give it a value?

I clicked on the "Awesome" link. Does this do it?

Options

If you're the person who posted the original question, there's a little question at the bottom of every comment that says:

Did this answer the question? Yes No

In this case, since you didn't ask the first question you won't be able to pin the "Best Answer". An Awesome reaction is a great way to say thanks! 🙂

Cheers,

Genevieve

• ✭✭✭✭✭✭
Options

You got me my "100 Awesomes" badge! Thanks!

Regards,

Jeff Reisman

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!