Problem in totalling columns in excel due to format issue

Question

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

hansvogelaar · Answer

ramramji&nbsp;
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?

detlef_lewin · Answer

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

Forum Discussion

Problem in totalling columns in excel due to format issue

Share

Resources