Excel: How to add Mr or Mrs to names
In some situations, you may need to add "Mr" or "Mrs" before names in Excel, as shown in the screenshot below. Instead of manually typing these prefixes one by one, you can use a few simple techniques to quickly add "Mr" or "Mrs" to multiple names at once. This tutorial will guide you through different methods, including using formulas, custom formatting, and the IF function based on gender.
Using the connector (&) to add Mr or Mrs
Formatting cells as custom to add Mr or Mrs
Using the IF function to automatically add Mr or Mrs based on gender
Note: The methods provided in this tutorial are tested in Excel 2021; there may be some differences in different Excel versions.
The general method of adding characters to a cell is using the connector (&).
Supposing to add Mrs to a list of names, please select a cell and type the formula as this:
="Mrs"&" "&A2
Press the Enter key, then drag the autofill handle (bottom-right corner of the first result cell) down to fill the formula into the cells.
The built-in feature Format Cells also can quickly add Mr or Mrs to the names.
Select the names that you want to add Mrs or Mr, and right-click to show the context menu, select Format Cells.
In the pop-up Format Cells dialog, under the Number tab, select Custom from the Category pane, then type “Mr “@ into the Type textbox. Click OK.
If you want to add Mrs, type “Mrs “@.
Now all selected names have been added Mr in the front.
If there is a table with two columns, one is names, the other one is genders, and you want to add Mr or Mrs to the names based on the genders, you can use the IF function.
Select a cell, and use the below formula:
=IF(B2="Female","Mrs "&A2,"Mr "&A2)
In the formula, B2 is the cell containing gender, A2 is the name you want to add Mr or Mrs.
For more details of IF function, please click here.
Then drag the auto-fill handle down to apply this formula to other cells.
Note that the contents in a picture will be changed as the cell contents change.
How to add text to specific parts of multiple cells in Excel |
Easily add text or characters to the beginning, middle, or end of multiple cells in Excel using the Add Text utility of Kutools for Excel. This feature helps you make bulk edits quickly and efficiently without manually modifying each cell. |
 |
Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now |
How To Fix/Lock Background Color In A Sheet
Sometimes, when you share a sheet with others to edit, you may want to lock the background color in a range of cells and prevent them to be changed.
How To Reduce Excel File Size?
Sometimes, it will take minutes to open or save if the Excel file is too large. For solving this problem, here in this tutorial, it tells you how to reduce the Excel file size by removing the contents or formats which are unnecessary or never used.
Excel: How To Create Or Insert A Bookmark
Have you ever imagined creating or inserting a bookmark for quickly jumping to a specific data range while there is a lot of data in a worksheet or a workbook?
How to apply shading to odd or even (alternative) rows/columns in Excel?
While designing a worksheet, many people tend to apply shading to odd or even (alternative) rows or columns in order to make the worksheet more visual. This article will show you two methods to apply shading to odd or even rows/columns in Excel.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!