Sign in to join the conversation:
The posts in this section are historical and no longer monitored for accuracy. If this discussion interests you and you'd like to join in, please visit the active Community to post and engage.
Hi
is there a way to convert a text to a number like: "One" to 1. VALUE() doesn't work.
Thanks
Peter
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...
Only way I would think is to build a massive nested IF statement.
=IF(Cell1 = "One", 1, IF(Cell1 = "Two", 2, etc...
Peter,
I'm interested in how you are intending to use this.
Can you share?
Craig
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.
You probably won't notice a speed hit for 10 columns.
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.
Agreed.
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.
