Export report from software to excel and cells don't work with sumifs and vlookup

Hi everyone,

I'm from Australia and we use an accounting software that can export data in excel format which is fine.

However, when I want to work with the data in the cells the cells don't work, like if the export gave them a weird format that I can't see and I can't get rid of.

I have tried everything, copy/paste special, copy/paste to new excel sheets, trim, concatenate, &, clear format and I cant solve the problem.

Please find attached 2 files showing a small example.

1. MYOB sample - is the file I am working with column A and B, and the rows 7 - 12 have this weird problem but rows 15, 16 and 17 work just fine
2. COASUM1 - MYOB - is the actual export from this software called MYOB

If someone could help me making rows 7-12 workable for sumifs and vlookups I would really really appreciate it

All the best,

Hi guys,

Thank you for your prompt replies.

bebo021999, I tried your suggestions and it didn't work

benishiryo, I am attaching the current file I am working on (i tried to delete and hide a lot of unuseful information). - MYOB sample II

Basically, I want the formula on "FI7" to return an amount using sumifs that are reading from "A7" (concat) that is reading from the weird cells in "C" and "D" ("C7" and "D7" are correctly being read by the formula)

To illustrate my point, I copy/pasted the same information that is working row 7 but in the weird cells format in row 9 and the formula is not working.

thank you so much for your help!

Thank you for the prompt reply

I am attaching "MYOB sample II" file I am working on (I tried to delete and hide a lot of unuseful information)

Basically, I want the formula on "FI7" to return an amount using sumifs that are reading from "A7" (concat) that is reading from the weird cells in "C" and "D" ("C7" and "D7" are correctly being read by the formula)

To illustrate my point, I copied/pasted the same information that is nicely working row 7 but in the weird cells format in row 9 and the formula is not working.
Its like row 9 is possessed, like the cells are dead or useless(also I am attaching part of the export from the MYOB software)

Thanks again for your help!