Method 1 – Using Combined Excel Formula
1.1 Separate Numbers After Text
Steps:
- At the very beginning, go to the C5 cell >> enter the formula given below.
=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)
The B5 cell refers to the text Microsoft Office 95.
Formula Breakdown:
- FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″) → returns the starting position of one text string within another text string. Here, {0,1,2,3,4,5,6,7,8,9} is the find_text argument while B5&”0123456789″ is the within_text argument. The FIND function returns the position of the numeric values in the string of text.
- MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″)) → returns the smallest number in a set of values. The FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″) is the number_1 argument and the MIN function returns the smallest value within this array.
- Output → 18
- LEN(B5) → returns the number of characters in a string of text. The B5 cell is the text argument which yields the value 19.
- Output → 19
- LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1 → becomes
- 19 – 18 + 1 → 2
- RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&”0123456789″))+1) → becomes
- RIGHT(B5,2)  → returns the specified number of characters from the end of a string. The B5 cell is the text argument 2 is the num_chars argument that the function returns the 2 characters from the right side.
-  Output → 95
- Use the Fill Handle Tool to copy the formula into the cells below.
The results should look like the image given below.
1.2 Separate Numbers Preceding Text
Steps:
- Move to the C5 cell >> type in the expression given below.
=LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{"0","1","2","3","4","5","6","7","8","9"},""))))
The B5 cell indicates the text 95 Microsoft Office.
Formula Breakdown:
- SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””) → replaces existing text with new text in a text string. Here, the B5 refers to the text argument while Next, the {“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9”} represents the old_text argument, and the “” points to the new_text argument which is left blank.
- LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)) → returns the number of characters in the string of text. The output from the SUBSTITUTE function is the text argument.
- SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””))) →  adds all the numbers in a range of cells. The SUM function returns the total of the numeric values.
- Output → 2
- LEFT(B5,SUM(LEN(B5)-LEN(SUBSTITUTE(B5,{“0″,”1″,”2″,”3″,”4″,”5″,”6″,”7″,”8″,”9″},””)))) → becomes
- LEFT(B5,2)  → returns the specified number of characters from the start of a string. The B5 cell is the text argument 2 is the num_chars argument that the function returns the 2 characters from the left side.
-  Output → 95
Your output should look like the picture given below.
1.3 Separate Decimal Numbers
Steps:
- The first place, insert the following equation into the C5 cell.
=RIGHT(B5,LEN(B5)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789"))+1)
The B5 cell represents the text value of Microsoft Office 95.1.
After completing the above step, the output should look like the screenshot below.
Method 2 – Utilizing Flash Fill Feature
Steps:
- The first place, manually type in the first ID number which is 1214 in the C5 cell so that Excel can recognize a pattern.
- Navigate to the C6 and enter the first two digits of 1215 and Excel will show a preview of the autofill results >> hit the ENTER key.
The results should look like the image given below.
Method 3 – Employing Text to Columns Feature
3.1 Applying Delimiters Option
Steps:
- Select the B5:B13 cells >> proceed to the Data tab >> click the Text to Columns option.
The B5:B13 cells point to the ID and Names columns.
This opens the Convert Text to Columns wizard.
- Choose the Delimited option >> hit the Next button.
- Insert a check mark for the Space delimiter >> press the Next button.
- Enter a Destination cell according to your preference, it is the C5 cell >> click Finish.
The results should appear in the picture shown below.
3.2 Utilizing Fixed Width Feature
Steps:
- Select the C5:C14 cells >> jump to the Data tab >> press the Text to Columns option.
The C5:C14 cells point to the Employee ID column.
The Convert Text to Columns wizard pops out.
- Select the Fixed Width option >> press the Next button.
- Left click to insert column breaks after the Code and ID >> click the Next button.
- Select the Destination cell, for example, the D5 cell >> hit the Finish button.
The end result should look like the screenshot provided below.
Method 4 –Â Using Power Query Editor
Steps:
- Select the B4:B13 cells >> hit the keyboard shortcut CTRL + T to insert an Excel Table >> press OK.
- Go to the B4 cell >> in the Data tab, click the From Table/Range option.
The Power Query Editor window appears.
- Move to the Transform tab >> press the Split Column drop-down >> choose the By Non-Digit to Digit option.
- Double-click the column headers to rename them >> press the Close & Load option to exit the Power Query window.
The output should look like the image depicted below.
Method 5 –Â Applying VBA Code
Steps:
- Navigate to the Developer tab >> click the Visual Basic button.
This opens the Visual Basic Editor in a new window.
- Go to the Insert tab >> select Module.
You can copy the code from here and paste it into the window as shown below.
Function SplitNumAndText(str As String, op As Boolean)
num = ""
txt = ""
For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then
num = num & Mid(str, i, 1)
Else
txt = txt & Mid(str, i, 1)
End If
Next i
If op = True Then
SplitNumAndText = num
Else
SplitNumAndText = txt
End If
End Function
âš¡ Code Breakdown:
The VBA code used to separate numbers. In this case, the code is divided into 2 steps.
- Declare the function SplitNumAndText where the first argument is a string and the second argument a Boolean type.
- Iterate through the whole string using For loop and If statement to check if the value is a number or not.
- In the later portion, use a second If statement to return output (Number or Text) based on the second argument. Passing 0 as the second argument will extract the Text while passing 1 returns the Number from the given string.
- Close the VBA window >> navigate to the C5 cell >> insert the function below.
=SplitNumAndText(B5,1)
The B5 cell refers to the ID and Names value of 1214 John Connor.
Yield the results shown in the screenshot below.
How to Split Numbers Using Formula in Excel
Steps:
- Jump to the D5 cell >> insert the expression into the Formula Bar.
=MID($C5,COLUMN()-(COLUMN($D5)- 1),1)
The C5 cell indicate the ID number 1214.
Formula Breakdown:
- COLUMN()-(COLUMN($D5)- 1) → returns the column number of a cell reference.
- 4 – (4-1) → 1
- MID($C5,COLUMN()-(COLUMN($D5)- 1),1) → becomes
- MID($C5,1,1)  → returns the characters from the middle of a text string, given the starting position and length. The C5 cell is the text argument, 1 is the start_num argument, and 1 is the num_chars argument, so the function returns the first character from the left side.
-  Output → 1
- Drag the Fill Handle tool across the rows to insert the remaining numbers.
- Select the D5:G5 cells and again use the Fill Handle tool to complete the table.
The final output should look like the following screenshot.
How to Split Numbers into Two Columns in Excel
Steps:
- Select the C5:C13 cells >> navigate to the Data tab >> click the Text to Columns option.
- Select the Fixed Width option >> press the Next button.
- Left click to insert column breaks after the Birth Year >> click the Next button.
- Select the Destination cell, like the D5 cell >> hit the Finish button.
Your results should look like the image shown below.
<< Go Back to Separate Numbers Text | Split | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
Hi,
Good day!
I tried to use column to text method to separate a range of numbers in a cell(A1) and it works well on B1,C1,D1… But if I change the range of numbers in A1 again, the rest of the column does not change. May I know why? How can I have the rest of the column refresh with new numbers when I change the range of numbers in A1
Hello Edmund Goh,
Thank you for sharing your problem with us. I assume you have properly gone through the steps to separate numbers with the Text to Columns feature. The reason that any changes are not updating is because the Text to Columns feature is static and it will not take any changes from the source cell. Yes, this is a limitation of this feature. You will also face the same if you apply the Flash Fill feature. In this case, I will suggest you to apply any other methods from this article except Method 4 and 5. Then you will be able to get changes on the separated numbers from the text.
Let us know if it helps you.
Thank you.
Regards,
Guria
ExcelDemy.