Forum Discussion
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 Method
4. Text to Columns method
Nothing worked out. Please help me.
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
6 Replies
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:
Yes, CLEAN and TRIM are only for the first part of ANSII table.
Or manually
Select range->Ctrl+H->Alt+0160 in Numpad for Find what:->Replace All
Thanks Guru- Its also worked outSergeiBaklan
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
You are a magicianExcelExciting
