Hi,

I have a spreadsheet of data that was downloaded from a corporate system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column. The
only way I can seem to do it is to physically click in each cell and delete
the apostrophe. Does anyone have any suggestions on how I can do that all at
once for all 1400 plus numbers I have in that column?

thanks.

Carla

Copy an empty cell, then select all your cells that have these numbers and
use Edit-Paste Special-Values-Add


"Carla" wrote:

Hi,

I have a spreadsheet of data that was downloaded from a corporate system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column. The
only way I can seem to do it is to physically click in each cell and delete
the apostrophe. Does anyone have any suggestions on how I can do that all at
once for all 1400 plus numbers I have in that column?

thanks.

Carla

Another play to try is Data Text to Columns
Select the entire col, click Data Text to Columns Finish
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Carla" wrote:
Hi,

I have a spreadsheet of data that was downloaded from a corporate system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column. The
only way I can seem to do it is to physically click in each cell and delete
the apostrophe. Does anyone have any suggestions on how I can do that all at
once for all 1400 plus numbers I have in that column?

thanks.

Carla

I have a spreadsheet of data that was downloaded from a corporate system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column.
The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that all
at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace on
Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the "Find
what" field, leave the "Replace with" field blank and press the "Replace
All" button.

Rick

Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a spreadsheet of data that was downloaded from a corporate system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column.
The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace on
Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the "Find
what" field, leave the "Replace with" field blank and press the "Replace
All" button.

Rick

Yes, I tried it before I posted it and it "appeared" to work fine. Why are
you asking? Does it not always work this way?

Rick


"Peo Sjoblom" wrote in message
...
Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a spreadsheet of data that was downloaded from a corporate
system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that column.
The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace on
Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the "Find
what" field, leave the "Replace with" field blank and press the "Replace
All" button.

Rick

I have never gotten it to work when it was preceded to make a number text

Try this

type

'012345

in A1, press enter and try to replace it


I always get "Microsoft Office Excel cannot find a match"

That is why copying an empty cell and pasting special selecting add works

The only times I got it to work was probably when it was visible in the cell
and not only in the formula bar



--


Regards,


Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, I tried it before I posted it and it "appeared" to work fine. Why are
you asking? Does it not always work this way?

Rick


"Peo Sjoblom" wrote in message
...
Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a spreadsheet of data that was downloaded from a corporate
system.
One of the fields is formatted as text - '00000454589. I would like to
remove all the leading zeros at the front of each number in that
column. The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace on
Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the "Find
what" field, leave the "Replace with" field blank and press the "Replace
All" button.

Rick

I copied your "number" from your post and pasted it several times in two
separate columns. I then took the "number" Carla posted and pasted it into
the two columns also. I then left one column formatted as General and the
other column I formatted as Text. I selected both columns, one at a time,
and applied the Replace function on each... for both columns, the zeroes
were removed... in the General column, the text became actual numbers (that
is, they became right justified), in the text column, the numbers stayed as
Text (that is, they remained left justified); but in both cases, the leading
zeroes were removed. If it matters any, I am using XL2003.

Wait a minute...

I just looked back at the columns and I must have missed this on my first
viewing... the original pasting of your number, and only that INITIAL
pasting of YOUR number, didn't change. Well, the apostrophe was removed, but
not the zero. For every other pasting of your number and all pastings of
Carla's number, the leading zero was removed, but not the FIRST pasting of
your number only. Strange.

Wait a minute...

I just tried it again on a fresh sheet and this time it worked perfectly for
all pasted numbers. I'm at a loss here. Any insights on any of this?

Rick


"Peo Sjoblom" wrote in message
...
I have never gotten it to work when it was preceded to make a number text

Try this

type

'012345

in A1, press enter and try to replace it


I always get "Microsoft Office Excel cannot find a match"

That is why copying an empty cell and pasting special selecting add works

The only times I got it to work was probably when it was visible in the
cell and not only in the formula bar



--


Regards,


Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, I tried it before I posted it and it "appeared" to work fine. Why
are you asking? Does it not always work this way?

Rick


"Peo Sjoblom" wrote in message
...
Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a spreadsheet of data that was downloaded from a corporate
system.
One of the fields is formatted as text - '00000454589. I would like
to
remove all the leading zeros at the front of each number in that
column. The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace
on Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the
"Find what" field, leave the "Replace with" field blank and press the
"Replace All" button.

Rick

I'm in agreement with Peo - Replace has NEVER worked for me. Not in 2000,
2002, 2003, and 2007

Try manually entering the #s with the leading apostrophe and then doing the
replace. What happens then??

"Rick Rothstein (MVP - VB)" wrote:

I copied your "number" from your post and pasted it several times in two
separate columns. I then took the "number" Carla posted and pasted it into
the two columns also. I then left one column formatted as General and the
other column I formatted as Text. I selected both columns, one at a time,
and applied the Replace function on each... for both columns, the zeroes
were removed... in the General column, the text became actual numbers (that
is, they became right justified), in the text column, the numbers stayed as
Text (that is, they remained left justified); but in both cases, the leading
zeroes were removed. If it matters any, I am using XL2003.

Wait a minute...

I just looked back at the columns and I must have missed this on my first
viewing... the original pasting of your number, and only that INITIAL
pasting of YOUR number, didn't change. Well, the apostrophe was removed, but
not the zero. For every other pasting of your number and all pastings of
Carla's number, the leading zero was removed, but not the FIRST pasting of
your number only. Strange.

Wait a minute...

I just tried it again on a fresh sheet and this time it worked perfectly for
all pasted numbers. I'm at a loss here. Any insights on any of this?

Rick


"Peo Sjoblom" wrote in message
...
I have never gotten it to work when it was preceded to make a number text

Try this

type

'012345

in A1, press enter and try to replace it


I always get "Microsoft Office Excel cannot find a match"

That is why copying an empty cell and pasting special selecting add works

The only times I got it to work was probably when it was visible in the
cell and not only in the formula bar



--


Regards,


Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, I tried it before I posted it and it "appeared" to work fine. Why
are you asking? Does it not always work this way?

Rick


"Peo Sjoblom" wrote in message
...
Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote in
message ...
I have a spreadsheet of data that was downloaded from a corporate
system.
One of the fields is formatted as text - '00000454589. I would like
to
remove all the leading zeros at the front of each number in that
column. The
only way I can seem to do it is to physically click in each cell and
delete
the apostrophe. Does anyone have any suggestions on how I can do that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click Edit/Replace
on Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in the
"Find what" field, leave the "Replace with" field blank and press the
"Replace All" button.

Rick

Ah! There is the error Peo was talking about.<g

So, there is a difference between typing the number in with a leading
apostrophe and pasting the number already having a leading apostrophe in.

Very strange.

Rick


"Duke Carey" wrote in message
...
I'm in agreement with Peo - Replace has NEVER worked for me. Not in 2000,
2002, 2003, and 2007

Try manually entering the #s with the leading apostrophe and then doing
the
replace. What happens then??

"Rick Rothstein (MVP - VB)" wrote:

I copied your "number" from your post and pasted it several times in two
separate columns. I then took the "number" Carla posted and pasted it
into
the two columns also. I then left one column formatted as General and the
other column I formatted as Text. I selected both columns, one at a time,
and applied the Replace function on each... for both columns, the zeroes
were removed... in the General column, the text became actual numbers
(that
is, they became right justified), in the text column, the numbers stayed
as
Text (that is, they remained left justified); but in both cases, the
leading
zeroes were removed. If it matters any, I am using XL2003.

Wait a minute...

I just looked back at the columns and I must have missed this on my first
viewing... the original pasting of your number, and only that INITIAL
pasting of YOUR number, didn't change. Well, the apostrophe was removed,
but
not the zero. For every other pasting of your number and all pastings of
Carla's number, the leading zero was removed, but not the FIRST pasting
of
your number only. Strange.

Wait a minute...

I just tried it again on a fresh sheet and this time it worked perfectly
for
all pasted numbers. I'm at a loss here. Any insights on any of this?

Rick


"Peo Sjoblom" wrote in message
...
I have never gotten it to work when it was preceded to make a number
text

Try this

type

'012345

in A1, press enter and try to replace it


I always get "Microsoft Office Excel cannot find a match"

That is why copying an empty cell and pasting special selecting add
works

The only times I got it to work was probably when it was visible in the
cell and not only in the formula bar



--


Regards,


Peo Sjoblom



"Rick Rothstein (MVP - VB)" wrote in
message ...
Yes, I tried it before I posted it and it "appeared" to work fine. Why
are you asking? Does it not always work this way?

Rick


"Peo Sjoblom" wrote in message
...
Did you get that to work?


--


Regards,


Peo Sjoblom




"Rick Rothstein (MVP - VB)" wrote
in
message ...
I have a spreadsheet of data that was downloaded from a corporate
system.
One of the fields is formatted as text - '00000454589. I would
like
to
remove all the leading zeros at the front of each number in that
column. The
only way I can seem to do it is to physically click in each cell
and
delete
the apostrophe. Does anyone have any suggestions on how I can do
that
all at
once for all 1400 plus numbers I have in that column?

Still another method.... select the entire column, click
Edit/Replace
on Excel's menu bar (or just key-in Ctrl+H), put an apostrophe in
the
"Find what" field, leave the "Replace with" field blank and press
the
"Replace All" button.

Rick