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

Options
Doyle54
Doyle54 ✭✭✭✭✭

No Text Function that I can find.



Answers

  • Doyle54
    Doyle54 ✭✭✭✭✭
    Options

    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 ✭✭✭✭✭
    Options

    One Solution is not pretty but seems to work:

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

  • Rob Hagan
    Rob Hagan ✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

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

    '09 displays as 09 (text)

  • Doyle54
    Doyle54 ✭✭✭✭✭
    Options

    Both Excellent Answers! Thanks. Great Weekend to You.

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

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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 ✭✭✭✭✭
    Options

    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"

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @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 ✭✭✭✭✭✭
    Options

    @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 ✭✭✭✭
    Options

    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