scientific number formats???

celeste

New Member
Joined
Apr 8, 2002
Messages
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!
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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,
 
Upvote 0
it makes it like 10000000000000000000000000, instead of reading what i type in.

What number did you type in?
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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,
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top