Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to a
column

Hi,

I couldn't get this without a macro but I bet someone does. In the meantime
rightclick your sheet tab, view code and paste this in. Currently monitors
A1:A100 so change to suit:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
mystring = Target.Value
For x = 1 To Len(mystring)
If Mid(mystring, x, 1) Chr(64) And Mid(mystring, x, 1) < Chr(91)
Then
mynewstring = mynewstring + Mid(mystring, x, 1)
End If
Next
End If
If Len(mynewstring) 0 Then
Target.Offset(o, 1).Value = mynewstring
End If
End Sub

Mike

"yshridhar" wrote:

Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to a
column

On Wed, 11 Jul 2007 01:58:02 -0700, yshridhar
wrote:

Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to a
column

You can write a User Defined Function (UDF) to do this.

To enter this function:

<alt-F11 opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use the UDF, enter the function

=Caps(cell_ref)

into some cell. e.g. =Caps(A1)

===============================================
Option Explicit

Function Caps(str As String) As String
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String = "[A-Z]"
Dim i As Long

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = False
oRegex.Pattern = sPattern

If oRegex.test(str) = True Then
Set mcMatchCollection = oRegex.Execute(str)
End If

For i = 0 To mcMatchCollection.Count - 1
Caps = Caps & mcMatchCollection(i)
Next i

End Function
=====================================

--ron

My best shot at a formula only counts the number of capital letters:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),
1))64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ))<91))


On Jul 11, 4:58 am, yshridhar
wrote:
Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to a
column

Just for fun

=SUM(N(EXACT({"A";"B";"C";"D";"E";"F";"G";"H";"I"; "J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V ";"W";"X";"Y";"Z"},TRANSPOSE(MID(A1,ROW(INDIRECT(" 1:"&LEN(A1))),1)))))

array entered


--
Regards,

Peo Sjoblom


"D Hilberg" wrote in message
ps.com...

My best shot at a formula only counts the number of capital letters:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),
1))64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ))<91))


On Jul 11, 4:58 am, yshridhar
wrote:
Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to
a
column

Cute. (If you change semicolons to commas, you don't have to
transpose.)

- David


On Jul 11, 6:27 pm, "Peo Sjoblom" wrote:
Just for fun

=SUM(N(EXACT({"A";"B";"C";"D";"E";"F";"G";"H";"I"; "J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V ";"W";"X";"Y";"Z"},TRANSPOSE(MID(A1,ROW(INDIRECT(" 1:"&LEN(A1))),1)))))

array entered

--
Regards,

Peo Sjoblom

"D Hilberg" wrote in message

ps.com...



My best shot at a formula only counts the number of capital letters:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),
1))64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ))<91))

On Jul 11, 4:58 am, yshridhar
wrote:
Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to
a
column

And then one could name
{"A","B","C","D","E","F","G","H","I","J","K","L"," M","N","O","P","Q","R","S","T","U","V","W","X","Y" ,"Z"}

CPS


and use

=SUMPRODUCT(N(EXACT(CPS,MID(A1,ROW(INDIRECT("1:"&L EN(A1))),1))))


<bg


Peo



"D Hilberg" wrote in message
oups.com...
Cute. (If you change semicolons to commas, you don't have to
transpose.)

- David


On Jul 11, 6:27 pm, "Peo Sjoblom" wrote:
Just for fun

=SUM(N(EXACT({"A";"B";"C";"D";"E";"F";"G";"H";"I"; "J";"K";"L";"M";"N";"O";"P";"Q";"R";"S";"T";"U";"V ";"W";"X";"Y";"Z"},TRANSPOSE(MID(A1,ROW(INDIRECT(" 1:"&LEN(A1))),1)))))

array entered

--
Regards,

Peo Sjoblom

"D Hilberg" wrote in message

ps.com...



My best shot at a formula only counts the number of capital letters:

=SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1) )),
1))64)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1 ))<91))

On Jul 11, 4:58 am, yshridhar
wrote:
Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied
to
a
column

Thanks alot Mr. Ron. The user function saved my time. My special thanks to
you
Sridhar


"Ron Rosenfeld" wrote:

On Wed, 11 Jul 2007 01:58:02 -0700, yshridhar
wrote:

Is there any fucntion to pick out capital letters in a word. I enter
miCRosoft in a cell. I want to CR in the next cell. Can it be applied to a
column

You can write a User Defined Function (UDF) to do this.

To enter this function:

<alt-F11 opens the VB Editor
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use the UDF, enter the function

=Caps(cell_ref)

into some cell. e.g. =Caps(A1)

===============================================
Option Explicit

Function Caps(str As String) As String
Dim oRegex As Object
Dim mcMatchCollection As Object
Const sPattern As String = "[A-Z]"
Dim i As Long

Set oRegex = CreateObject("VBScript.Regexp")
oRegex.Global = True
oRegex.ignorecase = False
oRegex.Pattern = sPattern

If oRegex.test(str) = True Then
Set mcMatchCollection = oRegex.Execute(str)
End If

For i = 0 To mcMatchCollection.Count - 1
Caps = Caps & mcMatchCollection(i)
Next i

End Function
=====================================

--ron

On Wed, 11 Jul 2007 23:44:03 -0700, yshridhar
wrote:

Thanks alot Mr. Ron. The user function saved my time. My special thanks to
you
Sridhar

You're very welcome. Thanks for the feedback.
--ron