Keeping a 12 digit format for grocery UPCs

jwdemo

Board Regular
Joined
Dec 12, 2013
Messages
188
Office Version
  1. 2013
I am trying to import some excel spreadsheets that contain a variety of UPC formats. Some of the UPCs contain a zero for the first number and when I remove the "-" is takes the zero away. Here are some examples:

Before:
A1: 011548-554782
B1: 0-14575-66871-3

After:
A1: 11548554782
B1: 14575668713

What I really want is this:
011548554782
014575668713

I feel like it is just a simple matter of choosing the correct format for the cell but I can't figure it out.

Any thoughts?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Format the cells as Text before editing.
 
Upvote 0
You mean using find and replace?

How about instead using a formula:

=SUBSTITUTE(A1, "-", "")

Then format that column as Text, then copy and paste values.
 
Upvote 0
Putting a single quote (') in front of numbers also forces them to always retain leading zeros.
 
Upvote 0
Data - Text to columns
Delimited
next
next
click text
finish
Edit the cells and they will stay as text.

If you just use format to format as text Excel will think it is smarter than you and change it to a number when there is no none numeric in it and you hit enter. Using the above will stop this.

HOWEVER, if you use search and replace to remove the - then it will change it back to a number

How are you removing the - in the numbers?
 
Upvote 0
This code will do it for you, notice the use of .text instead of .value

Code:
Sub RemoveDash()
Dim X As Long
For X = 1 To Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & X).Formula = Replace(Range("A" & X).Text, "-", "")
Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top