可以使用InStrRev来获取字符串中最后一个下划线的位置。

Function getCountryArea(Text As String)
    getCountryArea = Left(Text, InStrRev(Text, "_") - 1)
End Function

更新：

Function getCountryArea(Text As String)
    Text = Replace(Text, "SP", "Spain")
    getCountryArea = Left(Text, InStrRev(Text, "_") - 1)
End Function

这就是：

Option Explicit

Public Function str_name(str_input As String) As String
        
    Dim l_n as long
    
    l_n = InStr(1, str_input, "_", vbTextCompare)
    l_n = InStr(l_n + 1, str_input, "_", vbTextCompare)
    
    str_name = Left(str_input, l_n - 1)
    
End Function

Public Sub TestMe()

    Debug.Print str_name("Spain_1212_Barcelona")
    Debug.Print str_name("Spain_2321_Madrid")

End Sub