Problem in totalling columns in excel due to format issue

[ramramji]

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]

@ramramji 

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?

[#3]

@ramramji 

Getting data from a PDF mostly results in extra (non breaking) spaces. Use TRIM()/SUBSTITUTE() to remove those spaces.