scientific number formats???

celeste · Apr 28th, 2002, 12:57 AM

i have a spreadsheet that has one of its columns listing various product part numbers. a few of those numbers are like this:
10E0040
10E0041
etc, thru to 10E0049
Excel keeps automatically making the value of these (even if i enter it as a number) as 1E+41, 1E+42, etc. if i multiply all by 1 to make them into a number, it makes it like 10000000000000000000000000, instead of reading what i type in. these part numbers need to read as numbers because my list is sorted by part number. some part numbers have letters in them, some don't. how do i turn this function off? i do not know code, so any help other than code will be greatly appreciated!!!

TIA!

Rocky E · Apr 28th, 2002, 01:04 AM

Two choices. First Format Cells, Text or second start the part number with a single qoute like '10e1234 and Excel will leave things alone. Now the bad news the cells it has already converted will have to be redone by hand, unless someone else has a sudgestion.

HTH,

Rocky...

[ This Message was edited by: Rocky E on 2002-04-28 00:05 ]

celeste · Apr 28th, 2002, 01:08 AM

i just tried that, and it is still sorting them incorrectly because of it. for example, part number 10E0040 is coming before part number 08A0100. my formulas will not read correctly unless i can figure out a way to keep it from doing this. all of my formulas that will bounce off of this number are IF type formulas that have ranges as names. it stops searching for 08A0100 because it thinks it's not there because it sees the 10E0040. i'm half bald right now because it is driving me crazy!!

Thanks for you help,

Aladin Akyurek · Apr 28th, 2002, 01:11 AM

it makes it like 10000000000000000000000000, instead of reading what i type in.

What number did you type in?

celeste · Apr 28th, 2002, 01:12 AM

10E0040

Aladin Akyurek · Apr 28th, 2002, 01:18 AM

Quote:

On 2002-04-28 00:12, celeste wrote:
10E0040

If you insist on entering numeric and non-numeric data in the same column, you could enter part numbers with this structure with a quote like this:

'10E0040

Rocky E · Apr 28th, 2002, 01:29 AM

Woah slow down, relax a little. Trust us Ecell does work, something is just confussing it a little. When I get this way here what I do. Take a new worksheet. Format the whole column "A" as text. The enter by hand at least 3 part numbers out of order. Select the cokumn and click A->Z or Data, sort. Look carefully at the numbers. Does everything look the same as yours? Must be something different as the does work on my machine. Ssometimes if we have 8000 row with 24 columns the problems are harder to find.

This will work. A lot of us deal with part numbers everyday.

HTH

Rocky...

celeste · Apr 28th, 2002, 01:39 AM

i have them in as text now, so far there are only 10 that i have had to correct. i'm just worried about my formulas referencing off of them correctly. i'm going to double check them tomorrow.

Thanks all for your help,

Yogi Anand · Apr 28th, 2002, 09:19 AM

Hi Celeste:
The use of letter e preceded by some numeric digits in a numeric expression is used as scientific notation for really large and really small numbers. So, it would be prudent to change from the letter e to any other letter in your part number -- I recognize it may not be possible. But sooner or later you are going to run into problems again, when an innocent looking part number may be coerced into being a number again.
If you can change the letter e in the part number, you can do that easily by using the SUBSTITUTE command to another letter.

Hope This Helps!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 28th, 2002, 12:57 AM	#1
celeste New Member Join Date: Apr 2002 Posts: 7	i have a spreadsheet that has one of its columns listing various product part numbers. a few of those numbers are like this: 10E0040 10E0041 etc, thru to 10E0049 Excel keeps automatically making the value of these (even if i enter it as a number) as 1E+41, 1E+42, etc. if i multiply all by 1 to make them into a number, it makes it like 10000000000000000000000000, instead of reading what i type in. these part numbers need to read as numbers because my list is sorted by part number. some part numbers have letters in them, some don't. how do i turn this function off? i do not know code, so any help other than code will be greatly appreciated!!! TIA!

Apr 28th, 2002, 01:04 AM	#2
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Two choices. First Format Cells, Text or second start the part number with a single qoute like '10e1234 and Excel will leave things alone. Now the bad news the cells it has already converted will have to be redone by hand, unless someone else has a sudgestion. HTH, Rocky... [ This Message was edited by: Rocky E on 2002-04-28 00:05 ]

Apr 28th, 2002, 01:08 AM	#3
celeste New Member Join Date: Apr 2002 Posts: 7	i just tried that, and it is still sorting them incorrectly because of it. for example, part number 10E0040 is coming before part number 08A0100. my formulas will not read correctly unless i can figure out a way to keep it from doing this. all of my formulas that will bounce off of this number are IF type formulas that have ranges as names. it stops searching for 08A0100 because it thinks it's not there because it sees the 10E0040. i'm half bald right now because it is driving me crazy!! Thanks for you help,

Apr 28th, 2002, 01:11 AM	#4
Aladin Akyurek MrExcel MVP Join Date: Feb 2002 Location: The Hague Posts: 50,319	it makes it like 10000000000000000000000000, instead of reading what i type in. What number did you type in?

Apr 28th, 2002, 01:12 AM	#5
celeste New Member Join Date: Apr 2002 Posts: 7	10E0040

Apr 28th, 2002, 01:29 AM	#7
Rocky E Board Regular Join Date: Feb 2002 Location: North Alabama, USA Posts: 105	Woah slow down, relax a little. Trust us Ecell does work, something is just confussing it a little. When I get this way here what I do. Take a new worksheet. Format the whole column "A" as text. The enter by hand at least 3 part numbers out of order. Select the cokumn and click A->Z or Data, sort. Look carefully at the numbers. Does everything look the same as yours? Must be something different as the does work on my machine. Ssometimes if we have 8000 row with 24 columns the problems are harder to find. This will work. A lot of us deal with part numbers everyday. HTH Rocky...

Apr 28th, 2002, 01:39 AM	#8
celeste New Member Join Date: Apr 2002 Posts: 7	i have them in as text now, so far there are only 10 that i have had to correct. i'm just worried about my formulas referencing off of them correctly. i'm going to double check them tomorrow. Thanks all for your help,

Apr 28th, 2002, 09:19 AM	#9
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Celeste: The use of letter e preceded by some numeric digits in a numeric expression is used as scientific notation for really large and really small numbers. So, it would be prudent to change from the letter e to any other letter in your part number -- I recognize it may not be possible. But sooner or later you are going to run into problems again, when an innocent looking part number may be coerced into being a number again. If you can change the letter e in the part number, you can do that easily by using the SUBSTITUTE command to another letter. Hope This Helps!