Look Who it Was Random video

Excel - Excel Trick: Capitalize Interior 'C' in Names like McCartney - Episode 722

264 views · Published 13 February 2009 · 2:29 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Excel Trick: Capitalize Interior 'C' in Names like McCartney.

Welcome back to the MrExcel netcast! In this video, I'll be sharing a helpful Excel trick that I usually demonstrate in my seminars. It involves using the PROPER function to combine text from two cells and automatically capitalize the first letter of each word. However, there's always one pesky exception - when the second record is a name like Paul McCartney, the interior 'C' in McCartney doesn't get capitalized. But thanks to a suggestion from a fellow Excel enthusiast, I now have a solution for this issue.

During a recent seminar at Jefferson Wells in Chicago, a participant named Adrian shared a great solution for this problem. Instead of just using the PROPER function, we can add an IF statement to check if the last name starts with "Mc". If it does, we can use the PROPER function to capitalize the first letter of the last name, including the interior 'C'. If not, we can simply use the original formula. This is a much more efficient solution compared to manually changing the formulas or values.

To implement this trick, we use the LEFT and MID functions to extract the first two letters of the last name and the rest of the name respectively. Then, we use the PROPER function to capitalize the first letter of the last name and add it to the rest of the name. This ensures that the interior 'C' in McCartney is capitalized, along with the rest of the name. This is especially useful for names with Irish origins, where the interior 'c' is often capitalized.

I want to thank Adrian for sharing this brilliant solution with me and allowing me to share it with all of you. This is by far the best solution I've come across for this issue and I hope it helps you save time and effort in your Excel work. Don't forget to give this video a thumbs up and subscribe to the MrExcel channel for more helpful Excel tips and tricks. Thanks for watching and I'll see you in the next netcast!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


The PROPER function never manages to capitalize the interior C in McCartney. In today's podcast, an interesting way to solve this problem with a formula. Episode 722 shows you how.

Table of Contents:
(00:00) Capitalize Second C in McCartney
(00:20) Using the PROPER function
(00:30) Issue with second record
(01:00) IF function
(01:35) Original formula
(01:46) Successful result
(02:00) Best solution encountered
(02:10) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
Copy visible cells in Excel with autofilter
Creating a unique list of codes in Excel
How to turn on autofilter in Excel
Leveraging Excel's built-in functionality to solve problems
Pasting data in a specific cell in Excel
Running a macro in Excel
Selecting and copying a column in Excel
Turning off autofilter in Excel
Understanding the code generated by the macro recorder
Using autofilter instead of looping through rows in Excel
Using the macro recorder in Excel
VBA code to find non-blank cells in column I

Join the MrExcel Message Board discussion about this video at  https://www.mrexcel.com/board/threads/1152173/

More from this channel