Macro for Removing all Uppercase Words?

[HaoXu]

Hi guys!:

I'm looking for a macro that allows me to remove all the uppercase words of a selected range.
For example, i have:

*HELLO everyone NICE TO met you
-turns to:
*everyone met you

Thank you so much in advance guys.


Regards

 

[pgc01]

Hi

This is a udf solution.

In B2:

=RemoveUpperW(A2)

Function RemoveUpperW(s As String)

With CreateObject("VBScript.RegExp")
    .Pattern = "\b[A-Z]+\b"
    .Global = True
    RemoveUpperW = Application.Trim(.Replace(s, ""))
End With
End Function

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Test:REMOVE Only ALL UPPERCASE words.</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Test: Only words.</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>

 

[HaoXu]

Hi pgc1:

Thank you for your help! But i'm completely new at excel and i don't know how to use udf, i only know how to use macros.
When i try to use =removeupperw("range") it gives me an error, (i already pasted the code to my vba project).

Regards

 

[Rick Rothstein]

Thank you for your help! But i'm completely new at excel and i don't know how to use udf, i only know how to use macros.

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code pgc posted in Message #2 into the code window that just opened up. That's it.... you are done. You can now use RemoveUpperW just like it was a built-in Excel function. For example,

=RemoveUpperW(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[HaoXu]

Hi Rick:

Thank you for trying to help me, i did the whole process that you have posted, but it still gives me error. Isn't there any macro for this? because macro are the only thing that i can use...

Sorry for the incoveniences.

Regards

Hao Xu

 

[Rick Rothstein]

Hi Rick:

Thank you for trying to help me, i did the whole process that you have posted, but it still gives me error.

It should not have given you an error. I am wondering if you specified the wrong argument? I notice you mentioned "selection" in your original message... the UDF (user defined function) that pgc posted can only work on one cell at a time. When I gave you this example...

=RemoveUpperW(A1)

that was meant to tell you to put the first cell address from your selection in place of the A1, then copy the formula down (or across depending on which way your original data went) till the end of your data.

Isn't there any macro for this? because macro are the only thing that i can use...

Regular Expressions (what pgc used to create his solution) is not something I work with, but I believe this conversion of his UDF to a macro should work correctly. Select all the cells (as a contiguous range) that you want to process first, then run this macro...

Sub RemoveUpperCaseWords()
  Dim R As Long, C As Long, Data As Variant
  Data = Selection
  With CreateObject("VBScript.RegExp")
    .Pattern = "\b[A-Z]+\b"
    .Global = True
    For R = 1 To UBound(Data, 1)
      For C = 1 To UBound(Data, 2)
        Data(R, C) = Application.Trim(.Replace(Data(R, C), ""))
      Next
    Next
  End With
  Selection = Data
End Sub

 

[HaoXu]

Hi Rick!:

Thank you so much for your help, it worked perfectly!!

Thank you for your time!

Best Regards