Parsing a String (zipcode) [Excel 14/2010, VBA Only)

**lloydgodin** · 11-12-2012, 05:57 PM

I need help with the following code as I do not know how to read a string and return the 1st three values.

Sub test()

    Dim iZip As String, parsed_zip As Variant

    valid = False
    Do Until valid = True
    iZip = Application.InputBox(prompt:="Enter Zipcode", Title:="Zipcode", Left:=300, Top:=250, Type:=1)
    If iZip <> Empty And iZip <> "False" Then
            valid = True
        End If
    Loop
    inp_form.Range("F5").Value = iZip
    
    'need code to read 1st 3 digits of iZip and output to variable parsed_zip
    
    Select Case parsed_zip
        Case 620 To 629
        Case Else
    End Select

End Sub

**rylo** · 11-12-2012, 06:05 PM

Hi

parsed_zip = val(left(iZip,3))

Assuming that there is something in iZip that is 3 or more characters long....

rylo

**shg** · 11-12-2012, 06:10 PM

One way:

Sub test()
    Dim sZip      As String

    Do
        sZip = Application.InputBox(Prompt:="Five-digit Zip?", _
                                    Title:="Zipcode")
        If StrPtr(sZip) = 0 Then Exit Sub   ' user canceled
    Loop Until sZip Like "#####"

    Select Case CLng(Left(sZip, 3))
        Case 620 To 629
            '...
        Case Else
            '...
    End Select
End Sub