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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Todd Ray

    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

  • Dan W
    Dan W ✭✭✭✭✭
    Answer ✓

    @Mike TV

    @Todd Ray

    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Answer ✓

    @Todd Ray

    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

  • Dan W
    Dan W ✭✭✭✭✭

    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

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Todd Ray

    =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.

  • Dan W
    Dan W ✭✭✭✭✭
    Answer ✓

    @Mike TV

    @Todd Ray

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!