how to convert numbers and delete leading zeros
 

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.

how to convert numbers and delete leading zeros
 

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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


"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

Vaya con Dios,
Chuck, CABGx3



"Mike H" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

Vaya con Dios,
Chuck, CABGx3



"Sandy Mann" wrote:

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


"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

On Jan 11, 7:24*pm, "Sandy Mann" wrote:
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

"CLR" wrote in message

...



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

"Lori" wrote:

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.- Hide quoted text -

- Show quoted text -

how to convert numbers and delete leading zeros
 

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?

"CLR" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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)))
--
David Biddulph

"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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


"CLR" wrote in message
...
Hi Sandy..........
I also get Scientific Notation with this formula in my XL97...........

Vaya con Dios,
Chuck, CABGx3



"Sandy Mann" wrote:

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


"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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



"Pete_UK" wrote:

Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

On Jan 11, 7:24 pm, "Sandy Mann" wrote:
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

"CLR" wrote in message

...



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

"Lori" wrote:

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.- Hide quoted text -

- Show quoted text -

how to convert numbers and delete leading zeros
 

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

a column of data to a number format and eliminate the leading zeros,
leaving
only 12 significant digits

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


"Pete_UK" wrote in message
...
Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

On Jan 11, 7:24 pm, "Sandy Mann" wrote:
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

"CLR" wrote in message

...



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

"Lori" wrote:

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.- Hide quoted text -

- Show quoted text -

how to convert numbers and delete leading zeros
 

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.

"David Biddulph" wrote:

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)))
--
David Biddulph

"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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



"Lori" wrote:

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?

"CLR" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

Vaya con Dios,
Chuck, CABGx3



"Sandy Mann" wrote:

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


"CLR" wrote in message
...
Hi Sandy..........
I also get Scientific Notation with this formula in my XL97...........

Vaya con Dios,
Chuck, CABGx3



"Sandy Mann" wrote:

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


"CLR" wrote in message
...
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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CLR" wrote in message
...
Hi Mike.........
For my XL97, this results in Scientific Notation if the value has 12
significant digits.

Vaya con Dios,
Chuck, CABGx3



"Mike H" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

Vaya con Dios,
Chuck, CABGx3



"RagDyer" wrote:

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.

--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"CLR" wrote in message
...
Hi Mike.........
For my XL97, this results in Scientific Notation if the value has 12
significant digits.

Vaya con Dios,
Chuck, CABGx3



"Mike H" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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


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

Vaya con Dios,
Chuck, CABGx3



"RagDyer" wrote:

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.

--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"CLR" wrote in message
...
Hi Mike.........
For my XL97, this results in Scientific Notation if the value has 12
significant digits.

Vaya con Dios,
Chuck, CABGx3



"Mike H" wrote:

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

"Lori" wrote:

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.

how to convert numbers and delete leading zeros
 

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

On Jan 11, 7:48*pm, "Sandy Mann" wrote:
I am afraid that I don't follow your point. *The OP said,

a column of data to a number format and eliminate the leading zeros,
leaving
only 12 significant digits

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

"Pete_UK" wrote in message

...
Hi Sandy & Chuck,

Try it with:

00123456789123456

Pete

On Jan 11, 7:24 pm, "Sandy Mann" wrote:



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

"CLR" wrote in message

...

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

"Lori" wrote:

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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -