How do i strip off Upper case Characters to another cell in Excel.

[gugertmk]

example "Myers Power Products" in cell A3 end result "MPP" in cell C6

 

[joel]

=lower("Myers Power Products")

 

[Jacob Skaria]

If you are looking for a User defined function ...Set the Security level to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below function. Save. Get back to Workbook.

Use the formula
=GetUpperLetters(A1)

Function GetUpperLetters(strRange)
For intTemp = 1 To Len(strRange)
If Asc(Mid(strRange, intTemp, 1)) > 64 And Asc(Mid(strRange, intTemp, 1)) <
91 Then
GetUpperLetters = GetUpperLetters & Mid(strRange, intTemp, 1)
End If
Next
End Function


If this post helps click Yes

 

[ryguy7272]

It's interesting that you knew Excel could do this. How did you know Excel
could do what you wanted to do? I don't think most people know that Excel
can do this...or many other things that it can actually do quite easily...

Function Caps(r As Range) As String
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Text
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c >= "A" And c <= "Z" Then
s2 = s2 & c
End If
Next
Caps = s2
End Function

Call the function like this:
=Caps(A1)

That goes in Cell C1.

HTH,
Ryan---

 

[gugertmk]

If you are looking for a User defined function ...Set the Security level to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key Alt+F11.
Insert a module and paste the below function. Save. Get back to Workbook.

Use the formula
=GetUpperLetters(A1)

Function GetUpperLetters(strRange)
For intTemp = 1 To Len(strRange)
If Asc(Mid(strRange, intTemp, 1)) > 64 And Asc(Mid(strRange, intTemp, 1)) <
91 Then
GetUpperLetters = GetUpperLetters & Mid(strRange, intTemp, 1)
End If
Next
End Function


If this post helps click Yes

 

[Jacob Skaria]

Any issues???

If this post helps click Yes

 

[Rick Rothstein]

Just to be different, here is another way that UDF could be written...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
For X = 1 To Len(strRange)
If Mid(strRange, X, 1) Like "[!A-Z]" Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters= Replace(strRange, " ", "")
End Function

 

[Gary''s Student]

Interesting...I never knew that Mid was more than just a function.
--
Gary''s Student - gsnu2007xx

Just to be different, here is another way that UDF could be written...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
For X = 1 To Len(strRange)
If Mid(strRange, X, 1) Like "[!A-Z]" Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters= Replace(strRange, " ", "")
End Function

 

[Rick Rothstein]

Using Mid that way (as a statement rather than a function) is an extremely
fast (and I do mean **extremely** fast) way to change characters within a
String (it rivals API methods in speed to do the same) and, as such, it is
orders of magnitude faster than using concatenation to do it. It's a good
tool to keep in your coding arsenal. However, the Replace function is not
one of VB's fastest functions; so, more than likely, my use of it robs back
(untested) any speed gains my loop created (my *guess* is my UDF is no
faster than Jacob's). I would also note that the Like operator is also no
'speed demon' either (flexible, yes; fast, no), so it probably reduces the
function's efficiency somewhat as well. That part, however, can be overcome
by using the much faster InStr function to test our characters in place of
the Like operator test (my If..Then test doesn't really *need* all of the
flexibility built into the Like operator)...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To Len(strRange)
If InStr(Alpha, Mid(strRange, X, 1)) = 0 Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters = Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)

Interesting...I never knew that Mid was more than just a function.
--
Gary''s Student - gsnu2007xx

Just to be different, here is another way that UDF could be written...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
For X = 1 To Len(strRange)
If Mid(strRange, X, 1) Like "[!A-Z]" Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters= Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)

If you are looking for a User defined function ...Set the Security
level
to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key
Alt+F11.
Insert a module and paste the below function. Save. Get back to
Workbook.

Use the formula
=GetUpperLetters(A1)

Function GetUpperLetters(strRange)
For intTemp = 1 To Len(strRange)
If Asc(Mid(strRange, intTemp, 1)) > 64 And Asc(Mid(strRange, intTemp,
1))
<
91 Then
GetUpperLetters = GetUpperLetters & Mid(strRange, intTemp, 1)
End If
Next
End Function


If this post helps click Yes
---------------
Jacob Skaria


:

example "Myers Power Products" in cell A3 end result "MPP" in cell C6

 

[Rick Rothstein]

Interestingly enough, it seems we can eliminate the use of the Replace
function if we are willing to use (what I assume is) the much faster CLEAN
function call over in the worksheet. That is, call the revised UDF below
this way...

=CLEAN(GetUpperLetters(A1))

Here is the revised UDF without the Replace function...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To Len(strRange)
If InStr(Alpha, Mid(strRange, X, 1)) = 0 Then Mid(strRange, X, 1) = Chr(9)
Next
GetUpperLetters = strRange
End Function

Interestingly, there are several other characters with ASCII codes less than
32 that we can use in place of 9 I used above, but I used 9 for a specific
reason (although there are a few other codes less that 32 that would also
work as I'm about to describe). We can remove the CLEAN function call over
in the worksheet and the above UDF will **appear** to be working correctly,
but it isn't really. To see this, change the formula on the worksheet to
this...

=GetUpperLetters(A1)&"<"

You will note the result of this formula is MPP< (where the MPP part is as
the OP would want followed by a concatenated "<" symbol to see the "end" of
the text); however, assuming the cells to the right of the formula are
empty, look at the vertical grid lines in those first few cells after it...
they are missing as if the text had blank spaces and was longer than the
width of its cell. Now, there are no blanks and the output is as was wanted,
so perhaps the above would be usable (it would be the fastest the UDF could
be in that case). Of course, UDFs are not really very fast constructions in
the first place, so perhaps this quest for code efficiency is unwarranted.
Anyway, I though it was interesting enough to mention.

--
Rick (MVP - Excel)

Using Mid that way (as a statement rather than a function) is an extremely
fast (and I do mean **extremely** fast) way to change characters within a
String (it rivals API methods in speed to do the same) and, as such, it is
orders of magnitude faster than using concatenation to do it. It's a good
tool to keep in your coding arsenal. However, the Replace function is not
one of VB's fastest functions; so, more than likely, my use of it robs
back (untested) any speed gains my loop created (my *guess* is my UDF is
no faster than Jacob's). I would also note that the Like operator is also
no 'speed demon' either (flexible, yes; fast, no), so it probably reduces
the function's efficiency somewhat as well. That part, however, can be
overcome by using the much faster InStr function to test our characters in
place of the Like operator test (my If..Then test doesn't really *need*
all of the flexibility built into the Like operator)...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To Len(strRange)
If InStr(Alpha, Mid(strRange, X, 1)) = 0 Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters = Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)

Interesting...I never knew that Mid was more than just a function.
--
Gary''s Student - gsnu2007xx

Just to be different, here is another way that UDF could be written...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
For X = 1 To Len(strRange)
If Mid(strRange, X, 1) Like "[!A-Z]" Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters= Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)


If you are looking for a User defined function ...Set the Security
level
to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key
Alt+F11.
Insert a module and paste the below function. Save. Get back to
Workbook.

Use the formula
=GetUpperLetters(A1)

Function GetUpperLetters(strRange)
For intTemp = 1 To Len(strRange)
If Asc(Mid(strRange, intTemp, 1)) > 64 And Asc(Mid(strRange, intTemp,
1))
<
91 Then
GetUpperLetters = GetUpperLetters & Mid(strRange, intTemp, 1)
End If
Next
End Function


If this post helps click Yes
---------------
Jacob Skaria


:

example "Myers Power Products" in cell A3 end result "MPP" in cell C6

 

[Teethless mama]

Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/3000-2077_4-10423159.html

then use this formula
=REGEX.SUBSTITUTE(A1,"[^A-Z]","")

 

[Gary''s Student]

Thanks!
--
Gary''s Student - gsnu200842

Interestingly enough, it seems we can eliminate the use of the Replace
function if we are willing to use (what I assume is) the much faster CLEAN
function call over in the worksheet. That is, call the revised UDF below
this way...

=CLEAN(GetUpperLetters(A1))

Here is the revised UDF without the Replace function...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To Len(strRange)
If InStr(Alpha, Mid(strRange, X, 1)) = 0 Then Mid(strRange, X, 1) = Chr(9)
Next
GetUpperLetters = strRange
End Function

Interestingly, there are several other characters with ASCII codes less than
32 that we can use in place of 9 I used above, but I used 9 for a specific
reason (although there are a few other codes less that 32 that would also
work as I'm about to describe). We can remove the CLEAN function call over
in the worksheet and the above UDF will **appear** to be working correctly,
but it isn't really. To see this, change the formula on the worksheet to
this...

=GetUpperLetters(A1)&"<"

You will note the result of this formula is MPP< (where the MPP part is as
the OP would want followed by a concatenated "<" symbol to see the "end" of
the text); however, assuming the cells to the right of the formula are
empty, look at the vertical grid lines in those first few cells after it...
they are missing as if the text had blank spaces and was longer than the
width of its cell. Now, there are no blanks and the output is as was wanted,
so perhaps the above would be usable (it would be the fastest the UDF could
be in that case). Of course, UDFs are not really very fast constructions in
the first place, so perhaps this quest for code efficiency is unwarranted.
Anyway, I though it was interesting enough to mention.

--
Rick (MVP - Excel)

Using Mid that way (as a statement rather than a function) is an extremely
fast (and I do mean **extremely** fast) way to change characters within a
String (it rivals API methods in speed to do the same) and, as such, it is
orders of magnitude faster than using concatenation to do it. It's a good
tool to keep in your coding arsenal. However, the Replace function is not
one of VB's fastest functions; so, more than likely, my use of it robs
back (untested) any speed gains my loop created (my *guess* is my UDF is
no faster than Jacob's). I would also note that the Like operator is also
no 'speed demon' either (flexible, yes; fast, no), so it probably reduces
the function's efficiency somewhat as well. That part, however, can be
overcome by using the much faster InStr function to test our characters in
place of the Like operator test (my If..Then test doesn't really *need*
all of the flexibility built into the Like operator)...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
Const Alpha As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
For X = 1 To Len(strRange)
If InStr(Alpha, Mid(strRange, X, 1)) = 0 Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters = Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)

Interesting...I never knew that Mid was more than just a function.
--
Gary''s Student - gsnu2007xx


:

Just to be different, here is another way that UDF could be written...

Function GetUpperLetters(ByVal strRange As String) As String
Dim X As Long
For X = 1 To Len(strRange)
If Mid(strRange, X, 1) Like "[!A-Z]" Then Mid(strRange, X, 1) = " "
Next
GetUpperLetters= Replace(strRange, " ", "")
End Function

--
Rick (MVP - Excel)


If you are looking for a User defined function ...Set the Security
level
to
low/medium in (Tools|Macro|Security). 'Launch VBE using short-key
Alt+F11.
Insert a module and paste the below function. Save. Get back to
Workbook.

Use the formula
=GetUpperLetters(A1)

Function GetUpperLetters(strRange)
For intTemp = 1 To Len(strRange)
If Asc(Mid(strRange, intTemp, 1)) > 64 And Asc(Mid(strRange, intTemp,
1))
<
91 Then
GetUpperLetters = GetUpperLetters & Mid(strRange, intTemp, 1)
End If
Next
End Function


If this post helps click Yes
---------------
Jacob Skaria


:

example "Myers Power Products" in cell A3 end result "MPP" in cell C6

 

[gugertmk]

It's interesting that you knew Excel could do this. How did you know Excel
could do what you wanted to do? I don't think most people know that Excel
can do this...or many other things that it can actually do quite easily...

Function Caps(r As Range) As String
Dim s As String, s2 As String, c As String
s2 = ""
s = r.Text
l = Len(s)
For i = 1 To l
c = Mid(s, i, 1)
If c >= "A" And c <= "Z" Then
s2 = s2 & c
End If
Next
Caps = s2
End Function

Call the function like this:
=Caps(A1)

That goes in Cell C1.

HTH,
Ryan---

Thank You that worked like a charm, but had to change "Caps" to "Capsall" as
an error said "Caps" was to ambiguous.
Thanks again