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

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

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

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

--
ap
-----------------------------------------------------------------------
aph's Profile: http://www.excelforum.com/member.php...fo&userid=1717
View this thread: http://www.excelforum.com/showthread.php?threadid=47104