Can numbers as TEXT be turned into a NUMBER value?
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
Best Answer
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Thank you Jeff!!!
-
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)?
-
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
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
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.
-
How do I mark it as accepted and give it a value?
I clicked on the "Awesome" link. Does this do it?
-
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
-
You got me my "100 Awesomes" badge! Thanks!
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 290 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!