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

  • 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

Rafay Ahmed

New Member
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
 

Attachments

  • Template.xlsx
    9.1 KB · Views: 10
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.
 
"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.
 
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.
 
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.
Its not working. Issues in the sum total
 
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.
Yes this works, but I was in search of something better than this
 
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))
 
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))
I can not see the green marker, I have office 2016
 
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
 
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
 
Back
Top