Can't Convert Text to Numbers

manchuwok

New Member
Joined
Apr 17, 2013
Messages
6
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Format changes won't help if the value, as in the actual ASCII characters in the cell, are text based. This happens to me all the time and you need to either Convert the string [CDBL()], or at the very least do as you've done there (which TBH normally works for me - telling the cell value that it is the cell value forces a conversion/check)

Happens all the time with database exports. Thought if your 'cell.value = cell.value' doesn't at least change something.

Not sure I've helped at all, but try 'xcell.value = cdbl(xcell.value)' as see how you get on?
 
Upvote 0
Can you post some examples?
Don't write them by hand, go ahead and actually copy them from XL and paste them into your reply.
This way, any extra characters should be included in your post.
 
Upvote 0
Yeah - so it genuinely can't see it even being a number. Can you do this:

Add the line:
Code:
Debug.print xcell.address
into your procedure just before the line with the CDBL in it.... then in the VBE window hit Ctrl+G to bring up the immediate window.

Now run it again, what address does it stop on? A particular one? Or the first one?
 
Upvote 0
‎100
‎50
‎150
‎100
‎50



<colgroup><col style="width: 48pt;" width="64">
<tbody>


</tbody>
‎100
‎50
‎150
‎100
‎50

<colgroup><col style="width: 48pt;" width="64">
<tbody>


</tbody>
 
Upvote 0
That leads to a Run-time error '13':
Type mismatch
Let's try to narrow this down. Start with the following macro (which works for most cases for me) and tell us what happens...
Code:
Sub Test()
  Selection.Replace Chr(160), "", xlPart
  Selection.NumberFormat="General"
  Selection.TextToColumns
End Sub
 
Upvote 0
Let's try to narrow this down. Start with the following macro (which works for most cases for me) and tell us what happens...
Code:
Sub Test()
  Selection.Replace Chr(160), "", xlPart
  Selection.NumberFormat="General"
  Selection.TextToColumns
End Sub

That macro runs without error, but nothing changes. It seems that each of the things it is doing are the same as methods I've tried (above), perhaps.
 
Upvote 0
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
 
Upvote 0
Yeah - so it genuinely can't see it even being a number. Can you do this:

Add the line:
Code:
Debug.print xcell.address
into your procedure just before the line with the CDBL in it.... then in the VBE window hit Ctrl+G to bring up the immediate window.

Now run it again, what address does it stop on? A particular one? Or the first one?

First one.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top