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....
--
tia

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JockW" wrote in message
...
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....
--
tia

Download and install the free add-in Morefunc.xll from:
http://xcell05.free.fr/

=REGEX.SUBSTITUTE(A1,"[a-z,0-9]","")


"JockW" wrote:

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....
--
tia

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
--
If you find this post helpful pl. choose "Yes"...


"JockW" wrote:

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....
--
tia

Thanks Don, works fine but I would like the resilts in a seperate cell. I
didn't state that so my fault.
--
tia


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JockW" wrote in message
...
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....
--
tia

Instead of
MsgBox ms
use
ac.offset(,1)=ms
or
range("a2")=ms

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JockW" wrote in message
...
Thanks Don, works fine but I would like the resilts in a seperate cell. I
didn't state that so my fault.
--
tia


"Don Guillett" wrote:

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JockW" wrote in message
...
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....
--
tia

I can't get this to work! Probably me. Where do I put it?
--
tia


"Sheeloo" wrote:

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
--
If you find this post helpful pl. choose "Yes"...


"JockW" wrote:

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....
--
tia