Cookies help us deliver our services. By using our services, you agree to our use of cookies.
Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.

doc-separate-text-number-1

Separate text and numbers with formulas in Excel

Separate text and numbers with Flash Fill in Excel 2013

Separate text and numbers which are mixed irregular with User Defined Function

Separate text and numbers into two columns with Kutools for Excel


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…

doc-separate-text-number-18-18

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Separate text and numbers with formulas in Excel

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:

doc-separate-text-number-2

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:

doc-separate-text-number-3

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:

doc-separate-text-number-4


arrow blue right bubble Separate text and numbers with Flash Fill in Excel 2013

With the above formulas, you just can separate the text and numbers if the text is before the numbers. To separate the text string which number is before the text, you can use the Flash Fill feature of Excel 2013.

If you have Excel 2013, the Flash Fill feature may help you to fill the text in one column and numbers in another column, please do as this:

1. Type the numbers of your first text string completely into adjacent blank cell - B2, see screenshot:

doc-separate-text-number-5

2. And then select the range B2:B6 where you want to fill the numbers, and click DATA > Flash Fill, and only the numbers have been filled in the cells at once, see screenshot:

doc-separate-text-number-6

3. Then enter the text string completely into cell C2, see screenshot:

doc-separate-text-number-7

4. And select the cell range C2:C6 where you want to fill the text only, click DATA > Flash Fill as well as step 2, and you can see, the text has been separated as follows:

doc-separate-text-number-8

Tip: You also can drag the filled handle to the range you want to use, and then click Auto Fill Option and check Flash Fill.

doc-separate-text-number-13


arrow blue right bubble Separate text and numbers which are mixed irregular with User Defined Function

If you have some text string which are mixed text and numbers irregularly like following data shown, Excel does not support the general feature to solve this problem, but, you can create User Defined Function to finish this.

doc-separate-text-number-9

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: separate text and numbers into different cells from one cell

Public Function SplitText(pWorkRng As Range, pIsNumber As Boolean) As String
'Updateby20150306
Dim xLen As Long
Dim xStr As String
xLen = VBA.Len(pWorkRng.Value)
For i = 1 To xLen
    xStr = VBA.Mid(pWorkRng.Value, i, 1)
    If ((VBA.IsNumeric(xStr) And pIsNumber) Or (Not (VBA.IsNumeric(xStr)) And Not (pIsNumber))) Then
        SplitText = SplitText + xStr
    End If
Next
End Function

3. Then save and close this code, go back to the worksheet, enter this formula =SplitText(A2,FALSE) into a blank cell to get the only the text string and press Enter key, and type formula =SplitText(A2,TRUE) into another cell to get the numbers and press Enter key, see screenshots:

doc-separate-text-number-10
-1
doc-separate-text-number-11

4. And then select the cell B2:C2, and drag the fill handle over to the range that you want to apply these formulas, you will get the following result:

doc-separate-text-number-12

Tip: The result will be incorrect if there are decimal numbers in the text string.


arrow blue right bubble Separate text and numbers into two columns with Kutools for Excel

If you have Kutools for Excel, with its powerful tool- Split Cells utility, you can quickly split the text strings into two columns: one is number, and another is text.

Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 60 days

After installing Kutools for Excel, please do as following:

1. Select the data range that you want to separate the text and number.

2. Then click Kutools > Text > Split Cells, see screenshot:

doc-separate-text-number-14

3. In the Split Cells dialog box, select Split to Columns option under the Type section, and then check Text and number from the Split by section, see screenshot:

doc-separate-text-number-15

4. Then click Ok button, and a prompt box will pop out to remind you to select a cell to output the result, see screenshot:

doc-separate-text-number-16

5. Click OK button, and the text strings in the selection have been separated into two columns as following screenshot shown:

doc-separate-text-number-17


arrow blue right bubble Separate text and numbers with Kutools for Excel

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


Related articles:

How to split word into separate letters in Excel?

How to split cells into multiple columns or rows by carriage return?



Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    AM · 1 months ago
    Thanks worked for Alpha-numeric cell data [ =RIGHT(A2,LEN(A2)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))+1)]
  • To post as a guest, your comment is unpublished.
    Gowtam Roopun · 2 months ago
    Hello Can anyone help me? I want to remove this zero and braceket by an excel formula.
    1 BR PE-0.50CT(H SI2)- -0.00( )-2 SP PE-0.50CT(H SI1)-1 RU PE-0.40CT(H-SI)-750GF-RG-RING-25-40-2.50GM


    Gowtam
  • To post as a guest, your comment is unpublished.
    RAJ · 2 months ago
    VERY USEFUL FORMULA I LIKE IT.
  • To post as a guest, your comment is unpublished.
    Sarashwaty · 7 months ago
    Hi.... Thank you for sharing, it is very helpful and save lots of time for me.
    Regards,
    Saras.
  • To post as a guest, your comment is unpublished.
    mahsa s · 8 months ago
    Hi,
    I'm trying to split these values HarryJack 22 3,66 335,77 44,77 into two columns which I can only have text in one column and 22 in the second column.