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, now, you would like to separate the text and numbers from one cell into two different cells as following screenshot shown. In Excel, you can finish this task with these methods.
Method 1: Separate text and numbers with formulas in Excel
Method 2: Separate text and numbers with Flash Fill in Excel 2013 and later version
Method 3: Separate text and numbers which are mixed irregular with User Defined Function
Method 4: Separate text and numbers into two columns with Kutools for Excel
Method 1: 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 – C3 where you want to place the result: =LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))-1), (A3 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(A3,LEN(A3)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&"0123456789"))+1),( A3 is the cell which contains the text string you want to separate), enter this formula into cell D3 which you want to place the numbers, and press Enter key, then you will get the numbers as this:
3. Then select cell C3:D3, 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:
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 also can help you to slit alphanumeric strings into separated text and numbers columns, etc… Click to download Kutools for Excel!
Method 2: Separate text and numbers with Flash Fill in Excel 2013 and later version
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 and later version.
If you have Excel 2013 and later version, 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 - B3, see screenshot:
2. And then select the range B3:B7 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:
3. Then enter the text string completely into cell C3, see screenshot:
4. And select the cell range C3:C7 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:
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.
Method 3: 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.
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
'Updateby Extendoffice
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(A3,FALSE) into a blank cell to get the only the text string and then drag the fill handle down to the cells that you want to fill this formula, see screenshot:
4. And thne, type formula =SplitText(A3,TRUE) into another cell and drag the fill handle down to the cells that you want to fill this formula to get the numbers, see screenshot:
Note: The result will be incorrect if there are decimal numbers in the text string.
Method 4: 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.
After installing Kutools for Excel, please do as follows:
1. Select the data range that you want to separate the text and number.
2. Then click Kutools > Merge & Split > Split Cells, see screenshot:
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:
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:
5. Click OK button, and the text strings in the selection have been separated into two columns as following screenshot shown:
Click Download Kutools for Excel and free trial Now!
Separate text and numbers with Kutools for Excel
Related articles:
How to split word into separate letters in Excel?
How to split cells into multiple columns or rows by carriage return?
-
Kutools for Excel: 300+ handy features for Excel of free trial
Collecting more than 300 smart and professional tools for Excel, it simplifies various kinds of complicated tasks into a few clicks in your daily work, which can save much time and increase productivity.
- Combine multiple worksheet or workbooks into single workbook or worksheet
- Count and sum cells based on background, font or conditional formatting color
- Send the same email to a list of recipients individually with different attachments
- 110,000+ Excel users' choice. Full feature free trial 30-day, no credit card required!
- 60-day money back guarantee!
-
Combine multiple worksheets or workbooks into one single sheet or workbook
In your daily work, merging multiple worksheets or workbooks into one sheet or workbook may be a huge job for you to combine them one by one. But, with Kutools for Excel's Combine feature, you can solve this task with only several clicks.
-
Count and sum cells based on background, font or conditional formatting color
It may be a difficult task for you to count or sum the cell values based on background, font or conditional formatting color in a large range, Kutools for Excel supports an easy tool-Count by Color which can help you to deal with this job as quickly as possible without any Excel skills.
-
Send the same email to a list of recipients individually with different attachments
You can apply the Word application to send personalized emails to multiple persons without attachments, but, With Kutools for Excel's Send Emails feature, this limatation can be remedied. At the same time, you can CC or Bcc the messages to a specific person as well.
