From MCDONALD to Mcdonald to McDonald

[Joey Jr]

Hi All,

I use the PROPER function to turn uppercase to sentence case when composing a mailing list.

Is there any way to capitalise the letter after the Mc or Mac at the start of certain surnames, without searching for 'Mc' and manually amending it?

Thanks

[rrdonutz]

A related post that unfortunately doesn't really answer your question, except to point out the problem(s) with your request:

http://www.mrexcel.com/board2/viewtopic.php?t=70919

Maybe al_b_cnu's fuzzy lookup UDF can help, though?

BOL,

Tom

[Joe4]

You could write a macro to do the Search and Replace for you, but as the thread rrdonutz referenced suggests, there may be too many possibilites and exceptions to program something reliable.

[DRJ]

Try this:

=IF(LEFT(A1,2)="mc","Mc"&UPPER(MID(A1,3,1))&LOWER(MID(A1,4,LEN(A1))),IF(LEFT(A1,3)="mac","Mac"&UPPER(MID(A1,4,1))&LOWER(MID(A1,5,LEN(A1))),A1))

[rrdonutz]

Hi DRJ,

Your formula works, but . . . try it with names like:

Mack
Macklin
Mackovic

I doubt these folks want their names to become:

MacK
MacKlin
MacKovic

These are just a few of the exceptions that need a "work-around".

[DRJ]

Thats true. I suppose you could look at the letter after the mac or mc and if it is a D then capitalize it, or other letters that are likely to need to be capitals, but it is not 100%.

Of course the OP just wanted to cap the letter after mac or mc so that's all I tried to do.

[ultratch47]

will you be using the same names every time?
If no new names are expected, then just program a macro around the names you have..

[Ekim]

Joey,

One way:

Setup a matrix in the worksheet named “Setup":

******** ******************** ************************************************************************>

Microsoft Excel - Replace-Global Replace Words2.xls ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A1 =
	A	B	C	D	E	F	G	H
1	*	*	*	*	*	*	*	*
2	Incorrect*Word	Correct*Word	*	*	*	*	*	*
3	MCDONALD	McDonald	*	*	*	*	*	*
4	Mcdonald	McDonald	*	The*cells*in*green*are*named*"myRng".				*
5	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*
9	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*
11	*	*	*	*	*	*	*	*
Setup *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The green cells are named “MyRng” – make it a dynamic range so that it auto corrects for additions/deletions.

Put the following macro in a normal module and assign it to a button from the Forms menu. The macro works on the Activesheet.

Option Explicit
Sub replaceWord()
Dim myReplace As Variant
Dim i As Integer

 With ThisWorkbook.Worksheets("Setup")
    myReplace = .Range("MyRng").Value
  End With

  For i = 1 To UBound(myReplace)
    If Not IsEmpty(myReplace(i, 1)) Then
      ActiveSheet.Cells.Replace What:=myReplace(i, 1), _
            Replacement:=myReplace(i, 2)
    End If
  Next i

End Sub

Another way is to update your autocorrect file so that the names change to the correct format as you input (assuming that you are manually entering the names).

HTH

Mike