Why does SS not have a Text Function that allows numbers to be converted into Text? So simply.

Doyle54
Doyle54 ✭✭✭✭✭

No Text Function that I can find.



Answers

  • Doyle54
    Doyle54 ✭✭✭✭✭

    I need to create a location value with a consistent length - Section - 2 digits. Township - 3 digits. Range - 2 digits.

    When Section is less than 10 it creates a single value which makes it difficult to generate statistics and compare locations. How do I convert value - 9 to Text - 09?

  • Doyle54
    Doyle54 ✭✭✭✭✭

    One Solution is not pretty but seems to work:

    =IF(Section8 < 10, LEFT(DATE(2020, Section8, 10), 2), Section8)

  • Rob Hagan
    Rob Hagan ✭✭✭

    Where you know that the number will always be between 0 and 99 then

    • =IF(Number@row < 10, "0", "") + Number@row

    Where you know that the number will always be between 0 and 999 then

    • =IF(Number@row < 10, "00", IF(Number@row < 100, "0", "")) + Number@row
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Entering an apostrophe before the number will convert it to text and maintain the leading zero.

    '09 displays as 09 (text)

  • Doyle54
    Doyle54 ✭✭✭✭✭

    Both Excellent Answers! Thanks. Great Weekend to You.

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭

    The TEXT() function is LOOOONG overdue! Please Smartsheet, an update to some additional handy intrinsic functions should be in the 'Coming Soon' Roadmap - they can't be that difficult to code.

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Adrian @ Chess They do have an ISTEXT function that can output a true/false value if something is text (and ISNUMBER and ISDATE functions).


    To convert something into text, all you have to do is "add" a set of quotes.

    ="23"

    or

    =23 + ""

  • Adrian Mandile CHESS
    Adrian Mandile CHESS ✭✭✭✭✭

    Thanks @Paul Newcome@Cleversheet, but no.

    I'm after the flexibility that Excel has had since day dot. To format text from a cell however I want.

    Using the following function syntax:

    =TEXT (cell, format)

    So, if cell value is date of 10/10/2018, I can format it like "dddd dd ddd, yyyy" to get "Wed 10 Oct, 2018"

    Or if cell value is 7135551234, I can format it as a phone number "(###) ###-####' to get "(713) 555-1234"

    Adrian Mandile
    CHESS Consulting Australia - Smartsheet Solution Provider Gold Partner
    Collaborative | Holistic | Effective | Systems | Solutions

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Adrian @Chess Have you looked through the Ideas topic to see if the product enhancement has been suggested? If it has, you can vote on it and see it's roadmap status there. If it has not, you can submit it yourself and allow others to vote on it letting Smartsheet know you're interested in it.


    I'm sure they'd also welcome assistance with the coding portion as well since you seem to have some insight on the (lack of) complexity.

  • Cleversheet
    Cleversheet ✭✭✭✭✭✭

    @Adrian @Chess, I agree with @Paul Newcome's suggestion to ensure that's among the product enhancement requests. If you'll let us know the link to that entry I'll be happy to upvote it, as I'm sure Paul will as well.

    I do agree with you it'd be a great addition to the function list.

  • John C Murray
    John C Murray ✭✭✭✭

    You can simulate some of Excel's TEXT function for using a combination of LEFT(), a static string, and the LEN() of the data you want to work with.


    Say you have a column "Serial" which contains integers ranging from 1 to 999999. To left pad with zeroes use something like this:

    = LEFT( "000000", 6 - LEN( [Serial]@row ) ) + [Serial]@row

    so a value of 3231 would look like 003231. The 6 in the formula is the length of the static string "000000"


    If you wanted to right pad you would use something like:

    = [Serial]@row + RIGHT( "000000", 6 - LEN( [Serial]@row ) )

    A value of 3231 would look like 323100 in this case