UPC data format conversion

[dbecker215]

Hello,

I'm having an issue with converting different UPC string lengths to one common length. Basically the data teams in our company do not have a firm standard on UPC or UPC equivalent (for internal product identification) nor do our different software have the same database structure. What I need to do is create a VBA that converts identifiers with string lengths of 3 to 13 digits all to 13 digits by placing the appropriate number of "0"s in front of the given value. I have an excel function that I created, but I'm looking to create a user form that allows anyone to easily convert the data. The excel function is:

=IF(LEN(D36757)=3,"0000000000"&D36757,IF(LEN(D36757)=4,"000000000"&D36757,IF(LEN(D36757)=5,"00000000"&D36757,IF(LEN(D36757)=6,"0000000"&D36757,IF(LEN(D36757)=7,"000000"&D36757,IF(LEN(D36757)=8,"00000"&D36757,IF(LEN(D36757)=9,"0000"&D36757,IF(LEN(D36757)=10,"000"&D36757,IF(LEN(D36757)=11,"00"&D36757,IF(LEN(D36757)=12,"0"&D36757,IF(LEN(D36757)=13,D36757,"fix please")))))))))))

What vba syntax should I use to have this loop through an unknown amount of cells and either replace the value or place the value in the cell next to the original value.

Once I start working with sheets that have 100,000 rows of data the excel formula bogs down some of the computers when I need to also run vlookups from the same workbook.

Thank you,

 

[shg]

How about just a formula?

=right("0000000000000" & a1, 13)

 

[dbecker215]

Ha! That probably would've been an easier formula. The reason I need vba is because i'm passing this on to other analysts whose data skills vary from complete beginner to advanced, so the end goal is to put it into a user form so it's easy for everyone without the risk of errors. I think I figured something out though that seems to work.

Option Explicit


Sub upcConversion()


'Converting 3-12 digit upc to 13 digit upc


Dim len3 As String, len4 As String, len5 As String, len6 As String, len7 As String, len8 As String, len9 As String, len10 As String, len11 As String, len12 As String, len13 As String
Dim x As Range
Dim strtext As String




Application.ScreenUpdating = False




len3 = "0000000000"
len4 = "000000000"
len5 = "00000000"
len6 = "0000000"
len7 = "000000"
len8 = "00000"
len9 = "0000"
len10 = "000"
len11 = "00"
len12 = "0"




For Each x In Selection
x.NumberFormat = "@"
strtext = x
On Error GoTo 0

If Len(x) = 3 Then
x.Value = len3 & x.Value

ElseIf Len(x) = 4 Then
x.Value = len4 & x.Value

ElseIf Len(x) = 5 Then
x.Value = len5 & x.Value

ElseIf Len(x) = 6 Then
x.Value = len6 & x.Value

ElseIf Len(x) = 7 Then
x.Value = len7 & x.Value

ElseIf Len(x) = 8 Then
x.Value = len8 & x.Value

ElseIf Len(x) = 9 Then
x.Value = len9 & x.Value

ElseIf Len(x) = 10 Then
x.Value = len10 & x.Value

ElseIf Len(x) = 11 Then
x.Value = len11 & x.Value

ElseIf Len(x) = 12 Then
x.Value = len12 & x.Value

ElseIf Len(x) = 13 Then
x.Value = x.Value
End If



Next x

Application.ScreenUpdating = True




End Sub


This however does take a long time to process as almost every cell will need to receive the new data. Is there a way to set it up to be faster? I think most of the UPC's will have either 10 or 12 digits to begin with, if I put the if statements in order from 13 to 3 will it be a lot faster? Some of these spreadsheets are quite large.

 

[shg]

Maybe instead ...

Sub upcConversion()
    Dim rInp        As Range
    Dim avInp       As Variant
    Dim i           As Long    '
    Dim j           As Long

    Set rInp = Intersect(Selection, ActiveSheet.UsedRange)
    avInp = rInp.Value2
    For i = 1 To UBound(avInp, 1)
        For j = 1 To UBound(avInp, 2)
            avInp(i, j) = Format(avInp(i, j), "0000000000000")
        Next j
    Next i

    rInp.Value2 = avInp
End Sub

 

[shg]

Oops, need this:

    avInp = rInp.Value2
[COLOR="#008000"]    rInp.NumberFormat = "@"[/COLOR]

 

[dbecker215]

Wow! That was incredibly fast compared to what I wrote. Yours was almost instant, mine took nearly 5 full minutes. Can you elaborate on why that is at all? This is my first intro to Ubound and I'm not 100% on understanding what the syntax is doing.

Thank you very much though.

 

[shg]

1. Because if you select entire columns, the code traverses a million rows intead of just those that are relevant.

2. Because if you read the data into an array en masse, it can be input much faster than sequentially reading individual cell vales, processed faster in memory, and written out faster than writing individual cell values.