Numbers stored as text without being in scientific notation

[Nick0131]

Hi,

So at my new job I seem to be running into an issue with numbers stored as text. When I pull data from different databases the ISBNs come out stored as text (as I want them to be) but they're in scientific notation. When I use the column to text function they're still in scientific notation. If I convert them to numbers I run into a lot of errors with already created formulas and such. Any help on getting these out of scientific notation while keeping them as text?

Thanks for the help

[newdoverman]

One situation that can cause numbers to be converted to scientific format is that the column is too narrow for the number to be displayed. Try making the column wider.

[Sam Capricci]

Can you post a sample? I tried to recreate what I thought you might have but have been unsuccessful. I put 2.502E+12 in a cell and if I make it a number I get 2502066310000. If I put 2.502e+12 in another and make it text it seems to remain that way but if I point a formula to it the formula works - for instance =A2*2 makes it 504000000000.

[Nick0131]

Here's an example!

[Glenn Kennedy]

Is this what you wanted:

=TEXT(A2,"0")

[Nick0131]

Not really I was hoping for something that wasn't a formula, thank you though that could also work

[MrShorty]

Even though the cells are formatted '@' as text, they appear to contain "numbers" anyway (you can see this using the =istext() formula, it returns FALSE). This suggests to me that Excel is converting them to numbers before applying the text formatting, or that you are importing the numbers to Excel before applying the formatting.

If you do not want to use the TEXT() formula (post-import) as Glenn suggests, then I would suggest that this issue needs to be handled during the "extract info from database and import into Excel" operation. For example, If I take one of those numbers (formatted as "0" instead of "@" so I can see the entire number), and hand enter it into column A below the sample values, I get the desired text string (not a number). I recognize that it is undesirable to hand enter every entry, but, since you have not given us any information about the imprt process, and only shown us the final result, I offer this as an example of how your result may be different by changing how you extract the data from the database and import it into Excel.

In the end, a helper column with Glenn's TEXT() formula might be the easiest to apply, though.

[joeu2004]

[.... deleted ....]

[newdoverman]

Select column A and format as NUMBER. Click on Decrease Decimal (Home Tab, Number Group) to decrease the decimal places to 0.

	A	B	C
1	ISBN-13	Title	Author
2	9780345523259	IMPERFECT	ABBOTT, JIM
3	9780345523266	IMPERFECT	ABBOTT, JIM
4	9780812978513	AMERICAN ROSE	ABBOTT, KAREN