Remove leading zeros from downloaded data

[Carla]

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

 

[Duke Carey]

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

 

[Max]

Another play to try is Data > Text to Columns
Select the entire col, click Data > Text to Columns > Finish

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Peo Sjoblom]

Did you get that to work?


--


Regards,


Peo Sjoblom

 

[Rick Rothstein \(MVP - VB\)]

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]

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\)]

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

 

[Duke Carey]

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\)]

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