Can't Convert Text to Numbers

[manchuwok]

I am having significant trouble converting a column full of numerical text into numbers. Here are all the methods I tried, which have all failed:

- Changing cell format
- Paste Special multiply
- VALUE() function
- Error Checking (they are no recognized as numbers stored as text)
- Replacing hidden characters (ALT-0160)
- TRIM/CLEAN functions
- Text to Columns command
- VBA Macro:
Sub Enter_Values()
For Each xCell In Selection
Selection.NumberFormat = "0.00"
xCell.Value = xCell.Value
Next xCell
End Sub

None of these have worked. Why is this so hard? What is the one simple thing I'm missing?

 

[manchuwok]

There is a hidden character in those..
The first character is ascii code 63, which turns out to be a ?
Will be hard to use find replace for that, since it thinks it's a wildcard..

This works though

=RIGHT(A1,LEN(A1)-1)+0

Indeed it does. Thank you very kindly. I had tried your exact formula without the +0, and it didn't work. I had tried +0 without the formula and it didn't work. Together it seems to do the trick!

 

[vds1]

My try,

=VALUE(SUBSTITUTE(A1,LEFT(A1,1),""))

 

[Jonmo1]

Glad to help..

The RIGHT function returns a TEXT string, so you're still in the same boat having Non Numbers.
The +0 converts it to Number.

 

[West Man]

An alternative =REPLACE(A1,1,1,"")+0

 

[daveismissing]

There is a hidden character in those..
The first character is ascii code 63, which turns out to be a ?
Will be hard to use find replace for that, since it thinks it's a wildcard..

This works though

=RIGHT(A1,LEN(A1)-1)+0

Wow
Thanks for that.
I have been fighting a problem some months identical to manchuwok's where none of the regular solutions worked.
Registered to convey my appreciation to the poster and site.
D