FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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.
VBA Code:
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