Want to share these videos with a colleague? Use this link:
DIY Data - Extra Materials: https://help.pm.leapevent.tech/a/1196777
Excel Magic! Using a Nested Formula to Fix Last Names with the "Mc" Prefix
Here's the formula from the video, where C2 represents the value in the Last Name column:
=IF(LEFT(C2,2)=”Mc”,”Mc”&PROPER(RIGHT(C2,LEN(C2)-2),C2)
Click to copy
And here's the formula adjusted for the prefix "Mac" instead:
=IF(LEFT(C2,3)=”Mac”,”Mac”&PROPER(RIGHT(C2,LEN(C2)-3),C2)
Click to copy
With the adjusted formula, make sure to adjust the number of characters in the LEFT formula, the text to match, and the number of characters to subtract from the RIGHT formula!