Text to Number - Difference between Excel 2000 & 2002

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

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
 
C

claytorm

Dave,

Sorted. Thanks very much.

It turned out to be character 160 which is a non-breaking space, so no
modified by CLEAN. Thanks for the link to the Macros which did the jo
perfectly.

Berti
 
G

Gord Dibben

Dave

I did open the attached with Excel XP and Windows XP.

The code I posted totally ignored the 160's and converted to numbers correctly
as claytorm found on his XP setup.

Passing strange that the 2000 Excel and Windows OS needed the 160's stripped.

This would indicate some sort of OS or Office version functionality.

Gord
 
D

Debra Dalgleish

On my Windows XP machine, it ran without problems in Excel 2002 and
Excel 2000.

On my Windows NT machine, in Excel 2000, I got the error.

In both versions, on both machines, it ran without errors if I changed
the line to:

cel.Value = Replace(cel.Value, Chr(160), "") * 1
 
G

Gord Dibben

Thanks Debra.

Not Excel, but Windows NT doing the deed.

Gord

On my Windows XP machine, it ran without problems in Excel 2002 and
Excel 2000.

On my Windows NT machine, in Excel 2000, I got the error.

In both versions, on both machines, it ran without errors if I changed
the line to:

cel.Value = Replace(cel.Value, Chr(160), "") * 1
 
D

Dave Peterson

Anyone got a guess why the version of windows would make a difference?

I wouldn't have guessed this.
 
D

Debra Dalgleish

No idea, but in further tests, it works in Excel 97 on a Windows XP
machine, but not on a Windows 2000 Pro machine.
 
D

Dave Peterson

Thanks for the info.



Debra said:
No idea, but in further tests, it works in Excel 97 on a Windows XP
machine, but not on a Windows 2000 Pro machine.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top