• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

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:


They need to look like this:


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.

  • 2
  • 2
1 Solution
Harry LeeCommented:

Since you need the leading zero, you have to format the Excel column as Text.

Second step is add a helper column after the column which contains the UPCs, and do a formula like =right(A2,12), and copy it all the way down the list.

next, you can copy the entire added column, and paste special as value back to the original UPC column, and delete the added helper column.

Look at my sample
Giovanni HewardCommented:
Another way is to create a parallel column and use:


Open in new window

Expand the formula to all rows.
DollarBillIAAuthor Commented:
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!


Harry LeeCommented:

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 HewardCommented:
While redundant... it gets the job done. :)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now