how to convert numbers and delete leading zeros

L

Lori

I have data being brought into Excel from another program. I need to convert
a column of data to a number format and eliminate the leading zeros, leaving
only 12 significant digits.

If you are not going to be doing math with the "numbers", then maybe this
would do you.....

=RIGHT(A1,12)

Vaya con Dios,
Chuck, CABGx3

Put a 1 in any cell and copy it
Select your column of text (numbers) and then
Edit|Paste special
Select multiply and click OK

Mike

Or if you do want it to be a number:

=--RIGHT(A1,12)

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

Hi Mike.........
For my XL97, this results in Scientific Notation if the value has 12
significant digits.

Vaya con Dios,
Chuck, CABGx3

Hi Sandy..........
I also get Scientific Notation with this formula in my XL97...........

Vaya con Dios,
Chuck, CABGx3

Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

Will I be able to perform this function and leave the data in the same
column, or will I need to perform this function in another column?

That doesn't work if the number is something like 1234567890.98765
If the input is a number, rather than text, then by default it would lose
If there is a decimal point within the 12 significant digits, then you may
want =LEFT(A1,13) or =--LEFT(A1,13) to get it back to being a number (but
this will truncate, rather than rounding).
More generally, one can probably get to 12 significant figures with
=ROUND(A1,11-INT(LOG10(A1)))

You will unless you re-format as Number or a Custom.

In point of fact I had not been following the thread and I was just
responding to your post so I had not noticed the 12 significant digits.
Maybe I should mind my own business <g>

--
Reagrds,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

Hi Pete.........
Mine still returns the 12 rightmost digits.......since the OP asked for 12
significant digits from a string with leading zeros, I assumed there would
not be more.........probably dumb of me tho.....<g>

Vaya con Dios,
Chuck, CABGx3

I am afraid that I don't follow your point. The OP said,

So surely there are only 12 numeric characters?

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

Replace @mailinator.com with @tiscali.co.uk

Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

Thanks for the information. I'm trying to change the format of the column
without having the results end up in another column. I have 2 fairly complex
macros using this column and I need to keep it in the same location. I know
I can add in several steps to cut and paste the results back into this column
location but I was trying to avoid that.

You will need a helper column to use the formula I suggested.........if you
want to only stay within the same column, try the Data > TextToColumns
feature........

Vaya con Dios,
Chuck, CABGx3

It's always good to see you Sandy........"how 'bout another cup of coffee"?

Vaya con Dios,
Chuck, CABGx3

That's because that empty cell was formatted to General by default.

General format will take a 12 digit number and automatically convert it to
scientific, even on the XL02 machine I'm on today.

Just format to Number, either before or after the Paste Special.

Thanks RD........I tried, but I couldn't get it to take.........I'll go back
and try again.

Vaya con Dios,
Chuck, CABGx3

Well I'll be dipped.......it worked first time here at home.........I'll
have to try it again at work on monday...........

Vaya con Dios,
Chuck, CABGx3

The point was that with 00123456789123456 stripped of leading zeros
and to 12 significant digits you want to end up with:

123456789123000

but I see that the thread has rumbled on after I posted ...

Pete