Andrew,
How about call a UDF, as in =CapitalsOnly(A1)

Public Function CapitalsOnly(argRange As String) As String
Dim strTest As String * 1
Dim i As Long
Dim strTemp As String
Const CAPITAL_A As Long = 65
Const CAPITAL_Z As Long = 90

For i = 1 To Len(argRange)
strTest = Mid(argRange, i, 1)
If Asc(strTest) CAPITAL_A And Asc(strTest) < CAPITAL_Z Then
strTemp = strTemp & strTest
End If
Next

CapitalsOnly = strTemp

End Function

You would want a check that only a single value was passed.

NickHK


"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