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

 

[dchieu]

Hi Peter,


Here are samples of the input strings. Unfortunately, most are in caps:


NY TECH MEETUP (NYTM)
CITIZENSHIP EDUCATION FUND OPENING RECEPTION
MONEY WHERE IT MATTERS: MANAGING CORPORATE, FEDERAL & FOUNDATION FUNDS
ACCESS TO CAPITAL – PART II: SECURING THE MONEY MINORITY BUSINESSES NEED TO GROW
ACCESS TO CAPITAL LUNCHEON
Business of Hip Hop - Examining Next Steps
BUILDING IT TOGETHER: WHY STRATEGIC PARTNERSHIPS WORK
WHY DIVERSITY MATTERS


Desired output:
NY Tech Meetup (NYTM)
Citizenship Education Fund Opening Reception
Money Where It Matters: Managing Corporate, Federal & Foundation Funds
Access To Capital – Part II: Securing The Money Minority Businesses Need To Grow
Access To Capital Luncheon
Business Of Hip Hop - Examining Next Steps
Building It Together: Why Strategic Partnerships Work
Why Diversity Matters


The red highlighted words (to which there are just three currently) are in my sorted excludeDB range.


I have Mike's original entire set of lines with those red highlighted items removed.


Hope this helps. Thanks.

 

[mikerickson]

The original did not include the red text.

You need to have both of Mike's functions and use the CorrectCase function in your worksheet.

If that doesn't resolve your issue some samples as I asked for would be useful to me as I am investigating a slightly different method but would like to know what I have to deal with.

Peter, the original code is THREE functions, one below the scroll fold of the code window.

With that one line changed, it returns the values per post #11

Function CorrectCase(ByVal inputString As String, ByVal caseListRange As Range) As String
    Dim arrWords As Variant
    Dim i As Long, Pointer As Long
    On Error GoTo Halt
    Pointer = 1
    CorrectCase = inputString
    arrWords = WordsOf(inputString)
    
    For i = 0 To UBound(arrWords)
        Pointer = InStr(Pointer, inputString, arrWords(i))
        Mid(CorrectCase, Pointer) = CorrectCaseOneWord(CStr(arrWords(i)), caseListRange)
    Next i
    
Halt:
    On Error GoTo 0
End Function

Function WordsOf(inputString As String) As Variant
    Dim Delimiters As Variant, aDelimiter As Variant
    Dim arrResult As Variant
    
    Delimiters = Array(" ", ",", ".", ";", ":", Chr(34), vbCr, vbLf, "(", ")"): Rem add to as needed
    
    For Each aDelimiter In Delimiters
        inputString = Application.Substitute(inputString, aDelimiter, Delimiters(0))
    Next aDelimiter
    
    arrResult = Split(inputString, CStr(Delimiters(0)))
    WordsOf = arrResult
End Function

Function CorrectCaseOneWord(inWord As String, caseListRange As Range) As String
    With caseListRange
        If IsError(Application.Match(inWord, .Cells, 0)) Then
            CorrectCaseOneWord = Application.Proper(inWord)
        Else
            CorrectCaseOneWord = Application.Lookup(inWord, .Cells)
        End If
    End With
End Function

 

[Peter_SSs]

Peter, the original code is THREE functions, one below the scroll fold of the code window.

Mike, yes I realised that subsequently.
I should have left you to answer questions about your code


@dchieu
I did notice when copying your larger set of sample data that you have a couple of 'unusual' space characters (Chr(160)) in your sample which may require some further modification of Mike's code unless he has dealt with that already.

In any case, here is another (single) UDF that you might like to try.
If your excludeDB contained "NYTM" but your text contained " NYtM " then my function currently would leave your text as " NYtM ", see row 9. If that scenario is possible and you wanted to convert it to " NYTM " then post back as that would be a fairly simple mod.

Function FixCase(ByVal inputString As String, ByVal caseListRange As Range) As String
  Dim Bits
  Dim i As Long
  
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "\b(" & Join(Application.Transpose(caseListRange.Value), "|") & ")\b"
    Bits = Split(Replace(inputString, Chr(160), " "))
    For i = 0 To UBound(Bits)
      If Not .Test(Bits(i)) Then Bits(i) = UCase(Left(Bits(i), 1)) & LCase(Mid(Bits(i), 2))
    Next i
  End With
  FixCase = Join(Bits)
End Function

Excel Workbook
	A	B	C	D
1	NY TECH MEETUP (NYTM)	NY Tech Meetup (NYTM)		II
2	CITIZENSHIP EDUCATION FUND OPENING RECEPTION	Citizenship Education Fund Opening Reception		NY
3	MONEY WHERE IT MATTERS: MANAGING CORPORATE, FEDERAL & FOUNDATION FUNDS	Money Where It Matters: Managing Corporate, Federal & Foundation Funds		NYTM
4	ACCESS TO CAPITAL PART II: SECURING THE MONEY MINORITY BUSINESSES NEED TO GROW	Access To Capital Part II: Securing The Money Minority Businesses Need To Grow
5	ACCESS TO CAPITAL LUNCHEON	Access To Capital Luncheon
6	Business of Hip Hop - Examining Next Steps	Business Of Hip Hop - Examining Next Steps
7	BUILDING IT TOGETHER: WHY STRATEGIC PARTNERSHIPS WORK	Building It Together: Why Strategic Partnerships Work
8	WHY DIVERSITY MATTERS	Why Diversity Matters
9	NY TECH MEETUP (NYtM)	NY Tech Meetup (NYtM)
10
Fix Case

#VALUE!

</td></tr></table></td></tr></table>

 

[dchieu]

Hi Peter,

Thanks again for the time you put into this. It works exactly as you have indicated.
The possibility of " NYtM " showing up is fairly good. I would like to give the user the complete freedom to update the excludeDB range with how he wishes any particular word to be spelled out according to his expectations.

If the modification is fairly simple, by all means, I would very much welcome it!

Sincere appreciation,
Donald

PS,
Thanks to you too Mike.

 

[dchieu]

Hello again Peter,

I just noticed an odd behavior. Subsequent to changing the initial string inputs with copy and paste, the udf no longer produces any result other than "#NAME?".

If re-calc is required, how do I go about it?

Thanks

 

[Rick Rothstein]

Function FixCase(ByVal inputString As String, ByVal caseListRange As Range) As String
  Dim Bits
  Dim i As Long
  
  With CreateObject("VBScript.RegExp")
    .IgnoreCase = True
    .Pattern = "\b(" & Join(Application.Transpose(caseListRange.Value), "|") & ")\b"
    Bits = Split(Replace(inputString, Chr(160), " "))
    For i = 0 To UBound(Bits)
      If Not .Test(Bits(i)) Then Bits(i) = UCase(Left(Bits(i), 1)) & LCase(Mid(Bits(i), 2))
    Next i
  End With
  FixCase = Join(Bits)
End Function

Peter, I think I see a minor problem with your function. If the range passed into the "caseListRange" argument includes (for but one example) the word "to" where both letters are lower case) and the text passed into the "inputString" argument is "ONE TO TWO", the "TO" is left untouched whereas I think is should become proper case ("To") like the other words.

 

[mikerickson]

Rick, if I remember correctly, the 2010 OP requirements were that in your scenario ("to" in the list), "ONE TO TWO" be turned into "One to Two".

 

[Rick Rothstein]

Peter, I think I see a minor problem with your function. If the range passed into the "caseListRange" argument includes (for but one example) the word "to" where both letters are lower case) and the text passed into the "inputString" argument is "ONE TO TWO", the "TO" is left untouched whereas I think is should become proper case ("To") like the other words.

One more thing related to your code...

I just saw another problem with your code. If a non-proper case word appears in parentheses and is not in the "caseListRange", it does not become proper case (actually, it becomes lower case). For example, this...

ONE (TWO) THREE

becomes this...

One (two) Three

 

[Rick Rothstein]

Rick, if I remember correctly, the 2010 OP requirements were that in your scenario ("to" in the list), "ONE TO TWO" be turned into "One to Two".

I took the words "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.)" to mean if it is in the text exactly as it is in the exclusion list, then leave it alone; and not to convert anything that uses the same letters (in any case arrangement) to what is in the list. Otherwise, using the quoted list, a sentence "It had its ups and downs" would become "It Had Its UPS And Downs".

 

[Peter_SSs]

... the udf no longer produces any result other than "#NAME?"

That would normally indicate that the name of the function in the code and on the worksheet do not match. Could you have accidentally changed one?

Function FixCase(ByVal inputString As String, ByVal caseListRange As Range) As String

=FixCase(A5,excludeDB)

Alternatively, it could relate to where the function code resides. Is it in a standard module in the workbook that it is being used in? If it resides in another workbook you would get #NAME?

Peter, I think I see a minor problem with your function. If the range passed into the "caseListRange" argument includes (for but one example) the word "to" where both letters are lower case) and the text passed into the "inputString" argument is "ONE TO TWO", the "TO" is left untouched ..

That was intentional, as stated in my post ..

If your excludeDB contained "NYTM" but your text contained " NYtM " then my function currently would leave your text as " NYtM ", see row 9.

The OP has since stated that it would be better to match exactly what is in 'excludeDB' and I have attempted to address that below, along with your other point about my code failing if the first letter of an 'ordinary' word was not the first character after a space - thanks for highlighting that.

The possibility of " NYtM " showing up is fairly good. I would like to give the user the complete freedom to update the excludeDB range with how he wishes any particular word to be spelled out according to his expectations.

If the modification is fairly simple, by all means, I would very much welcome it!

Wasn't quite as simple as I had anticipated, but give this version a try.

Function FixCase(ByVal inputString As String, ByVal caseListRange As Range) As String
  Dim Bits, aExcl
  Dim i As Long
  Dim pat As String, sExclAll As String, sExcl As String, Ltr As String, tmp As String

  sExclAll = Join(Application.Transpose(caseListRange.Value), "|")
  aExcl = Split("|" & Replace(sExclAll, "|", "| |") & "|")
  With CreateObject("VBScript.RegExp")
    .Global = False
    .IgnoreCase = True
    pat = "\b(" & sExclAll & ")\b"
    Bits = Split(Replace(LCase(inputString), Chr(160), " "))
    For i = 0 To UBound(Bits)
      .Pattern = pat
      tmp = Bits(i)
      If .Test(tmp) Then
        sExcl = .Execute(tmp)(0)
        Bits(i) = Replace(tmp, sExcl, Replace(Filter(aExcl, "|" & sExcl & "|", True, 1)(0), "|", "", 1, -1, 1))
      Else
        .Pattern = "[a-z]"
        If .Test(tmp) Then
          Ltr = .Execute(tmp)(0)
          Bits(i) = Replace(tmp, Ltr, UCase(Ltr), 1, 1, 1)
        End If
      End If
    Next i
  End With
  FixCase = Join(Bits)
End Function

Excel Workbook
	A	B	C	D
1	NY TECH MEETUP (NYTM)	NY Tech Meetup (NYTM)		II
2	CITIZENSHIP EDUCATION FUND OPENING RECEPTION	Citizenship Education Fund Opening Reception		NY
3	MONEY WHERE IT MATTERS: MANAGING CORPORATE, FEDERAL & FOUNDATION FUNDS	Money Where It Matters: Managing Corporate, Federal & Foundation Funds		CaMeL
4	ACCESS TO CAPITAL PART*II: SECURING THE MONEY MINORITY BUSINESSES NEED TO GROW	Access to Capital Part II: Securing The Money Minority Businesses Need to Grow		to
5	ACCESS TO CAPITAL LUNCHEON	Access to Capital Luncheon		NYTM
6	Business of Hip Hop - Examining Next Steps	Business Of Hip Hop - Examining Next Steps
7	BUILDING IT TOGETHER: WHY STRATEGIC PARTNERSHIPS WORK	Building It Together: Why Strategic Partnerships Work
8	WHY DIVERSITY MATTERS	Why Diversity Matters
9	NY TECH MEETUP (NYtM)	NY Tech Meetup (NYTM)
10	ThIs WaS iN cAmEl CaSe BuT nOw OnLy CaMeL!	This Was In CaMeL Case But Now Only CaMeL!
11	ONE (-TWO-) [THREE]	One (-Two-) [Three]
12	ONE ($$$) THREE	One ($$$) Three
Fix Case

#VALUE!

</td></tr></table></td></tr></table>