First Letter From Words VBA - Acronymize

[1.12]

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 : 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 (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Excel Fox]

Try this in a code module, and use in formula



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)

[Rick Rothstein]

Try this in a code module, and use in formula



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...


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