none
Еxcel 2016 query editor RRS feed

  • Question

  • How to convert text type to number in query editor in excel 2016, It is giving problem "DataFormat.Error: We couldn't convert to Number."
    Friday, June 17, 2016 12:52 PM

Answers

All replies

  • Hi Datoyan. If you look at the step prior to the one reporting this error, do you see any non-numeric values in the column?

    Ehren

    Friday, June 17, 2016 9:11 PM
    Owner
  • Hi Datoyan,

    Adding to what Ehren has said, you could try adding a custom column with some code similar to the following:

    try Number.From([ColumnName]) otherwise null

    Or if you're happy to substitue these values with 0 then 

    try Number.From([ColumnName]) otherwise 0

    Either of the above substitute values can be converted to numbers.

    The benefit of substituting the non-numeric values for null (i.e. unknown) is that you could then more easily find values causing the conversion error (i.e. by filtering on 'null') assuming that nulls are less common than 0s in you data.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, August 9, 2016 7:27 AM
    Moderator