Names like McNeed etc.

[drums4monty]

Hi and a Happy New Year to you all.

I use this formula in the validaion menu of a worksheet to make the user enter proper names and not use all Caps or Lowercase:

=EXACT(A8,PROPER(A8))

The trouble is it will not allow the user to enter names such as O'Neill or McNeed etc. Is there a way to do this?

Alan

[Erdin? E. Ka]

Hi Alan,

You can try Tools | Auto Correct Options:
Replace: MCNEEd
With: McNeed

[drums4monty]

Thaks Erdinc but this would not solve the problem.

Many thanks

Alan

[d4vem]

Hi

The formula below will work for names starting with a "Mc" or "Mac" you can expand it to cater for other names by adding more if statements within the formula.

=IF(LEFT(A8,2)="Mc",EXACT(RIGHT(A8,LEN(A8)-2),PROPER(RIGHT(A8,LEN(A8)-2))),IF(LEFT(A8,3)="Mac",EXACT(RIGHT(A8,LEN(A8)-3),PROPER(RIGHT(A8,LEN(A8)-3))),EXACT(A8,PROPER(A8))))

[XLGibbs]

REPLACE(B4,FIND("c",B4)+1,1,PROPER(MID(B4,FIND("c",B4)+1,1)))

works for Mac or Mc and replaces the letter to the right of the c with a Capital...

With names it would be a never ending battle, perhaps a UDF

[d4vem]

I would suggest a Function based on either formula posted. This should be based on the range of initials that begin within a name entered.

ie if you focus on the first 2 initials the formula will always be the same as will 3,4 etc,etc.

So Mc will utilise the first 2 Initials in the if function and Mac will follow the first 3 initials in the if function that way you will not need to have a big "if" formula for each name but determine the position of the secondary capital letter based on the name stated in the if formula.

Within the function you will have to declare the individual names and not change the underlying code.

Declare the start of the name and what the return value should be

Mc=2
Mac=3
O'=2

[XLGibbs]

Yes, but even then there would be a seemingly heavy load of various precedents to cover.

McA
McD
McC
McK
McN
etc are just some that I know from within 100 feet of my cube

[d4vem]

XLG

But what I am saying is that regardless of the third letter if the first two start with Mc then all that needs to be validated is that the third initial is entered in upper case (as per my formula earlier). In regards to Mac it needs to validate that the fourth initial is in upper case.

Which begs another question. How many surnames names other then Mc, Mac, O', D' start with anything greater than three initials or symbols followed by a capital? Not many I would presume unless you have a double barrelled name (untested) but not part of the original post.

Dave

[XLGibbs]

But the function would still have to define a number of conditions, not all of the combinations would require the alteration. I wasn't questioning the need for a function, but more that the OP needs to re-evaluate the need to do this as it would be very difficult to account for everything properly.