Remove Quotations from UPC

[Bassplaya7]

I have a large list of product UPC codes which are 12 characters long. And they have quotations around them. some of the UPC's start with 0 so after i do find and replace of the quotes and format as a number it deletes the 0 from the upc, which i need that 0 to stay. Please help

 

[Joe4]

How about this formula?
=MID(A1,2,12)

 

[pgc01]

Hi

First

select the cells with the values and format them as Text

then

do the find and replace.

 

[Bassplaya7]

The MID function worked great! Thanks!

 

[Joe4]

Hi

First

select the cells with the values and format them as Text

then

do the find and replace.

<!-- / message --><!-- sig -->
In theory, it seems like that should work, but it in fact does not. There is something about Search and Replace that ignores the format of the cell and drops the leading zero.

I have actually written a short macro that I use to fix ID numbers to address this issue. If you are interested in a VBA solution, I can probably modify it to suit your needs.

 

[pgc01]

Joe, thank you. You are right, the result cell is still formatted as text but the value is converted into a number.

Bassplaya7

I'm sorry, my solution is wrong. Please ignore it.

 

[Joe4]

Here is the code I was talking about. All you have to do to run it after pasting it to your VB Editor is highlight the range you want fixed and run the macro.

Sub FixUPC()

'   This macro will run against the highlighted range
    Application.ScreenUpdating = False
    Dim cell As Range
    
'   Remove all quotes
    On Error Resume Next
    Selection.Replace What:="""", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
    On Error GoTo 0
    
'   Change format of entire column to text
    Selection.NumberFormat = "@"
    
'   Convert each entry to 12 digit UPC
    For Each cell In Selection
        cell = Format(cell, "000000000000")
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[Joe4]

Joe, thank you. You are right, the result cell is still formatted as text but the value is converted into a number.

Bassplaya7

I'm sorry, my solution is wrong. Please ignore it.

Yes, it seems to be one of those "strange" Excel bugs. It is not the behavior one would expect (doesn't seem logical).

I am quite familiar with the problem as I often have to help people in my office re-format Social Security Numbers and Zip Codes, which often have leading zeroes. So I have written a few macros which I keep handy to fix them with the click of button.