Convert text to number

Peter

Hi

 

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

 

Thanks

Peter

Greg Long

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...

Dave D

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

 

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

J. Craig Williams

Peter,

 

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

Can you share?

 

Craig

Peter

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.

 

 

J. Craig Williams

Peter,

 

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

 

Craig

 

Peter

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. 

J. Craig Williams

Agreed.

 

Craig

Paul Dewhurst

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.