Convert to Proper Case with Exceptions

[Capt_Ron]

I have found plenty of code to convert text in a range from UPPERCASE to Proper Case but I want to be able to exclude from the conversion any text that matches text found in a list on another worksheet (USA, TX, PD, UPS, a, an, at, in , on, etc.).

Can this be done referencing the list as a named range or would it be best to simply refer to the list of words by Sheet.Range?

Anyone have an example I could follow?

Ron

 

[Peter_SSs]

BTW, I know cell A10 is not what is normally referred to as Camel Case, just demonstrating the code.

 

[dchieu]

Hi Peter,

1- With regards, to #NAME?, it dealt with excel trust issues regarding macros and vba.
2- With regards to the latest coding, all I can say is FANTASTIC. Worked better than my expectations.

Thanks again and regards,
Donald

 

[Rick Rothstein]

2- With regards to the latest coding, all I can say is FANTASTIC. Worked better than my expectations.

Question... did Peter's code work as you expected for cell A4 in the example text he posted? I ask because the original data had an asterisk after the word "PART" and his process text eliminated that asterisk.

If you don't need the immediacy of a formula, there is a relatively simple macro available that will do what you want (it preserves the asterisk I mentioned above)...

Sub MakeProperExceptForList()
  Dim X As Long, LastRow As Long, List As Variant
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  List = Range("D1", Cells(Rows.Count, "D").End(xlUp))
  Range("B1:B" & LastRow) = Evaluate("IF(LEN(A1:A" & LastRow & "),PROPER(A1:A" & LastRow & "))")
  With Range("B1:B" & LastRow)
    For X = 1 To UBound(List)
      .Replace List(X, 1), List(X, 1), xlPart, , False
    Next
  End With
End Sub

 

[Peter_SSs]

Hi Peter,

1- With regards, to #NAME?, it dealt with excel trust issues regarding macros and vba.
2- With regards to the latest coding, all I can say is FANTASTIC. Worked better than my expectations.

Thanks again and regards,
Donald

1. Ah, thanks for that explanation.
2. Glad to hear it worked as you wanted.

 

[dchieu]

Hi Rick,

It actually produced the following:
Access To Capital – part*II: Securing The Money Minority Businesses Need To Grow

That would be a rare, if not mistaken, user input. Would not even know what would be the proper display since it has no meaning. But that was a good catch.

Thanks and regards.

 

[Peter_SSs]

Question... did Peter's code work as you expected for cell A4 in the example text he posted? I ask because the original data had an asterisk after the word "PART" ..

The original data (post #11) actually had a CHAR(160) in that spot. The asterisk has appeared just in my post - through use of Excel jeanie I think.

 

[shg]

It's a curiosity to me that vBulletin substitutes asterisks for NBS's. I wonder if there is some rationale, or it's just a legacy thing.

 

[Peter_SSs]

If you don't need the immediacy of a formula, there is a relatively simple macro available that will do what you want ...

Try "TONY WENT TO TORONTO" in col A with "NY" and "TO" in col D

 

[dchieu]

Peter,

Has anyone attempted to load these macro enabled workbooks onto Excel 2011 for Mac? The file loads but not the UDF.

Regards,
Donald

 

[Peter_SSs]

Peter,

Has anyone attempted to load these macro enabled workbooks onto Excel 2011 for Mac? The file loads but not the UDF.

Regards,
Donald

I'm sorry, I have no experience with Macs so I cannot provide any information about that.