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

[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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[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



"aph" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[aph]

NickHK

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