search a string and strip out ONLY the capital letters

[aph]

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

 

[June Macleod]

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

 

[NickHK]

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]

NickHK

I tried your suggestion and with a slight change it works fine (I
Asc(strTest) >= CAPITAL_A And Asc(strTest) <= CAPITAL_Z Then). B
adding the equals it finds letters A and Z. I call this function bu
where do I get the resulting data when it returns