Replace IF text with numbers automatically

Hello,

I have a survey coming in to a tracker. I would like to automatically replace the text answers to numerical values in my tracker.

I'd like the cell to look at its text content and replace it with specific numbers:

So if for the "communication accuracy" someone has answered "D'accord/ agree" , smartsheet looks into that cell and says ok, I need to replace this with 4.

If it is "Ni en accord ni en..." then it should replace it with 3.

Is it possible?

thank you!

Find more posts tagged with

Accepted answers

Kelly Moore

Hey @Abnoos Moslehi

The formula will go in a new column, not the column where the text resides. You cannot overwrite/replace the input text cell. I'll call this new column [Returned Numerical Value]. You can name it whatever you like

There's a couple of ways to find the numerical value.

Option 1: If you have set up a reference (ie a lookup table) just like you presented in your screenshot you can use an index match. My personal preference would be to set up a separate sheet just like your screen shot. Because this would be a cross sheet reference, you cannot just copy-paste this formula in. You must create the reference.

=INDEX({your reference sheet Number column}, MATCH([your text response]@row, {your reference sheet Text reference},0))

This formula above says get the term you need (eg. your [Numerical Value]) when a response (your text field) matches your reference table text.

Option 2: If you don't want to set up some columns or a different sheet to contain the reference information, we can use a nested IF in your [Returned Numerical Value] column. If you get more than these 5 responses, I would strongly recommend building a reference table as the Index/match is cleaner to write and significantly easier to update.

=IF([your text column with the communication answer]@row<>"",IF([your text column with the communication answer]@row="your response #1", 1, IF([your text column with the communication answer]@row= "your response #2", 2, IF([your text column with the communication answer]@row= "your response #3", 3, IF([your text column with the communication answer]@row= "your response #4", 4, 5)))))

Replace your [text column] name in the above formula, as well as inserting the phrases (exact spelling and it is case-sensitive).

cheers

All comments

Kelly Moore

Hey @Abnoos Moslehi

There's a couple of ways to find the numerical value.

=INDEX({your reference sheet Number column}, MATCH([your text response]@row, {your reference sheet Text reference},0))

This formula above says get the term you need (eg. your [Numerical Value]) when a response (your text field) matches your reference table text.

Replace your [text column] name in the above formula, as well as inserting the phrases (exact spelling and it is case-sensitive).

cheers

Abnoos Moslehi

Thank you @KDM

You made my Monday!😎

Kelly Moore

Very happy to help. Thanks for contributing your question to the community.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.