Click the red arrow/spreadsheet icon once more to return to the wizard.Now choose Data Data Tools Flash Fill (or press CTRL+E). Move to cell B2, and type the second number 6 in this cell. I selected just Tab and Comma but could also select Space to get rid of the extra space. Or, manually click and drag to select the sells you wish to contain the split data. Step 1: To use Flash Fill with our sample example, click on cell B1 to select it. Select the column that you want to split: Then go up to Data -> Text to Columns: You’ll get the following window: This window gives you several options for splitting the cells, using commas, spaces, semicolons, tabs, other, etc. 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).But you dont have to store your text in variable. The text for us is held in the variable called txt. To the right of the equal sign we have the Split function: Split (txt, ' ') The first item between the round brackets of Split is the text we want to split. A delimiter is a character, symbol, or space that indicates the beginning or end of a data item. The pieces of the string will be split and stored here, turning FullName into an array. On the Home tab of the Ribbon, go to the Split Column menu and choose By Delimiter. The first thing we are going to do in the editor is split our column. Click the red arrow/spreadsheet icon at the far right of the "Destination" text box. When you click OK, the Power Query Editor will open.Under "Column data format," choose "General.".Whereas in the Delimited splitting mode the splitting process starts when Excel encounters. This will split the text at 27th, 14 th, or 35th characters. Using the fixed-width splitting in Excel you can split your text after a specific number of characters. This allows us to split the text at each occurrence of specific characters. The Convert Text to Columns Wizard dialog box will open. From the ribbon, click Data > Data Tools (Group) > Text to Columns. Check the box next to "Treat consecutive delimiters as one." At the time of splitting text to columns, it’s very important to know how and where to do that split. Select the cell or cells containing the text to be split.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". In Excel (2016, 2013, 2010) it's possible to parse data from one column into two or more columns.