search a string and strip out ONLY the capital letters
 

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

search a string and strip out ONLY the capital letters
 

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

search a string and strip out ONLY the capital letters
 

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

search a string and strip out ONLY the capital letters
 

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