Change Case - Excel XP 2

[dunkyn]

I need to change the case of data in an Excel file from ALL CAPS to Proper Case. That part is easy. =proper(a1)

Where I need assistance is in fixing things like:

Iii to III
Ma to MA (Massachusetts)
Mcintyre to McIntyre

You get the idea.

I'd like to make a table and run a macro to do the find and replace. I can find the data, but I don't know how to create a macro that finds all instances and then replaces them.

Can someone point me in the right direction?

If I can't figure this out we will have to result to a mail merge with Word...UGH!

TIA!!!!

 

[origapizni]

Assuming you can limit the finding to just the strings you want (you don't want to change all words starting with "Ma" to begin with "MA&quot, then here's several variations:

Select the range in question. Then

    With Selection   
        .Replace What:="Iii", Replacement:="III"
        .Replace What:=" Ma ", Replacement:=" MA "
        .Replace What:="Mcintyre", Replacement:="McIntyre"
        etc., etc., etc. ....
    End With

A little faster way is to use the Array() function (both arrays should have the same number of entries):

    With Selection
        ''' This uses the Array() function, which is NOT the same as a VBA array
        .Replace _
            What:=Array("Iii", "Ma", "Mcintyre"), _
            Replacement:=Array("III", "MA", "McIntyre")
    End With

You might want to store the arrays in Custom Lists in the workbook for future use and easy editing without having to edit code. See VBA Help for
Application.GetCustomListContents(1) and related methods.

And of course it's possible to build such a list on-the-fly in VBA.

HTH

 

[RobBroekhuis]

Depends on how specific or general you want to apply the rules. For example, it will be easy enough to get the state abbreviations right if they consistently appear in the same position. Searching for " ii" and " iii" and replacing shouldn't be difficult either. Similarly, if ALL instances of Mc can be assumed to be in need of transformation, that's doable (but how about Mac?)

For the first, go directly to the cells containing the state, parse the contents (assuming it's city, ST zip) and correct the case. For the second two, you'll need to use the range.find method. If you need help with that, let us know.
Rob

 

[dunkyn]

Thanks very much!!!

I'll try it out and come back to you. I appreciate your help!

 

[dunkyn]

This primitive macro works,

Public Sub ReplaceText()
>ActiveSheet.Columns("C:J&quot.Replace what:=" Iii ", Replacement:=" III ",
>SearchOrder:=xlByColumns, MatchCase:=True
>ActiveSheet.Columns("C:J&quot.Replace what:=" Ii ", Replacement:=" II ",
>SearchOrder:=xlByColumns, MatchCase:=True
>ActiveSheet.Columns("C:J&quot.Replace what:=" Vi ", Replacement:=" VI ",
>SearchOrder:=xlByColumns, MatchCase:=True
>ActiveSheet.Columns("C:J&quot.Replace what:=" Vii ", Replacement:=" VII ",
>SearchOrder:=xlByColumns, MatchCase:=True
>ActiveSheet.Columns("C:J&quot.Replace what:=" Viii ", Replacement:=" VIII
>",
>SearchOrder:=xlByColumns, MatchCase:=True
>ActiveSheet.Columns("C:J&quot.Replace what:=" Ma ", Replacement:=" MA ",
>SearchOrder:=xlByColumns, MatchCase:=True
>End Sub

I would like to use the array you specified, but this is as far as I have gotten:

Sub FixText()
>'
>On Error Resume Next
>
>listArray = Application.GetCustomListContents(1)
>For i = LBound(listArray, 1) To UBound(listArray, 1)
> Worksheets("sheet1&quot.Cells(i, 1).Value = listArray(i)
>Next i
>
>End Sub

How do I get it to read my list and replace the text in my database?

List Sample:
Va VA
Ww WW
Wa WA
Wv WV
Wi WI
Wy WY
Mcsherry McSherry
Mcintyre McIntyre
Mcdonald McDonald


Thank you.

Your assistance is very much appreciated.

 

[origapizni]

Here's how. First set up 2 custom lists, one for Find values and one for Replace values. Make the first entry in each ##FIND## and ##REPL## respectively. Any value would work, I just picked some that probably wouldn't be found in normal data. This step is so you can find it using VBA. Both lists should have the same number of entries.

VBA doesn't give you many tools to manipulate lists, so you have to check each list in the collection and see if it's the one you want. Thus the For...Next loop. Once you find them, they become the argument values (as arrays) in your Replace method.

Sub testListArray()

Dim n As Integer
Dim TEMPArray As Variant
Dim FINDArray As Variant
Dim REPLArray As Variant

For n = 1 To Application.CustomListCount
TEMPArray = Application.GetCustomListContents
If TEMPArray(1) = "##FIND##" Then FINDArray = TEMPArray: Cells(n, 4) = "FIND List"
If TEMPArray(1) = "##REPL##" Then REPLArray = TEMPArray: Cells(n, 4) = "REPL List"
Worksheets("sheet1&quot.Range(Cells(n, 5), Cells(n, 4 + UBound(TEMPArray))) = TEMPArray
Erase TEMPArray
Next n

With Selection
''' This uses the VBA arrays you created in the For...Next loop above.
.Replace _
What:=FINDArray, _
Replacement:=REPLArray
End With

End Sub

You should put in error handling as well. I left it out due to lack of time.