i am faguely familiar w/ the "E" notation in math (scientific format?), but
it is causing me a problem and i need to know how to re-fomat a number to
NOT imply to be an "E" number. i asked a question earlier about "if it
looks like it is a match, it is a match." well, i found out the problem and
it is when an field (nothing to do w/ math, science...) is brought in to a
spreadsheet and it fits the profile that can be reduced to scientific
(6E+02), excel does it. that messes everything up, though. TRY THIS
OUT...

type in 6004665102 in cell A1 (set column width = 7.5 w/ times new roman at
size 10 to see this ugly transformation to scientific). then type in
'6004665102 in cell B1 (must start w/ the hyphen to eliminate transformation
to scientific format; also can set column width if you like). set both
cells to format--cells--general just so we are on even playing field.
write code:
Sub scientificjunk()
If Range("a1") = Range("b1") Then
MsgBox "they match!"
Else
MsgBox "something's wrong."
End If
End Sub

you can use: trim(B1) or B1.value, etc. all you want in code, but you can't
touch A1 since A1 is ultimately part of large array brought in (maybe in
text, maybe in html...). can you get it to say "they match!"? without
touching cell A1 in spreadsheet or qualifying range("a1") in the code.
my main goal: how can i NOT have A1 revert to scientific when downloaded?
good luck, mike allen

"mike allen" wrote in message
...

i am faguely familiar w/ the "E" notation in math (scientific
format?), but it is causing me a problem and i need to know how to
re-fomat a number to NOT imply to be an "E" number. i asked a
question earlier about "if it looks like it is a match, it is a
match." well, i found out the problem and it is when an field
(nothing to do w/ math, science...) is brought in to a spreadsheet
and it fits the profile that can be reduced to scientific (6E+02),
excel does it. that messes everything up, though.
TRY THIS OUT...

type in 6004665102 in cell A1 (set column width = 7.5 w/ times new
roman at size 10 to see this ugly transformation to scientific).
then type in '6004665102 in cell B1 (must start w/ the hyphen to
eliminate transformation to scientific format; also can set column
width if you like). set both cells to format--cells--general just
so we are on even playing field.
write code:
Sub scientificjunk()
If Range("a1") = Range("b1") Then
MsgBox "they match!"
Else
MsgBox "something's wrong."
End If
End Sub

you can use: trim(B1) or B1.value, etc. all you want in code, but
you can't touch A1 since A1 is ultimately part of large array
brought in (maybe in text, maybe in html...). can you get it to say
"they match!"? without touching cell A1 in spreadsheet or qualifying
range("a1") in the code.
my main goal: how can i NOT have A1 revert to scientific when
downloaded? good luck, mike allen


Hi Mike,

Without going into everything you wrote, the simple reason why the
your code returns "something's wrong." is that the two cells *do not*
contain the same values.

A1 contains the number (integer) 6004665102 (a number being just over
6 billion).
A2 contains a string of 10 characters "6004665102" (a text string)

They are fundamentally different things as your code correctly informs
you.

The *format* that the number is displayed in has nothing to do with
it's value.


To prove that, put the same (number *not* the text string) value in A1
and B1 as you outline above, and format as you say. They both
*display* as 6e+02.

Now expand B1 to be wider (say 50 wide). It now *displays* as
6004665102.

Now run your code, and it will correctly tell you that they match.

HTH,

Alan.

The difference is one's numeric the other text, by putting a single quote in
front you are telling Excel to store as text string.

Use format to change the display method, the cell normally defaults to
'general' which means for numerical entries the display changes to suit the
size of the cell, so an overflowing number will show in Scientific format,
if it didn't then the cell would show a series of hashes (#). You can
change the cell format using the following (not exhaustive).

Range("A1").NumberFormat = "0"
Range("A1").NumberFormat = "0.E+00"
Range("A1").NumberFormat = "General"


Cheers
Nigel

"mike allen" wrote in message
...
i am faguely familiar w/ the "E" notation in math (scientific format?),
but
it is causing me a problem and i need to know how to re-fomat a number to
NOT imply to be an "E" number. i asked a question earlier about "if it
looks like it is a match, it is a match." well, i found out the problem
and
it is when an field (nothing to do w/ math, science...) is brought in to a
spreadsheet and it fits the profile that can be reduced to scientific
(6E+02), excel does it. that messes everything up, though. TRY THIS
OUT...

type in 6004665102 in cell A1 (set column width = 7.5 w/ times new roman
at
size 10 to see this ugly transformation to scientific). then type in
'6004665102 in cell B1 (must start w/ the hyphen to eliminate
transformation
to scientific format; also can set column width if you like). set both
cells to format--cells--general just so we are on even playing field.
write code:
Sub scientificjunk()
If Range("a1") = Range("b1") Then
MsgBox "they match!"
Else
MsgBox "something's wrong."
End If
End Sub

you can use: trim(B1) or B1.value, etc. all you want in code, but you
can't
touch A1 since A1 is ultimately part of large array brought in (maybe in
text, maybe in html...). can you get it to say "they match!"? without
touching cell A1 in spreadsheet or qualifying range("a1") in the code.
my main goal: how can i NOT have A1 revert to scientific when downloaded?
good luck, mike allen

mike allen wrote:

i am faguely familiar w/ the "E" notation in math (scientific
format?), but it is causing me a problem and i need to know how to
re-fomat a number to NOT imply to be an "E" number. i asked a
question earlier about "if it looks like it is a match, it is a
match." well, i found out the problem and it is when an field
(nothing to do w/ math, science...) is brought in to a spreadsheet
and it fits the profile that can be reduced to scientific (6E+02),
excel does it. that messes everything up, though. TRY THIS OUT...

type in 6004665102 in cell A1 (set column width = 7.5 w/ times new
roman at size 10 to see this ugly transformation to scientific).
then type in '6004665102 in cell B1 (must start w/ the hyphen to
eliminate transformation to scientific format; also can set column
width if you like). set both cells to format--cells--general just so
we are on even playing field. write code:
Sub scientificjunk()
If Range("a1") = Range("b1") Then
MsgBox "they match!"
Else
MsgBox "something's wrong."
End If
End Sub

you can use: trim(B1) or B1.value, etc. all you want in code, but
you can't touch A1 since A1 is ultimately part of large array brought
in (maybe in text, maybe in html...). can you get it to say "they
match!"? without touching cell A1 in spreadsheet or qualifying
range("a1") in the code. my main goal: how can i NOT have A1 revert
to scientific when downloaded? good luck, mike allen

OTOH: type in 6004665102 in cell A1 *and* B1, without quotes.
Make sure A1 is in sci notation and B1 is not.
Your code will say they are identical.

BTW: it's better code if you explicitly write
Range("a1").Value = Range("b1").Value
This way you only compare the value of the cells and not the entire
cells.

--
Amedee Van Gasse using XanaNews 1.16.3.1
If it has an "X" in the name, it must be Linux?