asked on

Excel leading zeroes

I have an excel file formatted as text with UPC codes. There are a number of them that have two extra leading zeroes in front. See below:

00030999542953
00030999979100
00030999187048
00030999190574

They need to look like this:

030999542953
030999979100
030999187048
030999190574

If I go into the cell and make the change, all is good, but I have about 4000 of these to do. I tried Find 000 and Replace with 0, but when you do that excel strips the leading zero off, even if you format the find and the replace as text. I've tried custom formatting to no avail as well.

Any other ideas?

Thanks in advance.

Bill

ASKER CERTIFIED SOLUTION

Harry Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Giovanni

Another way is to create a parallel column and use:

=CONCATENATE(0,VALUE(TRIM(CLEAN(RIGHT(A1,LEN(A1))))))

Open in new window

Expand the formula to all rows.

DollarBillIA

ASKER

Perfect, I had been thinking about stripping the first two, but they weren't all like that. Didn't even think about leaving 12 instead of stripping two.

All done!

Thanks!

Bill

Harry Lee

x66_x72_x65_x65,

Why make it so complicated to strip the UPC apart then add the "0" back in?

Where is the KISS spirit? In your formula, the Trim, Clean, and Value is basically one of the best way to waste CPU processing power in this case.

When you skip out the Clean and/or the Trim, the formula would do the job anyway.

In terms of the Value, what's the point of using a formula to convert a text string back into numeric value, then use the Concatenate to convert it back to Text?

Giovanni

While redundant... it gets the job done. :)