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

Excel not reading numbers

A

Abhiroop

New Member
Hi,


i am stuck on a particular point. The problem i am facing is that excel is reading only certain numbers from a column of numbers and ignoring the remaining numbers.


as an example, i have the following numbers :

1000

2000

3000


The problem is excel only reads the number 2000 and not the others. If i sum up all three then the sum appears as 2000.


i have tried changing the format of the cell and pasting the entire column as values but the numbers are still not being recognised.


Any help would be appreciated.


Thanks!

Abhiroop
 
Abhiroop


It sounds as if your numbers are text instead of Numbers


To fix

Type the value 1 into a blank cell somewhere

Select the cell and Copy (Ctrl C)

Select the column of Numbers

Paste Special, Paste Special (Ctrl Alt V)

Select Values,

Select Multiply,

Apply
 
Hi Abhiroop ,


If your numbers are in column A , then in the corresponding cells in column B , put in the formula :


=TYPE(A2)


assuming that your numbers start from cell A2 , and copy it downwards.


Where ever you get 1 , there the data is numeric.


If your cells have been formatted as TEXT , and then you change the format to NUMBER , the change does not take effect till you enter a number in that cell e.g. if the cell A2 had been formatted as TEXT , and had the number 1000 in it ; now , if you change the format to NUMBER , the change will take effect only if you re-enter the number in that cell. Or you can press F2 and then ENTER.


Narayan
 
Hi Narayan,


I followed your procedure wrt to the formula =TYPE(A2) and to numbers that excel wasnt reading are appearing as 2 and the numbers that excel is reading is appearing as 1.


However, when i try to change the cells from text to numbers, there is no change. I did it in the following way :

1) Changed the format of the cell to number

b) F2

c) enter


It has made no difference. Excel still does not read the numbers.


Thanks!

Abhiroop
 
Hi Abhiroop,


Can you upload a sample file with this data??


you can use a helper column with formula
Code: 
=Value(Cell_Ref) to get the values as numbers and then sum or use directly, assuming your data is in A1:A12 the you can use


=SUM(VALUE($A$1:$A$12)))


Press Ctrl+Shift+Enter.


Regards,

Faseeh
 
Hi Abhiroop,


Can you please give a try..


* Select column A.

* Data > Text To Column.

* Next & Finish..


Now all your data is Numeric..(I hope so..)


Please let us know.. If it works..


Regards,

Deb
 
Hi,


If you take Hui's advice, but make sure the cell where you type the number 1 is formatted to Number, then it should work
 
Faseeh : the link for the sample work book is : https://www.dropbox.com/s/inkasel5cl9u02c/sample%20for%20upload.xlsx


Debraj : Debraj, I have tried that and unfortunately it doesnt work.


Thanks!

Abhiroop
 
Hi Abhiroop,


In E3, put this:


Code: 
=VALUE((SUBSTITUTE(D3,{",","."},"")))


Press Enter and drag down, now you can use column E values as usual.


Regards,
 
@Faseeh : I cant thank you enough man! immense help. My problem sorted and now i shall get back to doing whatever i was doing except in probably 1/3rd the time because of your help.


@Raja : I never realised about the special characters.


the "," and the "." could be mistaken to be a part of how numbers are formatted in excel (Alt +9 and Alt + H + K). had i realised that "," and "." was the problem i would not have posted this question in the forum and waited for 3 hours.

now that i know the level to which i need to specify the data i will make sure i do it the next time.


Faseeh again, Thanks a ton! appreciate it!
 
You must log in or register to reply here.
Back
Top