PROPER、UPPER、LOWER の使い方と落とし穴

I have never seen PROPER leave a “Mc” in place. I typed three versions of the same name into a test tab — mcdonald, McDonald, and MCDONALD — and all three came back as Mcdonald. The internal capital vanished even when it was already correct.

=PROPER("mcdonald")    → Mcdonald
=PROPER("McDonald")    → Mcdonald
=PROPER("MCDONALD")    → Mcdonald
=UPPER("McDonald")     → MCDONALD
=LOWER("McDonald")     → mcdonald

UPPER and LOWER did what they say. I use those when I actually want a uniform case, or when I am normalizing a label I will display. I do not use PROPER as a name-case button. Microsoft’s PROPER function page is clear about why: it capitalizes the first letter, and any letter that follows a character that is not a letter, and it forces every other letter to lowercase. There is no special case for “Mc”, “Mac”, or a brand capital in the middle of a word.

The same rule showed up everywhere else I tried.

=PROPER("o'neil")             → O'Neil
=PROPER("O'NEIL")             → O'Neil
=PROPER("sku-103")            → Sku-103
=PROPER("iPhone 12")          → Iphone 12
=PROPER("Mary-Jane")          → Mary-Jane
=PROPER("221b baker street")  → 221B Baker Street

The apostrophe in O'NEIL is not a letter, so the N after it stayed (or became) capital: O'Neil. A hyphen is also not a letter, so Mary-Jane survived and sku-103 became Sku-103. I would not run PROPER down a SKU column; leading-zero and casing issues on SKUs are already messy enough, and Keep Leading Zeros is the better place to start. iPhone 12 lost the P. 221b baker street picked up a capital B after the digit, which is the rule working as written, and probably not what a mailing list wants if you were trying to tidy addresses.

UPPER and LOWER of the same cells were simple: SKU-103 / sku-103, IPHONE 12 / iphone 12, 221B BAKER STREET / 221b baker street. Hyphens and digits were left alone. Microsoft’s UPPER and LOWER pages do not add extra rules, and I did not see any in this file.

Numbers, empty cells, and lookups

A12 was the number 12345, not text. UPPER, LOWER, and PROPER all displayed 12345, LEN was 5, and ISTEXT of the PROPER result was TRUE. The functions accepted a number and handed back text. I did not then try to add that result to anything; I am only reporting the type.

PROPER of an empty cell returned blank, with LEN 0. No error.

If the job is a lookup rather than a display cleanup, changing case may not be the lever you think it is. XLOOKUP and COUNTIF in this workbook have already been case-insensitive; folding a SKU to UPPER will not separate SKU-103 from sku-103. That write-up is Case-Sensitive Lookup. And if you are splitting a full name before you even think about PROPER, start with Split a Full Name — I would rather split first and then decide, per field, whether a mechanical PROPER is even acceptable.

I do not have a general substitute recipe that restores McDonald or iPhone. SUBSTITUTE can patch known tokens, but that is a word list, not a case function. See SUBSTITUTE vs REPLACE if you go that way. For a one-off list, I still fix the exceptions by hand.