2 questions - how to remove exponents, how to remove trailing '0's? Thanks

**makecoldplayhistory** · 11-26-2012, 03:01 AM

I have created my first, even remotely 'complex' Excel sheet with a lot of help from this forum.

It's almost finished (but please yell out if you see a silly mistake).

I just need to

a) remove the trailing (if that's the right word) zeros; for example, cell E5 displays 0.26000000000000000000000. I need to have the coloumn formated as numbers, to 23 decimal places, for later numbers. Is there a way to have it remove the pointless '0's'?

b) Some of the numbers are large i.e. 3.34847551282792E-07 - 1.19209289550781E-07 = 2.15638261732011E-07 (cell F27). Can I have the number displayed as a real number, not a number with an exponent?

Thanks a lot,

Mike

binary conversion in progress.xlsx

**dilipandey** · 11-26-2012, 08:10 AM

Hi Mike

Please look in to Excel specification in below link from Microsoft website :-

http://office.microsoft.com/en-us/ex...005199291.aspx

See the calculation specifications section.

As an alternative, a way could be to format the number as Text using some text functions but then you'll face issues in calculations.

Regards,
DILIPandey

<click on below 'star' if this helps>

**makecoldplayhistory** · 11-26-2012, 08:16 AM

Thanks for the reply dilipandey.

How about the trailing '0's'?

Is there a way to only have the significant digits displayed (up to 23 radix points)?

**dilipandey** · 11-26-2012, 08:37 AM

Hi Mike..

Try using below formula:-

Formula:

Please Login or Register  to view this content.

and drag it down

You might need to change the format of the formula cells as General.
If you need to do any calculations on these numbers then multiply the formula by 1, so use below formula in that case :-

Formula:

Please Login or Register  to view this content.

and drag it down
Hope this helps.. thanks.

See column F and G in attached file..

binary conversion in progress.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

**makecoldplayhistory** · 11-26-2012, 08:39 AM

Brilliant.

Thanks again for your help.

**dilipandey** · 11-26-2012, 08:44 AM

You are welcome Mike..

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

2 questions - how to remove exponents, how to remove trailing '0's? Thanks

LinkBack

Thread Tools

Rate This Thread

Display

2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Re: 2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Re: 2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Re: 2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Re: 2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Re: 2 questions - how to remove exponents, how to remove trailing '0's? Thanks

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions