#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Convert text to number

Options
✭✭
edited 12/09/19

Hi

is there a way to convert a text to a number like: "One" to 1. VALUE() doesn't work.

Thanks

Peter

• Options

I'm thinking no, I've tried to get your scenario to work with REPLACE, and SUBSTITUTE as well as VALUE with no luck...  Maybe an enhancement request...

• Options

Only way I would think is to build a massive nested IF statement.

=IF(Cell1 = "One", 1, IF(Cell1 = "Two", 2, etc...

• ✭✭✭✭✭✭
Options

Peter,

I'm interested in how you are intending to use this.

Can you share?

Craig

• ✭✭
Options

My problem was a feedback form. I wanted feedback with 0 - 5 Stars by each participant. I need the average for the whole event. The value of the stars field ist a text ("One", "Two" ...) - for the AVG function I need the number 1, 2 ...

I did it with the IF statement:

=IF([War das Thema informativ?]32 = "Eins", 1, IF([War das Thema informativ?]32 = "Zwei", 2, IF([War das Thema informativ?]32 = "Drei", 3, IF([War das Thema informativ?]32 = "Vier", 4, IF([War das Thema informativ?]32 = "Fünf", 5, 0)))))

Now I have 10 columns with this complex IF statement in many rows. I think that won't be good for the speed - but don't know it.

• ✭✭✭✭✭✭
Options

Peter,

You probably won't notice a speed hit for 10 columns.

Craig

• ✭✭
Options

many thanks ...

I think it would be much easier if the name of Symbols is a number. It's often needed to calculate with this values - progress bar for instance.

• ✭✭✭✭✭✭
Options

Agreed.

Craig

• Options

I can't imagine the design logic that went into design a symbol for stars, dollars etc that resulted in the use of text as the output. It makes no sense at all. I have so many use cases where I want to multiply them, like risk assessment that I have to create a compound if statement to fix. This has to be an easy one to sort out.

This discussion has been closed.