Extract first letter of each word from cell

[FryGirl]

I found this UDF, but not quite getting the preferred results.

Here is my string 323rd Special Victims Unit and it returns 3SVU. I would like for it to return 323 SVU.

Behind the number will always be two characters (i.e., th, rd, st). I would like to remove those two characters and just have the numbers. I could probably wrap a substitute formula around the UDF formula, but can it be done in one go.

I realize to start at the first word, Special, it has to do with starting the array at 1 instead of 0, but I can't seem to figure out that part. The words in this example are three, but the most would be five after the number.

Function GetFirstLetters(rng As Range) As String
    Dim arr
    Dim I As Long
    arr = 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

 

[footoo]

Function GetFirstLetters(rng As Range) As String
    Dim arr
    Dim I As Long
    arr = Split(rng, " ")
    If IsArray(arr) Then
        GetFirstLetters = Left(arr(0), 3) & " "
        For I = 1 To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
        Next I
    Else
        GetFirstLetters = Left(arr, 1)
    End If
End Function

 

[FryGirl]

Thank you footoo. I knew I should have added the full compliment of possible numbers. Numbers at the beginning can be 1 to 4 numbers.

4004th Special Victims Unit - This would be 4004 SVU
323rd Special Victims Unit - This would be 323 SVU
95th Special Victims Unit - This would be 95 SVU
2nd Special Victims Unit - this would be 2 SVU

Sorry about that.

 

[juddaaaa]

How about this

Function Abbreviate(strInput As String) As String

    Dim re As Object: Set re = CreateObject("VBScript.RegExp")
    Dim match As Object
    Dim strOutput As String
    Dim wrd As Variant
  
    With re
        .Pattern = "^(\d+)(th|rd|st|nd)\s([A-Za-z0-9\s]+)"
        Set match = .Execute(strInput)
      
        With match(0)
            strOutput = .SubMatches.Item(0) & " "
          
            For Each wrd In Split(.SubMatches.Item(2), " ")
                strOutput = strOutput & Left(wrd, 1)
            Next wrd
        End With
    End With
  
    Abbreviate = strOutput

End Function

Abbreviate("4004th Special Victims Unit")

 

[FryGirl]

Yes, that is something I can surely work with. Thank you both for your time.

 

[juddaaaa]

You're welcome

 

[sandy666]

another way with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.SelectColumns(Table.AddColumn(Source, "Result", each Text.Select([RAW],{"0".."9"})&" "&Text.Select([RAW],{"A".."Z"})),{"Result"})

in
    Result

 

[footoo]

Without Regex :

Function GetFirstLetters(rng As Range) As String
    Dim arr
    Dim I As Long
    arr = Split(rng, " ")
    If IsArray(arr) Then
        GetFirstLetters = Left(arr(0), Len(arr(0)) - 2) & " "
        For I = 1 To UBound(arr)
            GetFirstLetters = GetFirstLetters & Left(arr(I), 1)
        Next I
    Else
        GetFirstLetters = Left(arr, 1)
    End If
End Function

 

[FryGirl]

Thanks again everybody for these additional solutions.