Hello Excellers, welcome back to another #Exceltip blog post in my 2021 series. Today I will show you how to use flash fill to split your cells into separate columns. Yes, that is correct. It is an alternative to Text To Columns that you may currently use. Instead, we can use the Flash Fill option in Excel to do this. Flash fill in Excel is an intuitive function in Excel. Essentially Excel tries to guess the pattern of what you are trying to do in your Excel worksheet. Once you give Excel a few entries in your data set to show the type of content you need, then Excel will kick in with the Flash fill and do the rest of the hard work for you.
So, if you use any version of Excel 2013 to Excel 2019, the Flash Fill feature can automatically populate cells with data and split cell contents.
Let’s work with a sample data set. Below is a list of last and first names, all in one cell. This type of formatting is limited in its use as is. I need to split the first and second names. The screen grab below is before Excel splits the cells.
Not a difficult job for Excel flash fill.
How To Split Cells in Excel With Flash Fill.
- Insert a new column next to the column with the original data and type the text in the first cell that you want to split. In this case, it is the second text string in the cell.
- Type the text in a couple more cells. As soon as Excel senses a pattern, it will automatically populate similar data into other cells. How cool is that, right?
- Hit enter to accept the flash fill from Excel.
- Job is well and indeed done!