Exported report data into excel is visiually good but all numbers/values/dates are text

File opens up and states not in correct format, it's suggest corrupt but i know it's not, so open the file.

The file is an exported report from non excel program whcih visually is formatted with mutltiple headers, sub groups etc. However, all dates, numbers and values are as a text and cannot be read as values.

I have used the =value() before but only on a set of values that has no formatting
I can go into each cell (3,000) of them and "F2" it and its now a value
or
I completely undo all the formatting and cell merging etc, click each column and use text to column, i click TTC and then click finish. this gets rid of the text value and gives me the number, value or date. now i have to spend a long time to get the formatting back and if i grab the original data and copy paste sepcial as format, the formatting of the workbook is great, i dont get the text formatting back but the items are now off fomat so I have to select the columns i want to show date function, value and or number etc. the latter part is quick but the whole process seems way too manual.

Is there a way to select a workbook and convert what the excel file reads as a text string to a value without it being a very cumbersome approach as above, whilst keeping the file report format.

I need to be able to grab mutliple of the exported reports and summarise the values with formulas.

One fast way to do what you want one time would be importing data with Power Query, this would take care of numbers as text, for exemple, another way would be with VBA and the last would be using formulas, all depending much of the format of your file.

Thank you - I used power query (1st time) - "changed data type" , copied to new work book and i copied over the formatting from original file. took barely a few mins (once i knew what was what) I will now spend some time looking at power query in detail.

Appreciate the help.