Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Make Initials from Name Column (Excel 2000 >)

    Hi guys (yes, its me again...)

    (I have been busy the last couple of days since i was on the board, and I still have one or two more Q's lined up!!...sorry)

    Here is an attachment that contains a UDF to make initials from a Full name Column. The way I went about it is rather garbled and tedious... Is there a better way of going about this. The dynamics of the function look for a space, and then takes the character after the space and concatenates it with the others. Somehow I'm sure there is a more accurate and better way of doing this...so I'm throwing it out there for some input!

    PS: This function is limited to 4 initial letters maximum!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Initials from Name Column (Excel 2000 >)

    Option Explicit is AWOL again, and you haven't declared the variables! <img src=/S/argue.gif border=0 alt=argue width=50 height=25>

    You don't need WorksheetFunction.Search, since VBA has InStr. Howver, you can use the Split function to handle names with any number of initials:

    Function InitialsMaker(strName) As String
    Dim arr As Variant
    Dim i As Integer
    arr = Split(strName)
    For i = LBound(arr) To UBound(arr)
    InitialsMaker = InitialsMaker & UCase(Left(arr(i), 1))
    Next i
    End Function

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Initials from Name Column (Excel 2000 >)

    Hans beat me to it as usual but here is another way for as many names as you want


    Function Jezza(name As String)
    Dim strInit As String
    strlen = Len(name)
    strInit = Left(name, 1)

    For i = 2 To strlen

    If Mid(name, i, 1) = " " Then
    strInit = strInit + Mid(name, i + 1, 1)
    Else
    End If

    Next i
    Jezza = UCase(strInit)

    End Function
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Make Initials from Name Column (Excel 2000 >)

    I tried your function with input "Henry Anthony Nash Smythe". The result was Jezza = HANS. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Initials from Name Column (Excel 2000 >)

    <img src=/S/rofl.gif border=0 alt=rofl width=15 height=15> <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    Jerry

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Initials from Name Column (Excel 2000 >)

    Thank you Hans and Jezza.

    I knew this array scenario would work well, and I'm glad you used it Hans. I have been playing with it ever since you explained to me about LBound and UBound the other day! This is another succulent example!
    PS: I have another meaty Q coming regarding arrays.... I hope you are ready for this one! Stay tuned!

    Jezza, thanks for that. Strangely I never even considered loops to get through this problem. Its an interesting and eye opening way of tackling the problem too! Thanx for the input!
    Regards,
    Rudi

Posting Permissions

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