VBA Function to return Multiple Values

[Stephen_IV]

I have some code and it works for returning single values but I need to modify it to bring back multiple results

Public Function States(s As String) As String
    Select Case True
        Case InStr(s, "Alabama"): States = "AL"
        Case InStr(s, "Kansas"): States = "KS"
        Case InStr(s, "Connecticut"): States = "CT"
        Case InStr(s, "Virginia"): States = "VA"
        Case Else
    End Select
End Function

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>2000 ARLINGTON OAKS DR S; MOBILE Alabama 36695-8739</th><th>AL</th></tr></thead><tbody>
<tr><td>978 VIRGINIA ST SABETHA Kansas 66534-2431</td><td>KS</td></tr>
<tr><td>5200 WILSHIRE RD CHESAPEAKE Virginia 23321-3280</td><td>VA</td></tr>
<tr><td>1952 East Main St Bridgeport Connecticut 06610, 44 Brooks St Woodstock Virginia</td><td>CT, VA</td></tr>
<tr><td>1943 Roosevelt St Wichita Kansas, 441 West Ave. Auburn Alabama</td><td>KA, AL</td></tr>
</tbody></table>

 

[Eric W]

Try:

Public Function States(s As String) As String
Dim S1 As Variant, S2 As Variant, i As Long

    S1 = Array("AL", "KS", "CT", "VA")
    S2 = Array("Alabama", "Kansas", "Connecticut", "Virginia")

    For i = 0 To UBound(S1)
        If InStr(s, S2(i)) > 0 Then States = States & ", " & S1(i)
    Next i
    States = Mid(States, 3)
End Function

 

[JackDanIce]

Try:

Public Function States(ByRef msg As String) As String

    Dim x       As Long
    Dim arr()   As String
    arr = Split("AlabamaAL|KansasKS|ConnecticutCT|VirginiaVA", "|")
    
    For x = LBound(arr) To UBound(arr)
        If InStr(msg, Left$(arr(x), Len(arr(x)) - 2)) Then States = States & Right$(arr(x), 2) & ", "
    Next x

    States = Left$(States, Len(States) - 2)
    Erase arr
    
End Function

 

[Stephen_IV]

AHHHH! Thank you both for the help and knowledge that you supplied me!! I greatly appreciate it! Thanks again!