convert number to text HELP please

**rbpd5015** · 11-19-2010, 12:51 PM

Ok guys I got excel sheet from another person and I am trying to clean it up. It has a column with long numbers in it stored as text so it shows the green triangle.

FOR EXAMPLE

89014103243356509460

Now when I convert to a number it shows

8.90141E+19

Now I change cell from General to Number with no decimals I get this

89014103243356500000

????
The last 5 digits turn to ZEROS?

Can anybody help me here?

Thanks,

Matt

**ConneXionLost** · 11-19-2010, 01:03 PM

If these numbers are meant to be used for identification rather than representing quantity or intended for a calculation, then you're better off leaving them as a text string.

**rbpd5015** · 11-19-2010, 03:31 PM

Sorry thats not the answer I needed? Can you give me the correct answer. Thanks anyways.

**Paul** · 11-19-2010, 03:34 PM

It may not be the answer you needed, but it's the only answer.

Excel cannot store numbers greater than 15 significant digits in length. Any numbers greater than that and Excel converts the excess to zeros.

The only way to store a number with more digits than that is by formatting the cell as Text - which may give you that little green arrow.

What do you need to do with those "numbers"? If it's not math, then what's the harm in keeping them as text? You can hide that error by going into Excel Options.

**noexcel=badwork** · 11-19-2010, 07:38 PM

here is one suggestion:
suppose number which is more than 15 digits in cell A1 and it is in text format
use this formula in cell B1
=LEFT(A1,50)
copy down

**teylyn** · 11-20-2010, 04:12 AM

and what exactly does that achieve?

**noexcel=badwork** · 11-20-2010, 07:22 AM

the cell will look like a number not a text
and no need to make any formatting
we can hide cells containing number which
is in text format

**martindwilson** · 11-20-2010, 07:34 AM

if you wanted to do that why not just align cells to the right

**noexcel=badwork** · 11-20-2010, 07:50 AM

I make proposed alignment
it is still showing the cells as text
what I want to do here is to hide
that green arrow which is coming
in cells containing numbers in text
format. still this is not a solution
to this excel shortage, but it is a proposed
solution to what Paul suggested to hide
green arrow and to change some options in
excel options