Or, manually click and drag to select the sells you wish to contain the split data. Highlight the columns you wish to contain the split data by clicking the letters directly above the columns (you can choose columns from anywhere within the spreadsheet).Click the red arrow/spreadsheet icon at the far right of the "Destination" text box.Under "Column data format," choose "General.".Check the box next to "Treat consecutive delimiters as one.".For example, if your column reads “Smith, John” you would select “Comma” as your delimiter. A delimiter is the symbol or space which separates the data you wish to split.In step 1 of the wizard, choose “Delimited” > Click.Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns." The "Convert Text to Columns Wizard" will appear.Highlight the column that contains the combined data (e.g., Last Name, First Name) by clicking the letter directly above the column.Open the Excel spreadsheet containing the data you want to split, then: Follow these steps to split the data from column A into a "Last Name" column and a "First Name" column. Suppose column A contains "Last Name, First Name". This, in essence, is a smarter way to copy and paste the data into new cells.Ĭlick inside the first cell of the appropriate column-the one named “First, in our example-and type in the first name of the first person in your dataset.In Excel (2016, 2013, 2010) it's possible to parse data from one column into two or more columns.
If you only have a few names, and you don’t want to mess with the Text to Columns Wizard, you can use Flash Fill instead. If this happens, just click inside the “Destination” area within the wizard or add the information manually into the Destination field. You may notice a chime and then an inability to select the cell you want to move the data into.
To do so, we’d highlight the first names in the wizard (notice the black highlight in the screenshot that signifies the active column) and then click the appropriate cell. We could do this differently-for example, adding first names to column B and last names to column C. Next, we’re going to click the cell where we want to start adding the data-in this case B2-and click “Finish.” This will add the first and last names to their respective columns. You can use any delimiter that fits your data set. In this case, we’re using comma and space because each cell in column A has a comma and a space separating the two. The Convert Text to Columns wizard opens. Also make sure to pass True to the expand parameter. The Data preview section shows you what your content would look like. Select the delimiter or delimiters to define the places where you want to split the cell content. Choose Delimited if it is not already selected, and then click Next. The Convert Text to Columns Wizard opens.
Excel split cells by dash series#
On the ribbon's Data tab, find the Data Tools section and click Text to Columns. Apply the pandas series str.split() function on the Address column and pass the delimiter (comma in this case) on which you want to split the column. On the Data tab, in the Data Tools group, click Text to Columns. In our example, a hyphen is the delimiter. Then select the cell whose contents you want to split. Delimiters are simply how the data is separated. Insert one or more empty columns to the right. Check both the “Comma” and “Space” delimiters and then the “Next” button.