Excel thinks my numbers are text [Archive]

View Full Version : Excel thinks my numbers are text

10-30-2002, 12:42 PM

J.T. had a problem caused by Excel treating his numbers as text. This is probably the most anoying thing about XL. Often (but of course not always) when bring data in from somewhere else, XL treats the numbers as text.

Does anyone have a reliable way to fix this once it happens? Just changing the format does not appear to be enough.

P

Pseudolus

10-30-2002, 12:52 PM

I had the same problem awhile back and asked it here (http://www.actuary.ca/phpBB/viewtopic.php?p=113306&highlight=excel+text#113306).

Short answer:

If you're pasting the numbers in, try pasting as text.

If that doesn't work or isn't possible, the best workaround seems to be soing a side calculation where you multiply all your text/numbers by 1 (or add zero). The results will be recognized as numerics and can be pasted back where you want them to go. This works well for contiguous arrays, less well if you have a bunch of scattered instances.

Good luck!

Dr T Non-Fan

10-30-2002, 01:06 PM

Depends on where it's coming from. Access? Is it a flat-file of data being imported and parsed?

I usually have the opposite problem: numbered text always gets converted to numbers, and if the text can be interpreted as a date field, it will be converted to dates. I think this is the fault of the creator of "General" number format.
Does this have something to do with the sheet's "Style" default? Mine's "General," which I interpret as my saying, "Oh, great Excel God, omniscient in data formats, do thy stuff."
I'll bet your default number style is on "Text." Check "fOrmat, Style."

MountainHawk

10-30-2002, 01:12 PM

If I'm going to be doing a calculation on an imported column, I'll usually create a column with =value in it, so I know the column is numbers.

10-30-2002, 01:43 PM

I interpret the replies so far as saying... yep, we all have problems with XL formats and don't have easy solutions. I interpret the lack of easy solutions to mean that there are no easy solutions.

I take it as more proof of my contention that no one at Microsoft ever uses these products. If they did, there would be easy ways to fix any formatting problems.

Elisha

10-30-2002, 03:11 PM

You can always do an =ISNUMBER(cell) to test if it is and then a =VALUE(cell).

MountainHawk

10-30-2002, 04:14 PM

Elisha,

Why test? =value() of a number is the number anyway, is it not?