Sometimes when working with excel, you may find that your workbook contains jumbled-up data of texts and numbers. Such data may be hard to read or understand hence the need for a separation.
Luckily, it is possible to separate texts and numbers in excel using the different features and functions it offers. Here, we give you different methods you can achieve this.
Steps on how to separate texts from numbers in excel using the Text to Column method
1. On your computer, open the excel file that contains all the jumbled data.
2. Select the first column that contains the data you want to separate into different columns.
3. On the main menu, click the Data tab.
4. Next, click the Text to Columns icon under the Data Tools section. Doing so will display a pop-up window.
5. In the 'Convert Text to Columns wizard, select the Delimited option. In case your excel data is small and separated by spaces, select the option 'Fixed Width.'
6.
Click 'Next' to see a preview of your selected data. Here you can manually adjust your column separators.
7. In your next step, you can choose to specify the delimiters and text qualifiers. Here you will get to choose between tab, semicolon, comma, space, or other. Afterward, click the Next button.
8. Choose the data format and specify where you want to paste the split data.
- Under the column data format, by default Excel selects 'General' which works well in most cases. If you would like a specific data format, click on the column under Data Preview and choose your preference.
- On the destination section, select the place where you want to output your separated data.
9. Finally, click on the Finish button. You will notice that Excel has split and separated your numbers and texts into different cells.
Using Flash Fill to separate text and numbers in excel
Flash Fill is an easier way of separating texts and numbers in your excel worksheet. It is the preferred choice. Hereis what to do;
1. On your computer, open the workbook that you need to separate the texts and numbers.
2. Provide an example by typing in the numbers of your first text string is a blank adjacent cell.
3. from there, select the range where you want to fill in the other separated numbers.
4. On the main menu, click the Data tab.
5. Select the option Flash Fill. You will notice that Excel automatically fills up the other cells with only numbers.
6. Afterward, type in your text in a new column. Select the cell range you want to fill your separated text.
7. Go to the Data tab and click on Flash Fill. Afterward, excel will automatically fill up the selected blank cells with your text.
Using Excel formulas to separate numbers and texts
You can also use an excel formula to split a cell that contains numbers and texts. All this boils down to you finding out where the delimiter is and extracting a substring before, after, or in-between the delimiters. Here, you will use the SEARCH and FIND functions. You will also use Text functions such as the LEFT, RIGHT, or MID to separate the substring.
In case of they FIND function, you can use the generic formula;
= MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1 & "0123456789"))
For example
The above example returns 7 as your result. It is the position of the number 3 in the apple30 string.