First Letter From Words VBA - Acronymize

Hello Everyone,

How qualify a specific problem in excel file with a formula, including a formula in formulas already existing or with vba?

I join a file where the problem is : Attachment 52. I need to qualify cells from "A2" to "A31". The results in "AE", or/and "AF" or/and "AG" should be true.

There is severals formulas in cell "A2" when you press crtl + F3. There are 3 others formulas active in AE,AF,AG.

The problem is to include a formula like in AH, initials of the names in the first colomn, to say it's ok to qualify email or/and website.

I don't know if it's possible with excel formulas or vba.

Thanks a lot for your kindly help,
Cheers,

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

Try this in a code module, and use in formula

Code:

---


Function ACRONYMIZE(rng As Range) As String
    Dim lng As Long
 
    For lng = 0 To UBound(Split(Trim(rng.Text), " "))
        ACRONYMIZE = ACRONYMIZE & Left(Split(Trim(rng.Text), " ")(lng), 1)
    Next lng
 
End Function

---

Example = ACRONYMIZE(A2)

Quote:

---

Originally Posted by Excel Fox

Try this in a code module, and use in formula

Code:

---


Function ACRONYMIZE(rng As Range) As String
    Dim lng As Long
 
    For lng = 0 To UBound(Split(Trim(rng.Text), " "))
        ACRONYMIZE = ACRONYMIZE & Left(Split(Trim(rng.Text), " ")(lng), 1)
    Next lng
 
End Function

---

---

Using the UBound function where I have highlighted in red does not help in making sure the acronyms returned by the function will be all upper case letters. Also, I think it would be better to use the WorksheetFunction's Trim function instead of the VB Trim function as that would reduce multiple contiguous spaces down to single spaces and avoid having to try to trim individual words in the loop. Finally, my preference for text processing functions is to make the text argument a String rather than a Range... doing this would allow the function to be more easily used by other VB code and still not affect the functionality when used as a UDF. This is how I probably would have written your ACRONYMIZE function...

Code:

---

Function ACRONYMIZE(S As String) As String
    Dim X As Long, Words() As String
    Words = Split(UCase(WorksheetFunction.Trim(S)))
    For X = 0 To UBound(Words)
        ACRONYMIZE = ACRONYMIZE & Left(Words(X), 1)
    Next
End Function

---