Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Finding first letters in names

    Hi,

    I have a column - Say A, which contains numerous names, same have one name (ie superman) and others have two (ie Donald Duck)

    I need to extract the first letter into Column B. I have this one name sorted out using =left() - ie Superman = S, but can't figure out how to Donald Duck to = DD.

    The formula is embedded so need to arrange it so there in no space behind the one letter ie, S not S+space.

    I hope this makes sense, thanks in appreciation of any help
    Last edited by verada; 2012-04-15 at 00:20. Reason: typos

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    =IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1))

    cheers, Paul

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Thanks Paul - just right!

    Cheers

  4. #4
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post
    Can anyone suggest how the formula would change to find 3 or more first letters? ie Mr Donald Duck = MDD

    Thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    =IF(ISNUMBER(FIND(" ",A1)),IF(ISNUMBER(FIND(" ",A1,FIND(" ",A1)+1)),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)),LEFT(A1,1))

    Steve

  6. #6
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,172
    Thanks
    47
    Thanked 981 Times in 911 Posts
    That's starting to get silly. ;-))
    You'd be better off splitting it into 3 columns, Title, First, Last.

    cheers, Paul

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about the shorter
    =LEFT(A1,1)&IF(ISNUMBER(FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,1)&IF(ISNUMBER(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),""),"")

    Steve

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,822
    Thanks
    134
    Thanked 481 Times in 458 Posts
    Hi Steve

    How about a simple custom Function that would deal with lots of names, e.g:

    Function zCaps(z As String)
    On Error Resume Next
    zText = UCase(z)
    zArray = Split(zText, " ")
    zCaps = ""
    For i = 0 To UBound(zArray)
    zCaps = zCaps & Left(zArray(i), 1)
    Next i
    End Function

    ..see attached workbook for examples

    zeddy
    Attached Files Attached Files

Posting Permissions

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