1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert a given Text to Number

Discussion in 'Ask an Excel Question' started by Rafay Ahmed, Nov 13, 2017.

  1. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    Hi All,
    Attached is the sheet, where I am not able to convert the given value (I can not clearly identify its a text or something else) to number sue to which I am not able to calculate totals.
    Thanks

    Attached Files:

  2. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    These are all texts. You will notice green marker to top left of each cell. When you hover, it will indicate number stored as text.

    Just select entire range with that marker and convert to number.
    Thomas Kuriakose likes this.
  3. David Evans

    David Evans Active Member

    Messages:
    647
    "Reporting" formats from external systems have a habit of converting everything to text - there are probably better ways of bringing that information into Excel, perhaps by linking the underlying data to a Pivot Table, which will get around this annoyance.
  4. p45cal

    p45cal Well-Known Member

    Messages:
    884
    I'm on Excel 2010 and I don't get the green markers, however, if I select all the numbers and Find & Replace (Ctrl+H) $ with nothing, all the texts are converted to numbers.
  5. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    Its not working. Issues in the sum total
  6. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    Yes this works, but I was in search of something better than this
  7. p45cal

    p45cal Well-Known Member

    Messages:
    884
    What kind of thing were you looking for?
  8. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,805
    Demonstrate how it isn't working. Simply stating something doesn't work won't get you any better help.

    FYI - I tested my solution and worked fine on my end.

    If you want to you can use formula to convert text to number, but I don't recommend it.

    Ex: =SUM(VALUE(C2:C11)) Confirmed as array (CSE).
    Or =SUMPRODUCT(VALUE(C2:C11))
    David Evans and Thomas Kuriakose like this.
  9. Rafay Ahmed

    Rafay Ahmed New Member

    Messages:
    15
    I can not see the green marker, I have office 2016
  10. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    Rafay
    1. You have described what you can't see, not what is wrong with the techniques presented by the people above

    2. In Excel 2016, goto the File, Options, Formula menu
    make sure it is marked liked this
    upload_2017-11-14_14-8-25.png
  11. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,772
    To convert to Numbers

    Type the value 1 anywhere
    Copy that cell with the 1 in it
    Select the Range C2:F11
    got the Home, Paste, Paste Special Tab
    Click Values, Multiply and ok
    upload_2017-11-14_14-11-44.png

    In C12 type: =SUM(C2:C11)
    Copy across

    Enjoy

Share This Page