TRIM function

  • Thread starter Thread starter JockW
  • Start date Start date
J

JockW

Hi
Is it possible to use a function to extract only words in capital letters in
a cell?
So, for instance from "IS IT POSSIBLE to use a function" would return IS IT
POSSIBLE and ignore the rest.

Answers on a postcard please....
 
One way
Sub getcaplettersfromstring()
ac = ActiveCell
For i = 1 To Len(ac)
If Mid(ac, i, 1) = UCase(Mid(ac, i, 1)) Then
'MsgBox Mid(Ac, i, 1)
ms = ms & Mid(ac, i, 1)
End If
Next i
MsgBox ms
End Sub
 
Try this UDF (sorry, may not fit on a postcard :-)
'USAGE enter into any cell as =extractCAPS(A1) if your string is in A1
Function extractCAPS(iStr As String) As String
extractCAPS = ""
Dim c As String
Dim iWord As String
iWord = ""
Dim i As Integer
If Len(iStr) = 0 Then
extractCAPS = ""
Exit Function
End If
For i = 1 To Len(iStr)
c = Mid(iStr, i, 1)
If c <> " " Then
iWord = iWord & c
Else
If (iWord = StrConv(iWord, vbUpperCase)) Then
extractCAPS = extractCAPS & iWord
End If
iWord = " "
End If
Next i
If (iWord = StrConv(iWord, vbUpperCase)) Then
extractCAPS = extractCAPS & iWord
End If
End Function
 
Thanks Don, works fine but I would like the resilts in a seperate cell. I
didn't state that so my fault.
 
Back
Top