Split cell values

[Misca]

I've got a few worksheets that each have a lot of cells filled with the beginnings of zip codes and I'm looking for a way to split the cell values to actual zip codes.

The values in cells vary from single zip codes ("03" = 03000) to combinations ("11,215-219,30" = 11000, 21500, 21900, 30000) and I'm trying to come up with a macro that can identify each beginning of a zip code in the cell to an actual zip code (NumberFormat = "00000").

I've tried to come up with some MID(c.value,i,x)-loops but I haven't been able to come up with a solution that can handle the mixed lengths, beginning zeroes etc

The splitting characters are space, comma and hyphen and because they've typed manually there can be more than a single splitting characters in a row ("00 - 01, 03- 035" = 00000, 01000, 03000, 03500).

Any ideas how to do this?

 

[WRL]

Hi Misca,

Do you spesifically want it in VBA, or will formulas do if possible?

So do you only want the zip code in the resulting cell? E.g. like this:

<TABLE style="WIDTH: 430pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=573><COLGROUP><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><COL style="WIDTH: 218pt; mso-width-source: userset; mso-width-alt: 10642" width=291><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 20pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=26>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 218pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=291>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>F</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>03 = 03000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>03000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>11,215- 219,30 = 11000, 21500, 21900, 30000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>11000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>21500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>21900</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>30000</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>00 - 01, 03- 035 = 00000, 01000, 03000, 03500</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>00000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>01000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>03000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>03500</TD></TR></TBODY></TABLE>

(Or like the above, but just in VBA?)

How many codes could be in one line? E.g. above has 1 code in A1 and 4 each in A2 and A3. Can this be many, of limited to some maximum?

Regards

 

[BSALV]

Sub splitsen()
  Dim c As Range, splits As Variant, splits2 As Variant, s, i As Integer, j As Integer
  For Each c In Selection.Cells                            'treat each cell in your selection
    If c.Value <> "" Then
      s = ""                                               'aux. string
      splits = Split(c.Value, ",")                         'split on the ","
      For i = 0 To UBound(splits)                          'treat every part
        If InStr(1, splits(i), "-") <> 0 Then              'look for "-" in that part
          splits2 = Split(Trim(splits(i)), "-")            'split on "-"
          If UBound(splits2) <> 1 Then
            MsgBox "error, normally 2 parts ???"
          Else
            If Not IsNumeric(splits2(0)) Or Not IsNumeric(splits2(1)) Then  'both parts numeric ?
              MsgBox "error, values not numeric"
            Else
              For j = splits2(0) To splits2(1)
                s = s & Format(j, "00") & "000" & vbTab    'add number to aux. string
              Next
            End If
          End If
        Else
          If Not IsNumeric(splits(i)) Then                 ' part numeric ?
            MsgBox "error, values not numeric"
          Else
            s = s & Format(splits(i), "00") & "000" & vbTab  'add number to aux. string
          End If
        End If
      Next
    End If
    If s <> "" Then                                        'aux. string not empty
      splits = Split(s, vbTab)                             'split aux. string on vbtab
      c.Offset(, 1).Resize(, UBound(splits) + 1).Value = splits  'write in the cells to the right of the selection
    End If
  Next
End Sub

 

[Misca]

I'll be making lookup tables from the zip codes. I haven't noticed a cell with more than 6 zip codes in it but there are so many cells & they're in different workbooks that I can't be sure there won't be more than that.

The VBA solution would be awesome because that way I can line them all up at once for the lookup table.

 

[Misca]

Thank you, BSALV!

Your code is just what I hoping for.