Forum Discussion
Problem in totalling columns in excel due to format issue
I have a large database containing two files and I am trying to merge them through "Index , match" function and then sum up values in a resultant column. The data from both files is from a PDF file converted to Excel. But whatever i do the totalling column remains "zero" and doesn't accept any changes through "change format" option. The only way it works is if i type the same value manually, but that would be impossible for 10000 records. I tried looking at all format options but without success . the number is always preceded by '(single quote) mark. (I am unable to attach actual file. Not sure why. I don't get "attach File" option
2 Replies
Getting data from a PDF mostly results in extra (non breaking) spaces. Use TRIM()/SUBSTITUTE() to remove those spaces.
Try the following:
- Select a single column with numbers that won't format.
- On the Data tab of the ribbon, click Text to Columns.
- Select Delimited, then click Finish.
Does that convert the values to real numbers?
