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

Doyle54

No Text Function that I can find.

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

One Solution is not pretty but seems to work:

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

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

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

'09 displays as 09 (text)

Doyle54

Both Excellent Answers! Thanks. Great Weekend to You.

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.

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

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

@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

@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

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