How to use Excel formula to split text

For many different sorts of data, the Text to Columns Wizard performs admirably. However, this wizard cannot separate all types of data. For instance, with this approach, you cannot separate data that is not fixed-width or that lacks delimiters ( Text to Columns Wizard). You can benefit from the Flash Fill functionality in situations like these. Remember that Flash Fill only functions well when the data is reliable. Flash fill is also an Excel formula to split text.

Flash Fill separates data using pattern recognition. Let's take an example where you have a column with text in various rows.

The worksheet in the next picture has some text in one column. We want to take the numbers out of each cell and place them in their own column. This division cannot be done via the Text to Columns Wizard because the space delimiters are inconsistent.

Delimiters are not used consistently, as shown in the first row where the number 20 is placed after three spaces and the second row where the number 6 is placed after two spaces, etc.

Therefore, we are unable to use the Text to Columns Wizard to separate the numbers from the text. This sort of number can be separated from the text using an array formula, however creating an array formula is difficult.

1. Click cell B1 to pick it in order to apply Flash Fill with our sample example. In cell B1, type the first number (20). Enter the second 6 in cell B2 after moving there. Select Data Data Tools Flash Fill at this point, I.e. Excel formula to split text or just press CTRL+E. You'll observe that Excel automatically fills the remaining cells. The next illustration depicts how your new column will appear.

As you can see, Excel estimates certain values incorrectly when we manually enter them in fields B1 and B2.

2. As you can see in the image up above, Excel has correctly detected the majority of the numbers. But there are also some incorrect assumptions. More examples will help you be more accurate. 

You can enter a decimal number in a new column, for instance, as some of your numbers have decimal values. Enter 3.12 in cell B6, delete the recommended numbers, and then click Ctrl + E. or 3.14159 can be entered into a B4 cell. You'll see that Excel has them all right this time. View the screenshot below. This is perfect Excel formula to split text.

Excel correctly inputs all of the decimal numbers once you enter an example.

3.You should keep the following in mind when using Flash Fill, perfect Excel formula to split text.

After utilising Flash Fill, you must carefully examine your data. If you recognise that the first few rows are accurate and presume that Flash Fill was successful for all rows, a major error may result. More examples boost the accuracy of Flash Fill.

Did you learn about Excel formula to split text. You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.

You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience