• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Convert Text to Numbers

We're going to use Find and replace to do this in bulk. what looks like spaces in your data are actually characters with ascii code 160.
1. Go and edit say cell A2, and in the formula bar, using the keyboard cursor keys and the shift key select one of those characters, say the 3rd one in that cell, then on the keyboard press Ctrl+c which will put that single character into the clipboard.
Now Escape from editing that cell.
Now select the area on the sheet - in your sample file that's A2:H34.
Bring up the Find & replace dialogue (with Ctrl+h), make sure both Find what: and Replace with: fields are completely empty, then in the Find what: field paste the clipboard contents in with Ctrl+v.
Now click Replace all and that should be that.
 
One line of code will do it:
Code:
Range("A1").CurrentRegion.Replace Chr(160), "", xlPart, SearchFormat:=False, ReplaceFormat:=False
 
@p45cal - info
I tested Your 'one line of code' and it won't work with my Excel version
( Named argument not found; 'SearchFormat:=' )
and those 'looks spaces' are Chr(202) with my Excel (2011) version.
Interesting differences with versions?
That's maybe Okay
 
Respected Sir.

Thank you very much for the Find and Replace solution. I have got the spaces removed, but the value is still text, it is not showing summation of the columns.

Kindly check this.

Thank you so much,

with regards,
thomas
 

Attachments

  • Convert Text to Number.xlsx
    15.1 KB · Views: 3
@p45cal - info
I tested Your 'one line of code' and it won't work with my Excel version
( Named argument not found; 'SearchFormat:=' )
and those 'looks spaces' are Chr(202) with my Excel (2011) version.
Interesting differences with versions?
That's maybe Okay
Then for a Mac (they always have to be different) it probably simplifies to:
Range("A1").CurrentRegion.Replace Chr(202), "", xlPart
 
Respected Sir.

Thank you very much for the Find and Replace solution. I have got the spaces removed, but the value is still text, it is not showing summation of the columns.

Kindly check this.

Thank you so much,

with regards,
thomas
The file you supplied with this message; does it represent you having removed spaces? (Because they're not.) Did you follow instructions to the letter?

Is your machine a PC or a Mac?
 
Respected Sir,

My machine is a PC.

I followed the instructions. kindly find the picture of the cell without space

upload_2016-2-7_20-18-47.png

I multiplied the value by 1 but I get #Value!

Kindly check,

Thank you so much,

with regards,
thomas
 
@Thomas Kuriakose
If same file that before #6 Reply,
please check 'spaces' before and after 185.000
and
after that use p45cal's
Code:
sub xx()
  Range("A1").CurrentRegion.Replace Chr(160), "", xlPart, SearchFormat:=False, ReplaceFormat:=False
end sub
 
Respected Sirs,

My mistake, apologies, I checked and found the spaces after the numbers were not removed. The replace field got copied with the find characters.

Thank you so much for this solution provided.

Thanks once again,

with regards,
thomas
 
Respected Sirs,

Thank you all for the number of solutions provided to his problem.

This is an amazing forum, with gems who have an undying spirit to resolve any problem and for sharing their valuable knowledge with all.

Much appreciated and thank you once again.

with regards,
thomas
 
Back
Top