To ignore all numbers stored as text, you need to open the Data tab and select the Text to Columns option. Then, select the Delimited option from the Convert Text to Columns wizard.

To fix DataFormat.Error, We couldn't convert to Number, when working with data in Power Query, whether in Excel or Power BI use these solutions ...

Navigate to the "Number" group within the "Home" tab in Excel. Click the small arrow beside the "Number Format" box. Click "Text" to convert your cells to text formatting.