Concatented Numbers Display as Scientific Notation
 

xl2k on w2kPro.
My macro is creating a "Key" field that is made up of a concatenation of two cells.
The first cell is seven digits, the second varies between 2 and seven digits.
The cells are on a CSV workbook.

I insert the Key column and create the Key with;

"SrcSht.Cells(i, 1) = SrcSht.Cells(i, 2) & SrcSht.Cells(i, 3)"

The resultant display will vary as follows;
Key Field 1 Field 2
1E+11 1000001 29830
1000002309 1000002 309


The reasoning behind this is beyond me although, for this app, it doesn't matter as a
lookup has no problem finding the key and the "=" entry area displays the correct value.
Also, if I do the concatenation using the UI (Field1 & Field2) all is well.
Some results (not all) will come out correctly if I enlarge the column width.

Therefore it appears to be a display anomaly only(?).

--
Regards;
Rob
------------------------------------------------------------------------

Concatented Numbers Display as Scientific Notation
 

If the cell is formatted as General (the default) then it will
automatically display as scientific notation if the column is too
narrow. It will revert to normal notation if the column is autosized.
If the column is formatted to Number and the column is too narrow, the
Excel will fill the cell with ####. Again, the number will look normal
when the column width is widened enough.

So if you want the display to look correct, have your macro do a
Columns("A:A").EntireColumn.AutoFit statement after concatenating all
the values.

Concatented Numbers Display as Scientific Notation
 

Nick;
Thanks.
I thought that what was the case given that the format is General but, as I mentioned,
only some results will come out correctly if I enlarge the column width.
I tried enlarging the column width both in the UI and the macro (Autofilt).

Looking at it again it seems that if the second field is longer than four digits it won't
display properly.
i.e. if the concatenated length is 11 digits it screws up.

As noted, the actual value is ok, just the display is "wonky".

I think I'm missing something fundamental here (or a few brain cells).
--
Regards;
Rob
------------------------------------------------------------------------
"Nick Hebb" wrote in message
oups.com...
If the cell is formatted as General (the default) then it will
automatically display as scientific notation if the column is too
narrow. It will revert to normal notation if the column is autosized.
If the column is formatted to Number and the column is too narrow, the
Excel will fill the cell with ####. Again, the number will look normal
when the column width is widened enough.

So if you want the display to look correct, have your macro do a
Columns("A:A").EntireColumn.AutoFit statement after concatenating all
the values.

Concatented Numbers Display as Scientific Notation
 

You could give that cell a nice number format and not have to worry about what
excel likes to do:

with SrcSht.Cells(i, 1)
.value = SrcSht.Cells(i, 2).value & SrcSht.Cells(i, 3).value
.numberformat= "0"
.entirecolumn.autofit 'stop #'s from showing up.
end with

RWN wrote:

Nick;
Thanks.
I thought that what was the case given that the format is General but, as I mentioned,
only some results will come out correctly if I enlarge the column width.
I tried enlarging the column width both in the UI and the macro (Autofilt).

Looking at it again it seems that if the second field is longer than four digits it won't
display properly.
i.e. if the concatenated length is 11 digits it screws up.

As noted, the actual value is ok, just the display is "wonky".

I think I'm missing something fundamental here (or a few brain cells).
--
Regards;
Rob
------------------------------------------------------------------------
"Nick Hebb" wrote in message
oups.com...
If the cell is formatted as General (the default) then it will
automatically display as scientific notation if the column is too
narrow. It will revert to normal notation if the column is autosized.
If the column is formatted to Number and the column is too narrow, the
Excel will fill the cell with ####. Again, the number will look normal
when the column width is widened enough.

So if you want the display to look correct, have your macro do a
Columns("A:A").EntireColumn.AutoFit statement after concatenating all
the values.


--

Dave Peterson

Concatented Numbers Display as Scientific Notation
 

Rob, are you doing Autofit and Number cell formatting, or just Autofit?
I tried this out and saw the issue with numbers greater than 11
digits. Like you said, changing the width didn't help. But changing
the format to Number (with 0 decimal places) did.

Playing around a bit, I also found that numbers 1E16 have truncated
precision, so you may want to watch out for that if you're
concatenating very large numbers

Concatented Numbers Display as Scientific Notation
 

And if Rob doesn't need the value as a number, he could make it text:

with SrcSht.Cells(i, 1)
.value = "'" & SrcSht.Cells(i, 2).value & SrcSht.Cells(i, 3).value
.entirecolumn.autofit
end with

or

with SrcSht.Cells(i, 1)
.numberformat= "@"
.value = SrcSht.Cells(i, 2).value & SrcSht.Cells(i, 3).value
.entirecolumn.autofit
end with

Nick Hebb wrote:

Rob, are you doing Autofit and Number cell formatting, or just Autofit?
I tried this out and saw the issue with numbers greater than 11
digits. Like you said, changing the width didn't help. But changing
the format to Number (with 0 decimal places) did.

Playing around a bit, I also found that numbers 1E16 have truncated
precision, so you may want to watch out for that if you're
concatenating very large numbers

--

Dave Peterson

Concatented Numbers Display as Scientific Notation
 

Dave;
That's basically what I wound up doing.
I guess my question was more in the realm of "What's this?".
I'd never seen this before-mind you, there's a lot of things I haven't seen!.
(Just when I thought I knew what I was doing:-o)
--
Regards;
Rob
------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
You could give that cell a nice number format and not have to worry about what
excel likes to do:

with SrcSht.Cells(i, 1)
.value = SrcSht.Cells(i, 2).value & SrcSht.Cells(i, 3).value
.numberformat= "0"
.entirecolumn.autofit 'stop #'s from showing up.
end with

RWN wrote:

Nick;
Thanks.
I thought that what was the case given that the format is General but, as I mentioned,
only some results will come out correctly if I enlarge the column width.
I tried enlarging the column width both in the UI and the macro (Autofilt).

Looking at it again it seems that if the second field is longer than four digits it
won't
display properly.
i.e. if the concatenated length is 11 digits it screws up.

As noted, the actual value is ok, just the display is "wonky".

I think I'm missing something fundamental here (or a few brain cells).
--
Regards;
Rob
------------------------------------------------------------------------
"Nick Hebb" wrote in message
oups.com...
If the cell is formatted as General (the default) then it will
automatically display as scientific notation if the column is too
narrow. It will revert to normal notation if the column is autosized.
If the column is formatted to Number and the column is too narrow, the
Excel will fill the cell with ####. Again, the number will look normal
when the column width is widened enough.

So if you want the display to look correct, have your macro do a
Columns("A:A").EntireColumn.AutoFit statement after concatenating all
the values.


--

Dave Peterson

Concatented Numbers Display as Scientific Notation
 

Nick;
Thanks.
As I noted in my response to Dave, this was the 1st time I'd seen this and was wondering
If I'd "slipped a cog" somewhere.

Also, I believe the max # is 1E14 (15 digit number) which doesn't concern me because my
Key will never exceed 14 digits, but thanks for the reminder.

--
Regards;
Rob
------------------------------------------------------------------------
"Nick Hebb" wrote in message
ups.com...
Rob, are you doing Autofit and Number cell formatting, or just Autofit?
I tried this out and saw the issue with numbers greater than 11
digits. Like you said, changing the width didn't help. But changing
the format to Number (with 0 decimal places) did.

Playing around a bit, I also found that numbers 1E16 have truncated
precision, so you may want to watch out for that if you're
concatenating very large numbers