How To Separate Text And Numbers From One Cell Into Two Columns?

How To Separate Text And Numbers From One Cell Into Two Columns?

If you have a column of text strings which are composed of text and numbers, as the following screenshot shown, the numbers followed by the text within one cell, now, you would like to separate the text and numbers from one cell into two different cells as follows. In Excel, you can finish this task with following methods.

---

Split / Separate text strings into individual text and number columns: Kutools for Excel’s Split Cells feature is a powerful tool, it can help you to split cell values into multiple columns or rows, it can help you to slit alphanumeric strings into separated text and numbers columns, etc…

---

With the following formulas, you can extract the text and numbers from the cell into two separated cells. Please do as follows:

1. Enter this formula into a blank cell – C2 where you want to place the result: =LEFT(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1), (A2 is the cell which contains the text string you want to separate), and then press Enter key to get only the text from the cell A2. See screenshot:

2. And then you can extract the numbers from the cell by applying this formula: =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1),( A2 is the cell which contains the text string you want to separate), enter this formula into cell D2 which you want to place the numbers, and press Enter key, then you will get the numbers as this:

3. Then select cell C2:D2, and drag the fill handle over to the cells that you want to contain these formulas, and you can see the text and numbers have been separated into different cells: