Text and Capletters

[Guest]

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

 

[Guest]

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

 

[Ron Rosenfeld]

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

 

[D Hilberg]

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))

 

[Peo Sjoblom]

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

 

[D Hilberg]

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

- David

 

[Peo Sjoblom]

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:"&LEN(A1))),1))))


<bg>


Peo

 

[Guest]

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

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

 

[Ron Rosenfeld]

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