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?

Bill
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
DollarBillIA,

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
Q-28245577-Sample.xlsx
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
Another way is to create a parallel column and use:

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

Expand the formula to all rows.
0
Author 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!

Thanks!

Bill
0
Commented:
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?
0
Commented:
While redundant... it gets the job done. :)
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.