How to split data into multiple columns in Excel

If you start an Excel workbook by grouping data in the same cell and later decide to ungroup it, Excel has several simple tools that can split a spreadsheet column in two. Next, se explica cómo usar tanto “Text to columns” What “Flash fill”.

How to use “Text to columns” in excel

Select the cells you want to split by clicking the first cell and dragging down to the last cell in the column. In our example, we will divide the first and last names listed in column A into two different columns, column B (last name) and column C (Name).

Click on the tab “Data” en la parte de arriba de la cinta de Excel.

Click the button “Text to columns” in the ribbon at the top of the Excel window.

In the Convert Text to Columns Wizard, select “we will select all the full names that we want to separate” and then click “Next”. Delimited works great in our example, since the names are separated by commas. If the names were separated only by a space, podría elegir “Fixed width” instead.

Marque los delimitadores de “Coma” and “Space” y posteriormente el botón “Next”. The delimiters are simply how the data is separated. For this case, we use a comma and a space because each cell in column A has a comma and a space that separates them. You can use any delimiter that fits your dataset.

Next, we will click on the cell where we want to start adding the data, in this circumstance B2, and later in “Finalize”. This will add the first and last name to their respective columns.

We could do this differently, as an example, adding names to column B and last names to column C. To do it, we would highlight the names in the wizard (notice the black highlight in the screenshot which means the active column) and then click on the respective cell.

You may notice a ringing and later the inability to choose the cell to which you want to move the data. If this happens, simplemente haga clic dentro del área “Destiny” dentro del asistente o agregue la información manualmente en el campo Destino.

How to use “Flash fill” in excel

If you only have a few names and don't want to mess with the Text to Column Wizard, you can use Flash fill instead. This, in essence, it is a smarter way to copy and paste the data into new cells.

Click inside the first cell of the respective column, la que se llama “First, in our example” y escriba el nombre de la primera persona en su conjunto de datos.

Press “Enter” en el teclado para pasar a la próxima celda hacia abajo. In the tab “Beginning” of the tape, click on “Edit” and later in “Flash fill”.

Alternatively, you can press Ctrl + E on your keyboard.

Flash Fill will try to figure out what you are trying to achieve, adding only the first names in this example, and paste the results into the corresponding cells.

In second place, click inside the first cell of the last column and type the last name of the appropriate person, and press “Enter” on the keyboard.

In the tab “Beginning”, click on “Edit” and later in “Flash fill”. Or use the keyboard shortcut Ctrl + E.

One more time, Flash Fill will try to find out what data you want to fill in the column.

If Flash Fill does not work properly, there is always Undo (Ctrl + WITH).