You might try the ASC function to return the value of each character. If
it falls between 65 and 90 then it is a capital letter.

The following is slightly long-winded but does what you ask for:

Public Sub findInitials()

For Each c In Range("mynames") ' name the range of value you want to check
therow = c.Row
thecol = c.Column
If Len(c.Value) 0 Then
For n = 1 To Len(c.Value)
If Asc(Mid$(c.Value, n, 1)) = 65 And Asc(Mid$(c.Value, n, 1)) <= 90
Then
thestring = thestring & Mid$(c.Value, n, 1)
End If
Next n
If thestring "" Then
Range(Cells(therow, thecol + 1), Cells(therow, thecol + 1)) =
thestring
thestring = ""
End If
End If
Next c
End Sub



"aph" wrote in message
...

Hi fellow Excel users

Does anyone know a way to search a string and strip out ONLY the
capital letters?

So if cell A1 = "First X Name" how do I put "FXN". Into A2. It is
possible that there are various combinations of spaces and no spaces
between letters :-
"FirstXName"
" FirstXName"
"FirstX Name"

It has been suggested using 'Trim' but any thoughts would be helpful.

Regards

Andrew


--
aph
------------------------------------------------------------------------
aph's Profile:
http://www.excelforum.com/member.php...o&userid=17175
View this thread: http://www.excelforum.com/showthread...hreadid=471040