error importing excel cells starting with equals sign

[Luis_Ma]

I am loading Excel files from a folder in power query.

In one of the text columns, some records start with "=".

Example: "= Number of people"

This produces a #Name type error. When opening the file in excel the error is displayed in the cell. And when trying to read the file in power query the error is transferred.

Is there a way in Power Query to remove, or replace, the equals sign from the column, avoid the error, and import the text?

I would like to import: "Number of people" or "Equal Number of people" in my example.

I understand that the solution must be related to being able to read the formulas of an excel table as if they were text, even if they are incorrect.

Has anyone had this problem and fixed it?

Any ideas?

Thanks

[Luis_Ma · Accepted solution]

Hello:

I have solved the problem whit this macro based on @Vijay_A_Verma proposal:

https://1drv.ms/x/s!ApCA2Iz4chbHmDbhSxSdK4OoEPbn?e=Mu7CzX

I hope it helps people with the same problem.

[Luis_Ma]

Thank you very much Vijay.

I will implement your solution in a VBA macro.

It's a shame that the Excel.Workbook function doesn't have more parameters. Or return more information besides "data" and "kind". It would be great if you could import: formulas, cell color, font type, etc.

So we could handle this problems at PQ level.

Maybe for future versions. 🤔

[Vijay_A_Verma]

PQ reads the value not formula. Hence, you can not handle this at PQ level. This has to be handled at source level only.

To handle at source level i.e. in Excel file (Save a backup of your Excel file first)

1. Select the column

2. Press CTRL+G to open Go To Window and click Special

3. Click Formulas and check only Errors and OK. This will select only error cells (if you are having other errors also, then those will also get selected. This is an unintended outcome of this)

4. CTRL+H and put = in Find what: box and press Replace All