Thanks!
--
Gary''s Student - gsnu200842


"Rick Rothstein" wrote:

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)


"Rick Rothstein" wrote in message
...
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)


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


"Rick Rothstein" wrote:

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)


"Jacob Skaria" wrote in message
...
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


"gugertmk" wrote:

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