# Thread: Finding first letters in names

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

2. =IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1))

cheers, Paul

3. Thanks Paul - just right!

Cheers

4. Can anyone suggest how the formula would change to find 3 or more first letters? ie Mr Donald Duck = MDD

Thanks

5. =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. That's starting to get silly. ;-))
You'd be better off splitting it into 3 columns, Title, First, Last.

cheers, Paul

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

#### Posting Permissions

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