C
claytorm
Copied below is the the original problem, converting text to numbers
Gord, thanks for your reply. I tried this on the same data (which i
included as an attachment) on my machine running Excel 2000 on Window
2000, this returns "No cells found!". However, when I ran it on anothe
machine running Excel 2002 on WinXP, it ran fine, returning just th
results I wanted.
Is this likely to be a difference between the Excel versions, o
something wrong with my settings? I could upgrade to Excel 2002, bu
this would be a last resort, can anyone suggest a workaround?
Bertie.
This worked for me on your sheet.
Sub NumFormat()
Dim cel As Range
On Error GoTo endit
For Each cel In Selection
cel.Value = cel.Value * 1
cel.numberformat = "0.00"
Next cel
Exit Sub
endit:
MsgBox "No cells found!"
End Sub
Gord Dibben Excel MVP
Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65631
Gord, thanks for your reply. I tried this on the same data (which i
included as an attachment) on my machine running Excel 2000 on Window
2000, this returns "No cells found!". However, when I ran it on anothe
machine running Excel 2002 on WinXP, it ran fine, returning just th
results I wanted.
Is this likely to be a difference between the Excel versions, o
something wrong with my settings? I could upgrade to Excel 2002, bu
this would be a last resort, can anyone suggest a workaround?
Bertie.
This worked for me on your sheet.
Sub NumFormat()
Dim cel As Range
On Error GoTo endit
For Each cel In Selection
cel.Value = cel.Value * 1
cel.numberformat = "0.00"
Next cel
Exit Sub
endit:
MsgBox "No cells found!"
End Sub
Gord Dibben Excel MVP
Thanks for your replies guys. I finally used Gord Dibben's solution
(Data>Text to Columns>Next>Next "column dat format">Date>DMY>Finish.)
which worked well.
On a related theme...
I still have a problem with some numbers imported as text from th same
source. The cells are formatted General, and have 19 spaces after the
last digit in each. I have tried several solutions to clean the data
and convert it to a number with no success.
Some of the solutions I've tried already:
I have tried CLEAN, then using VALUE on the result to convert to a
number, but this returns #VALUE!
I have also tried find and replace on the spaces.
I have also tried various macros recommended elsewhere.
My thinking is that the spaces are in some way not normal. I attach a
sheet with example data.
Any comments appreciated.
Attachment filename: egdata.xls
Download attachment http://www.excelforum.com/attachment.php?postid=655942
Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=65631