PDF Number formatting in Excel

[syedshah]

Hi

I need help with formatting PDF report in excel. I have system generated PDF reports, i have converted/exported those PDF reports in excel in order summarize the reports. In PDF table i have some negative numbers that do not add up when i sum up the table. i have used trim formula to make sure there is no space but still doesn't work.

Please see example above. Could someone please advise what should i do, how i can format these numbers so i can use formulas to sum up the table, rather deleting and then manually typing each negative number. Thank you

 

[earthworm]

use substitute formula to remove "-" sign and multiply by 1

example =substitute(cell value ,"-","")*1

 

[syedshah]

Thank you but this doesn't work. Plus i want to keep negative number as negative but want to format so i can add them up. Thanks

This is just an example i have big data with both positive and negative numbers. i can do manually but it take times. I need to format as normal so i can use sumifs formula to get total for each agency and do reporting. Thank you

 

[Fluff]

Try using text to columns on the data tab, delimited, next, clear all checkboxes, finish.

 

[syedshah]

Thanks, I have tried this and still doesn't work. When i sum up the numbers formula don't work.

 

[Fluff]

What do you use as the decimal separator?

 

[syedshah]

These numbers are imported from PDF to excel. Where there is no negative it fine i can add up those numbers. Wherever negative number come up formula doesn't work. Please see below when i remove minus sign and add back in (manually) it is fine, I can add up numbers and i can format those numbers. As reports data is big and have both negative and positive numbers, it is time consuming if i do manually. I was thinking if there is any solution.

I have copied and paste some numbers from report below. Please have a look and if you or someone can help that would be great. Thank you

‐24.28
‐23.67
‐23.67
8.58
8.58
8.58
8.58
9.06
9.06
9.06
10.66
10.66
10.66
4.87
4.87
4.87
4.87

 

[Fluff]

Ok, is you put this in a blank cell what does it return

=UNICODE(K7)