Excel for mac split data by demiliter how to#

It makes the data easier to skim through and work with. If you are having problems separating names not in the format I have above, post a comment with your data and I’ll try to help.When working with data and spreadsheets, readability, and structure matter a lot. The Text to Columns feature is very powerful and can make your life a lot easier. As you can see, one row turned into five columns and the other one into four columns. If you have something specific like dates, then choose that format.Ĭlick Finish and watch how your data is magically separated into columns. Normally, General will work just fine for most types of data. You can pick from tab, semicolon, comma, space or type a custom one in.įinally, you choose the data format for the column. On the next screen, you will choose the delimiter. In step 1, you choose whether the field is delimited or fixed width. This will bring up the Text to Columns wizard. Then, go ahead and click on the Data tab and then click on Text to Columns. In Excel, first select the column you want to separate.

As you can see, trying to use the formulas above would be impractical. I would like to split that into 4 columns and 5 columns, respectively. It’s also much more efficient if you have a column that has more than two pieces of data.įor example, below I have some data where one row has 4 pieces of data and the other row has 5 pieces of data. It’s a featured called Text to Columns and it works very well. There is also another easy way you can separate combined text into separate columns in Excel. So there you have it! You have now split the first name and last name into separate columns using a few simple formulas in Excel! Obviously, not everyone will have their text formatted in this way, but you can easily edit it to suit your needs. 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. So we are just doing another right and this time applying the formula on the combined middle initial and last name cell. 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. Great, now we have the second part of the name! In the first two cases, you’re pretty much done, but if there is a middle initial in the name, you can see that the result still includes the last name with the middle initial. That will give us everything to the right of the first space or comma. So we want the length of the string minus the position of the space or comma. 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. – Search the text in the cell for a space or comma, find the position and then subtract the position from total length of the string. Great! So depending on how your data is stored, you have now extracted either the first name or the last name. We got the first name from row 3, the last name from row 5 and the first name from row 7. So what is the result for the 3 scenarios I have mentioned? In the second case, we are looking for a comma instead of a space. In the first case, we search for a space by using double quotes and putting a space in-between. The -1 is there to remove the extra space or comma at the end of the string.Īs you can see, we start out with the left function, which takes two arguments: the string and the number of characters you want to grab starting from the beginning of the string. Here’s a simple formula that gets the job done correctly: =LEFT(NN, SEARCH(” “, NN) – 1), where NN is the cell that has the name stored in it. Search the text in the cell for a space or comma, find the position and then take out all the letters to the left of that position. In my case, we’re going to use two functions: left and search. Let’s start with extracting the first part of the name. Using some simple formulas and combining a couple of them together, you can easily separate the first name, last name and middle initial into separate cells in Excel.

Whenever I have seen a middle initial, it’s usually firstname midinitial lastname like below : The most common two ways I have seen are firstname lastname with just a space and lastname, firstname with a comma separating the two. To get started, let’s see how names are usually stored in a Excel spreadsheet. For example, if one column has 6 fields combined together, then using the formulas below will become really messy and complicated. 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.

Excel for mac split data by demiliter how to#

If you don’t like formulas and want a quicker solution, scroll down to the Text to Columns section, which teaches you how to use an Excel feature to do the same thing.