2011 Excel Split First And Last Names Into Two Cells Download 100 Different

This is a very common issue in Excel and you can probably do a Google search and download 100 different macros written by various people to do it for you.

If you use Excel a lot, its probably a good idea to learn some of the more advanced functions so that you can do more interesting things with your data.

In addition, the text to columns feature is also better to use if you have more than two items in a cell you need to separate.

For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated.

Whenever I have seen a middle initial, its usually firstname midinitial lastname like below.

In my case, were going to use two functions: left and search.

In the second case, we are looking for a comma instead of a space.

Great So depending on how your data is stored, you have now extracted either the first name or the last name.

This takes two arguments also: the string and the number of characters you want to grab starting from the end of the string going left.

So we want the length of the string minus the position of the space or comma.

That will give us everything to the right of the first space or comma.

So how do we just get the last name and get rid of the middle initial Easy Just run the same formula again that we used to get the second section of the name.

It will find the space after the middle initial and then take the length minus the position of the space number of characters off the end of the string.

Its a featured called Text to Columns and it works very well.

Its also much more efficient if you have a column that has more than two pieces of data.

I would like to split that into 4 columns and 5 columns, respectively.

As you can see, trying to use the formulas above would be impractical.

Then, go ahead and click on the Data tab and then click on Text to Columns.

In step 1, you choose whether the field is delimited or fixed width.

You can pick from tab, semicolon, comma, space or type a custom one in.

Normally, General will work just fine for most types of data.

2011 Excel For Mac Split First And Last Names Into Two Cells