Married couple first names

Hi Steve,

Did you check above replies? If you still need further support, please do not hesitate to reply to me.

Best regards,

Jazlyn

You already have a bunch of good suggestions.

.

Are you going to be getting more data later?

Where does your new data come from. Could you pull it from some other computer source or do you have to type it manually?

.

If you can pull new data in from another computer or file then you may want to take advantage of the automation built in to PowerQuery.

PowerQuery allows you to pull data in from MANY sources. Then you can manipulate the data in very many ways. In this case you will be splitting the 2 first names.

What about "blended" families with 2 moms and a dad , or visa versa?

After you set up the query you can use the refresh function to pull in new data.

.

If you are interested I can give you some links to get started.

@Steve Lilgreen,

Note that I edited my previous post. The formula goes in cell B2, not A2 as I initially had in both the text and screen shot (but the formula references cell A2.)

My apologies for the error and this post to ensure you get an email notification because notifications not sent for edits.

If you are using Excel in Office 365 then the following UDF (User Defined Function) is also a solution. (Does not work in earlier versions of Excel. Not sure about Excel 2019 and 2021)

Not sure of your expertise here so I will include full guidelines to install the code.

1. Alt and F11 to open the VBA editor window
2. In the VBA editor select menu item Insert -> Module
3. Copy the VBA code below and paste into the VBA editor.
4. Close the VBA editor (Cross very top right of VBA editor window)
5. Save the workbook as Macro enabled.

Ensure macros are enabled as follows.

1. If Developer ribbon is displayed then go to step 3. Otherwise right click anywhere in one of the ribbons and select "Customize the ribbon".
2. On the right side of the dialog check the box against "Developer" and click OK. (Ensure you Click OK and don't just click the cross top right of dialog to close it or the change does not get saved).
3. Select Developer ribbon.
4. Select Macro Security (In the Code block at left end of Developer ribbon).
5. The dialog that displays should default to Macros in left column but if not, Select Macros)
6. Select required security option button. (Option to "Disable all macros with notification" should be OK.)
7. Click OK to close the dialog.

Copy the code below from Function Separate to End Sub and paste into the VBA editor***.***

Function Separate(rng As Range) As Variant '(Variant so it can be an array)

Dim strToSplit As String 

Dim arrSplit As Variant 

strToSplit = Replace(rng.Value, " ", "")    'Remove all spaces from string and save result in strToSplit 

Separate = Split(strToSplit, "&")           'Assign strings separated by "&" to a zero based array 

End Function

ScreenShot with more guidelines on using the UDF in the worksheet. Note the formula that goes in cell B2 and copied down.