Separate numerical and text values of differing lengths from a single cell into separate columns
How do I separate numerical and text values of differing lengths from a single cell into separate columns? For example, I'm trying to total the "AREA" Column, but due to having "SF" in the cell, it will not return a numerical value. The numerical values to the left are of differing lengths, making this problematic. The provided image here is what I have done manually, but I would like for this to be in the form of a formula or function.
Best Answers

Here's a method. Example sheet:
First you have to find the # of characters in the Area column with this formula in the # Chars column:
=LEN(Area@row)
Then you can remove the " SF" from the field with this formula in the Area Value column:
=LEFT(Area@row, ([# Chars]@row  3))
However, SmartSheet is going to treat that Area Value column as text. So if you want to do further calculations on the Area Value column results you'll need to do something like this which is in the Further Calculations column:
=VALUE([Area Value]@row) / 10

I see you got it worked out
Yes I did not account for the space between the numbers and SF so adding a space in " SF" works just fine. Cheers!
=VALUE(SUBSTITUTE(AREA@row, " SF", ""))
Answers

Here's a method. Example sheet:
First you have to find the # of characters in the Area column with this formula in the # Chars column:
=LEN(Area@row)
Then you can remove the " SF" from the field with this formula in the Area Value column:
=LEFT(Area@row, ([# Chars]@row  3))
However, SmartSheet is going to treat that Area Value column as text. So if you want to do further calculations on the Area Value column results you'll need to do something like this which is in the Further Calculations column:
=VALUE([Area Value]@row) / 10

Is it always SF? If so you could remove the SF with.
=VALUE(SUBSTITUTE(AREA@row, "SF", ""))
Then add the SF into UNIT with ="SF"

@Mike TV  Thank you very much for the assistance here. It all worked out as directed in your response except for the following formula =VALUE([Area Value]@row) / 10. I just had to change the "10" to "1", which worked perfectly for all the values.
@Dan W  It is always SF, but I get the following response #INVALID VALUE when trying to utilize the proposed formula. Any thoughts on why this may be occurring? Thank you

=VALUE(SUBSTITUTE(AREA@row, " SF", ""))
Dan W has a more elegant solution than mine. His method was broken because he forgot to take out the space with the substitute function. The above should work for you possibly better than my method of doing it.

I see you got it worked out
Yes I did not account for the space between the numbers and SF so adding a space in " SF" works just fine. Cheers!
=VALUE(SUBSTITUTE(AREA@row, " SF", ""))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 325 Global Discussions
 183 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!