Forum Discussion

Sameer_Kuppanath_Sultan's avatar
Sameer_Kuppanath_Sultan
Brass Contributor
Mar 10, 2020
Solved

Could not Convert numbers in text to Numbers

hi All   Please find the attached sheet in which the numbers are mentioned in text could not be converted to Numbers. I have tried following methods.  1. Trim 2. Value Method 3. Error Checking M...
excel
  • ExcelExciting's avatar
    ExcelExciting
    Mar 10, 2020

    Hi Sameer_Kuppanath_Sultan 

     

    You data is having a special character, to handle this scenario use the below formula 

    =VALUE(SUBSTITUTE(C3,CHAR(CODE(C3)),""))

     

    You can also check link for more details explanation : https://youtu.be/dgUu24eUzWw

     

    Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

    If you find the above solution resolved your query don't forget mark as Official Answer.

Patrick2788's avatar
Patrick2788
Silver Contributor
Mar 10, 2020

Sameer_Kuppanath_Sultan 

Every so often I'll see this in a client's workbook.  Usually it's when data is exported from a non-Office app to Excel.  Glad you got some good solutions and resolved it.

 

Whenever I encounter these I'm curious as to what special character is being used.  CLEAN will often be ineffective in removing it.

 

This what Word revealed:

 

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Mar 10, 2020

    Patrick2788 

    Yes, CLEAN and TRIM are only for the first part of ANSII table.

Resources