Why does Excel think this is a number?

[EricG]

I have a text string of 4-digit numbers separated by commas. When I paste
that string into a cell, Excel converts it to a number instead of
interpreting it as text. Can anyone explain why? I could understand if it
were 3-digit numbers separated by commas, because that is a conventional
representation. Here is an example:

I paste in a string (without quotes) like:
"4235,4236,4237,4238,4246,4247,4248,4249,4250,4251,4252"

Excel turns it into a number like:
42,354,236,423,742,300,000,000,000,000,000,000,000,000,000

Interestingly, if I paste in:
"4235, 4236, 4237, 4238, 4246, 4247, 4248, 4249, 4250, 4251, 4252"

I get a text string and not a number. This does not happen in cells where I
preformat the cell as Text, but does when the cell is General format.

Just curious,

Eric

 

[Rick Rothstein]

Those commas without any spaces next to them look like and attempt to
include the thousands' delimiter in your number to Excel. Obviously they are
not in the correct places, but Excel is apparently "correcting" the mistake
it thinks you made. As it turns out, just including one comma anywhere in a
string of digits is sufficient to kick off this action on Excel's part. This
appears to be one of those cases where Excel is trying to be helpful, but
it's not.

 

[EricG]

P.S. - just noticed that I posted in the Programming group, so I'll add that
I'm pasting this into the cell from my VBA code! LOL.

 

[JLGWhiz]

Adding to Rick's comments:
Excel was originally designed to be a number crunching spreadsheet for
competition with Lotus 123 and Quaro Pro to name a couple. Many of the
features of Excel are based on manipulation of numbers and facilitating the
User's input by ensuring the format of the numbers are correct, such as
automatically making an entry with a slash (/) to a date data type. For
number crunchers, these little aids are usually helpful, but for some of the
more sophisticated users, they can be a pain. In the old days, many of
these things were explained in files or books that accompanied the
installation software. Today, when a user buys a system, it comes loaded
with the software and no books to tell them what the software does, or why
it does it.. Now you have to go to the book store and buy books that tell
you how to use what you bought. The changes that have been made to Excel,
and other MS software, over the years would result in many volumes of books
to fully describe the capablities, peculiarities, quirks and remedies. Most
of it is on-line somewhere if one has the patience to look for it.

 

[J_Knowles]

Say you are returning the text string to celll A25, format A25 to be text in
the macro.

Sub TextString()
text_strg = "1000,1002,1003"
Range("E25").NumberFormat = "@" ' format for text
Range("E25").Formula = text_strg
End Sub


HTH

 

[EricG]

Thanks for all the replies, fellas. I'm fully aware of the way Microsoft has
tried to be useful to their customers. Their brand of "artificial
intelligence" is not always what we want, but overall they have done pretty
well at making our lives easier, so I won't whine about one or two little
glitches!

Happy Holidays,

Eric

 

[Stefano]

If you want to enter a number as a text start by typing a single quote:
'1
is the text "1"

Stefano