Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Excel.
example "Myers Power Products" in cell A3 end result "MPP" in cell C6
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Excel.
=lower("Myers Power Products")
"gugertmk" wrote: example "Myers Power Products" in cell A3 end result "MPP" in cell C6 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in 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 "gugertmk" wrote: example "Myers Power Products" in cell A3 end result "MPP" in cell C6 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "joel" wrote: =lower("Myers Power Products") "gugertmk" wrote: example "Myers Power Products" in cell A3 end result "MPP" in cell C6 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
"Jacob Skaria" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
Any issues???
If this post helps click Yes --------------- Jacob Skaria "gugertmk" wrote: "Jacob Skaria" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Excel.
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Excel.
Download and install the free add-in Morefunc.xll from:
http://www.download.com/Morefunc/300...-10423159.html then use this formula =REGEX.SUBSTITUTE(A1,"[^A-Z]","") "gugertmk" wrote: example "Myers Power Products" in cell A3 end result "MPP" in cell C6 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do i strip off Upper case Characters to another cell in Ex
"ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "joel" wrote: =lower("Myers Power Products") "gugertmk" wrote: example "Myers Power Products" in cell A3 end result "MPP" in cell C6 Thank You that worked like a charm, but had to change "Caps" to "Capsall" as an error said "Caps" was to ambiguous. Thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format Cell - Only Upper Case Alpha characters | Excel Discussion (Misc queries) | |||
Changing multiple cell text from lower case to upper case | Excel Discussion (Misc queries) | |||
Changing upper case characters to upper/lower | Excel Discussion (Misc queries) | |||
Changing file in all upper case to upper and lower case | Excel Discussion (Misc queries) | |||
How do I convert all upper case excel sheet into upper and lower . | Excel Discussion (Misc queries) |