vba.split function with multiple delimeters

[Smurphster16]

Hi,

I currently have the following code where when the function is used in excel it retrieves the first letter of each word within the cell when the words are separated by spaces.

I want to edit the function so the first letter of the words are also retrieved if they are seperated by a forward slash or a dash as well as a space - but not sure if the VBA split function allows for multiple delimeters?

e.g Currently if i apply the function to the cell containing Equity Long/short it returns EL but I would like it to return ELS

Thanks so much for any help!

Function getfirstletters(rng As Range) As StringDim I As Long


arr = VBA.Split(rng, " ")


    If IsArray(arr) Then
    For I = LBound(arr) To UBound(arr)
        getfirstletters = getfirstletters & Left(arr(I), 1)
        Next I
        
    Else
        getfirstletters = Left(arr, 1)
        
    End If
  


End Function

 

[Scott Huish]

I rewrote that a little bit, but you don't really need to check for multiple delimiters, just change those characters in the code to a space and do a regular Split.

Function GetFirstLetters(r As Range) As String
Dim t As Long, arr
arr = Split(Replace(Replace(r, "/", " "), "-", " "))
For t = 0 To UBound(arr)
    arr(t) = UCase(Left(arr(t), 1))
Next
GetFirstLetters = Join(arr, "")
End Function

 

[Smurphster16]

Works just how I needed, thanks very much