Can numbers as TEXT be turned into a NUMBER value?

Cruz Ortiz

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

Jeff Reisman

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.

Jeff Reisman

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.

Cruz Ortiz

Thank you Jeff!!!

andrew.taylor37161

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)?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/371287#Comment_371287

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:

andrew.taylor37161

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.

andrew.taylor37161

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

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

Genevieve P.

Hey @andrew.taylor37161

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/371546#Comment_371546

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