how to convert numbers and delete leading zeros

[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.

 

[CLR]

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

 

[Mike H]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[Pete_UK]

Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

 

[Lori]

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?

 

[David Biddulph]

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
the leading zeroes.
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)))

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[CLR]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

 

[Lori]

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.

 

[CLR]

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

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[RagDyer]

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.

 

[CLR]

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

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

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

 

[Pete_UK]

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