Excel @Text() Functionality in Smartsheet?

Is there a way in Smartsheet to use a formula that gives the same result as using @text() in Excel?

I have a spreadsheet that I need to concatenate several fields together and have the field then display as text. Most of what I see now for formulas is to covert from text into formula. But I need to go the other way?

Use case: I work at a hospital and most hospitals use pagers. The spreadsheet is an issues log and I need to take several fields and place them together in a cell that the notification then is used to send an email notification to 5551234567@txt.att.net

I realize that you can set the column heading as text, but that doesn't actually set the cell values to text.

This is an example of what the text page currently looks like:

Find more posts tagged with

Construction

Healthcare

Formulas

IT & Operations

Accepted answers

All comments

Shaine Greenwood

Hello,

I had to remove your example as the formatting didn't quite translate legibly into the context of your community post.

Currently, Smartsheet automatically treats values as text or numbers based on the characters in them. You can convert a number into text by adding an apostrophe to the beginning of it. You can either do this manually or by concatenating from other cell values with a formula. For example:

="'" + [Issue ID]1

There currently isn't a TEXT() or similar function to force the conversion of something into text.

More on building formulas can be found in the help center: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

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.

John C Murray

You can simulate Excel's TEXT function 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

Robert_at_work

I found an easy way to force Smartsheet interpret values as text, simply by concatinating it with an empty string.

For example, if you want to extract the year from a date, but as text, try:

= YEAR(created@row) + ""

This will lead to a text without any additional function needed.

PSExcellence

https://community.smartsheet.com/discussion/comment/440766#Comment_440766

THANK YOU you just helped me solve a broken "countifs" formula that was returning zeros. I created a helper column using your formula to recreate the values I am counting as "text", and now the countif works properly.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.