Cleaning data format or date type exported from software i.e. Tally (Accounting software)

Hello everyone.

I have exported an excel file with invoice number and amount from my accounting software and I have some trouble cleaning some cells with a certain data type.

On the attached excel file, in Column D the data were in General format, and I have changed it to Number format. In column J I cleaned the data by multiplying the cell into 1 for all the data in that column.

However if you see in Column J there are some data which shows #VALUE!. I am unable to clean this data and it becomes difficult to reconcile them as I cannot use a formula or functions on these cells. Please advise how to clean them.

A bit of context, the cells in Column D are invoice reference numbers which I have typed manually in tally when referring supplier invoices.
Some of the cells show #VALUE! it is because for these cells considering there were a lot of invoices I had highlighted the text in supplier invoices and copied it to tally instead of manually typing them out. Now I am unable to clean them when i exported the data in excel.

Please help.

@TMS

Thanks. Can you advise how to clean the data? Do I have to delete the code or character 10 from the formula or something.

Thanks in advance.

Hi John.

Can you advise how I should the same for future reports.

I am not an expert in excel but I know we have to go to developer options, insert the module and save as macro. How do you run it on the file?
Tried your code in another sheet but it did not work out correctly. [/B]

Thank you

Hi John.

Could you please check the file I attached.

Was trying to clean the data as per method mention but it did not work on this file.

I want to clean up column D and column I.

Please help.

Many thanks
Kunal