Prevent excel from converting non-number to scientific number

**maacmaac** · 10-11-2009, 01:29 PM

I am importing a list of values from a text file. Some of the values have the letter "E" embedded in the string. For example one of the imported values is "29875E100". Excel is automatically converting this into scentific notation. Is there a way to avoid this? Thanks.

**DonkeyOte** · 10-11-2009, 01:52 PM

If you're copying and pasting the values from the text file ensure you format the column to Text prior to pasting the values.

If you're importing via the Import Wizard then in Step 3 set the appropriate Column to Text.

**maacmaac** · 10-11-2009, 02:17 PM

Thanks for the tip. Solved issue as expected.

**mamyers64** · 12-11-2012, 01:02 PM

That solution works fine to prevent Excel from converting numbers to a numeric format cell. However, assuming the part numbers 6033E01 and 060330 are stored in text type cells and on another sheet you attempt an aggregation on those call in conjunction with a numeric value like quantity using the SUMIF function then Excel always considers those "part numbers" to be the same. So my question is how to prevent that?

Col A (Text) Col B (Number) Col C (Text)
Part Number Quantity Lookup Part Number
6033E01 5 6033E01
060330 10

=SUMIF(A:A,C2,B:B) = 15 which is incorrect