Results 1 to 3 of 3

Thread: First Letter From Words VBA - Acronymize

  1. #1
    Junior Member
    Join Date
    May 2011
    Posts
    1
    Rep Power
    0

    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 : SPREADSHEET EXEMPLE.xlsx. 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
    Last edited by DocAElstein; 10-02-2023 at 12:48 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    14
    Quote Originally Posted by Excel Fox View Post
    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

Similar Threads

  1. Converting a Column Number to a Column Letter
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 6
    Last Post: 12-17-2012, 02:29 AM
  2. Replies: 11
    Last Post: 10-07-2012, 12:05 AM
  3. Replies: 4
    Last Post: 09-26-2012, 09:04 AM
  4. Spell a Date out in Words
    By Rick Rothstein in forum Excel and VBA Tips and Tricks
    Replies: 4
    Last Post: 02-25-2012, 08:49 PM
  5. Number to Words (Rupees)
    By sa.1985 in forum Excel Help
    Replies: 2
    Last Post: 12-16-2011, 08:57 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •