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

Peter
Peter ✭✭
edited 12/09/19 in Archived 2016 Posts

Hi

 

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

 

Thanks

Peter

· Share on FacebookShare on Twitter

Comments

  • Greg Long
    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...Frown  Maybe an enhancement request...

    · Share on FacebookShare on Twitter
  • Dave D
    Dave D ✭

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

     

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

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Peter,

     

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

    Can you share?

     

    Craig

    · Share on FacebookShare on Twitter
  • Peter
    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.

     

     

    Unbenannt.JPG

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Peter,

     

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

     

    Craig

     

    · Share on FacebookShare on Twitter
  • Peter
    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. 

    · Share on FacebookShare on Twitter
  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Agreed.

     

    Craig

    · Share on FacebookShare on Twitter
  • Paul Dewhurst
    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.

     

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories