Share via

Excel Query, Some Number Cannot Convert to Number

Anonymous
2021-01-05T03:28:02+00:00

I have a column which list all number, but some rows become error when I change the type of this column as Number

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Vijay A. Verma 104.7K Reputation points Volunteer Moderator
    2021-01-05T05:04:08+00:00

    It simply means that it contains some invisible characters which can't be converted into numbers. 

    In Transform tab - Format - Run Trim and then Clean and see if the problem goes away.

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2021-01-07T09:40:04+00:00

    Hi Kenny KW,

    Welcome to post back if you still need assistance.

    Regards,

    Clark

    0 comments
  3. Anonymous
    2021-01-07T09:54:56+00:00

    It simply means that it contains some invisible characters which can't be converted into numbers. 

    In Transform tab - Format - Run Trim and then Clean and see if the problem goes away. 

    I checked this cell "13607" is without any space before "1" or after "7", and I also run Trim before convert into number, but it still come out this error.

    0 comments
  4. Vijay A. Verma 104.7K Reputation points Volunteer Moderator
    2021-01-07T11:38:32+00:00

    In Power Query, do the CTRL+C of this cell.

    Close Power Query.

    In Excel, do CTRL+V to paste this value.

    Now let me know the output of these formulas (replace A2 with the cell where you pasted this value)

    =CODE(LEFT(A2,1))

    =CODE(RIGHT(A2,1))

    0 comments
  5. Anonymous
    2021-01-09T09:23:34+00:00

    Hi Kenny KW,

    Please feel free to post back if there are any updates.

    Regards,

    Clark

    0 comments