Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Capitalizing Just a Surname.
Cheryl is using a worksheet that has, in column A, client names in the format "Smith, Jane." She would like to capitalize only the surname, as in "SMITH, Jane", leaving the rest of the name unchanged.
If there is one and only one comma that separates the surname from the first name, you can create a formula to do the conversion. Assuming the name is in A1, the formula would be:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
=UPPER(LEFT(A1,FIND(",",A1)-1))&MID(A1,FIND(",",A1),LEN(A1))
If you prefer to not use a formula (which may mess up the look of your worksheet), you could also use a macro to convert the names, in place. Consider the following:
Sub CapitalizeSurnames()
Dim rCell As Range
Dim iComma As Integer
For Each rCell In Selection
iComma = InStr(rCell, ",")
If iComma > 0 Then
rCell = UCase(Left(rCell, iComma - 1)) & _
Mid(rCell, iComma)
End If
Next
Set rCell = Nothing
End Sub
Simply select the cells that you want to convert (such as those in column A) and then run the macro. It makes the conversion to the names in the cells.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12639) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Capitalizing Just a Surname.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Sometimes the data in a worksheet isn't in the exact format desired. If you want to dividie your values by 1,000, there are a ...
Discover MoreGot a worksheet in which there may be entire columns that are duplicates of each other? If you want to delete those duplicate ...
Discover MoreWe all make mistakes. Fortunately, Excel makes it rather easy to undo your makes, right after you make them.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2017-06-18 13:22:53
Willy Vanhaelen
Here is a one line macro that does the job.
Sub CapitalizeSurnames()
Selection = Evaluate(Replace("IF(ISERROR(FIND("","",@)),@,UPPER(LEFT(@,FIND("","",@)-1))&MID(@,FIND("","",@),LEN(@)))", "@", Selection.Address))
End Sub
2017-06-17 06:27:49
John
Ah, good point.
I read it completely the wrong way and didn't test it either. Unforgiveable. :-)
Thanks for the correction - I'll be more careful when commenting (and reading!) in future.
John
2017-06-16 09:15:45
Bryan Marks
John, you raise a good point about the complexity involved with the capitalization of last names. However, the point of this tip is to capitalize the entire surname (e.g., "de la Croix" would become "DE LA CROIX", "Macdonald" would become "MACDONALD"). The code above does just that.
2017-06-16 05:18:34
John
This is far too simplistic - what about, say, 'de la Croix', or 'd'Oliviera', or 'Macdonald' and 'MacDonald'. It's pretty much impossible to cover every possibility, particularly as some surnames with exactly the same letters are capitalised differently depending on the person whose surname it is! I guess this is a reasonable approximation but definitely not a comprehensive solution.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments