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

Data Not Being Recognized

T

Tom90

Member
Hi can any help me as I think I am going mad, in all my time working with excel this is the first time that something like this has happened to me and I guess it will be something simple but I cannot figure it out so help.
I am copying data from an export file from SharePoint in table form into an excel file and for some reason it will not read the number data I cannot sum it or get a formula to work on it, I know that it may come from the SharePoint Export as text but I have tried to recopy it and save as Special values I have also tried everything that I can think off but it will still not work can anyone help
Thanks Tom90
 
Is the cell you are importing in to the right format?
import the data and then in a cell put =istext(your cell reference) this will return true if the data is still none numerical.
 
Paste the value 1 in Blank cell
Copy the cell
Select the columns of numbers
Paste, Paste Special, Multiply by


Also Goto the Formula, Calculation Options tab
Make sure Calculation is set to Automatic
 
Hi,
In addition to above suggestions... can you post your file?
So users can look around and give you better ideas.

Regards,
 
Hi,

In addition to above please check if there are extra spaces. Just go to that cell and press F2, wherein you may find extra spaces which are supposed to be removed in order to work fine.

If that is the case just create an extra column mention =Trim(a1)--copy--paste special--values

Extra spaces will be removed.



Note: A1 is the cell name and it may differ in your file so choose accordingly.
 
Hi all have tried all that you are saying and yes it is “text value” but I am still not too sure how to convert it to numbers, Hui I have tried what you are saying and it does convert it to numbers but when I copy and paste special values my data back into the column it goes back to text, and as I have said I have tried all the format changes but that is not working can you help me further on how to change the sheet or column into taking numbers that I am pasting into or that are in the column already, Tom90
 
It would help if you upload a copy of your work book, then members could see the actual problems instead of scrabbling around in the dark.
 
Hi as suggested I have uploaded my file all the columns are ok except column ”F” which for some reason is text and no matter what I do I cannot convert it to number so I can use formula and add the column, what I am looking to is retain the info but convert it to numbers so I can work with it, Tom90
 

Attachments

From F2 selected whole column range, you then see on the left of F2 the small warning down arrow select and choose convert to number, then fixed it in the ribbon/number section
 
Bob sir solved your problem,
Another way is using Text to Columns.

Select your range F2:F396
Go to Data > Text to columns > Next > Next > Finish
 
You must log in or register to reply here.
Back
Top