Text to Number - Difference between Excel 2000 & 2002

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:

Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data
format"DateDMYFinish.)
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the
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
---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/

I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:

Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data
format"DateDMYFinish.)
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the
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
---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Dave,

Sorted. Thanks very much.

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

Bertie


---
Message posted from http://www.ExcelForum.com/

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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:

I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:

Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data
format"DateDMYFinish.)
which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the
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
---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/

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


Gord Dibben wrote:
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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:


I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:


Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data

format"DateDMYFinish.)

which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the

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

---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra.

Not Excel, but Windows NT doing the deed.

Gord

On Sun, 22 Aug 2004 13:03:25 -0400, Debra Dalgleish
wrote:

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


Gord Dibben wrote:
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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:


I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:


Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data

format"DateDMYFinish.)

which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the

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

---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/

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

I wouldn't have guessed this.

Gord Dibben wrote:

Thanks Debra.

Not Excel, but Windows NT doing the deed.

Gord

On Sun, 22 Aug 2004 13:03:25 -0400, Debra Dalgleish
wrote:

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


Gord Dibben wrote:
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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:


I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:

Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:


Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data

format"DateDMYFinish.)

which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the

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

---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/



--

Dave Peterson

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

Dave Peterson wrote:
Anyone got a guess why the version of windows would make a difference?

I wouldn't have guessed this.

Gord Dibben wrote:

Thanks Debra.

Not Excel, but Windows NT doing the deed.

Gord

On Sun, 22 Aug 2004 13:03:25 -0400, Debra Dalgleish
wrote:


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


Gord Dibben wrote:

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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:



I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:


Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:



Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data

format"DateDMYFinish.)


which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the

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


---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks for the info.



Debra Dalgleish wrote:

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

Dave Peterson wrote:
Anyone got a guess why the version of windows would make a difference?

I wouldn't have guessed this.

Gord Dibben wrote:

Thanks Debra.

Not Excel, but Windows NT doing the deed.

Gord

On Sun, 22 Aug 2004 13:03:25 -0400, Debra Dalgleish
wrote:


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


Gord Dibben wrote:

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

On Sun, 22 Aug 2004 07:07:21 -0500, Dave Peterson wrote:



I don't open attachments, but I bet it's differences in data that's causing the
problem.

If you've taken the data from a web page, maybe it's those non-breaking HTML
thingies.

David McRitchie has a macro that cleans up this kind of thing.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

And if you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

"claytorm " wrote:


Copied below is the the original problem, converting text to numbers.
Gord, thanks for your reply. I tried this on the same data (which is
included as an attachment) on my machine running Excel 2000 on Windows
2000, this returns "No cells found!". However, when I ran it on another
machine running Excel 2002 on WinXP, it ran fine, returning just the
results I wanted.

Is this likely to be a difference between the Excel versions, or
something wrong with my settings? I could upgrade to Excel 2002, but
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

On Sat, 21 Aug 2004 06:47:57 -0500, claytorm
wrote:



Thanks for your replies guys. I finally used Gord Dibben's solution
(DataText to ColumnsNextNext "column data

format"DateDMYFinish.)


which worked well.

On a related theme...

I still have a problem with some numbers imported as text from the

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


---
Message posted from http://www.ExcelForum.com/

Attachment filename: egdata.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=656315
---
Message posted from http://www.ExcelForum.com/



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

--

Dave Peterson