Many times I get mixed data from field and server for analysis. This data is usually dirty, having column mixed with number ans text. While doing data cleaning before analysis, I separate numbers and text in separate columns. In this article, I’ll tell you how you can do it.
Scenario:
So one our friend on Exceltip.com asked this question in the comments section. “How do I separate numbers coming before a text and in the end of text using excel Formula. For example 125EvenueStreet and LoveYou3000 etc.”
To extracting text, we use RIGHT, LEFT, MID and other text functions. We just need to know the number of texts to extract. And Here we will do the same first.
Extract Number and Text from a String when Number is in End of String
For above example I have prepared this sheet. In Cell A2, I have the string. In cell B2, I want the text part and in C2 the Number Part.
So we just need to know the position from where number starts. Then we will use Left and other function. So to get position of first number we use below generic formula:
Generic Formula to Get Position of First Number in String:
This will return the position of first number.
For above example write this formula in any cell.
Extract Text Part
It will return 15 as the first number which is found is at 15th position in Text. I will explain it later.
Now, to get Text, from left we just need to get 15-1 character from string. So we will use
LEFT function to extract text.
Formula to Extract Text from Left
Here we just subtracted 1 from what ever number returned by MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")).
Extract Number Part
Now to get numbers we just need to get number characters from 1st number found. So we calculate the total length of string and subtract the position of first number found and add 1 to it. Simple. Yeah it’s just sound complex, it is simple.
Formula to Extract Numbers from Right
Here we just got total length of string using LEN function and then subtracted the position of first found number and then added 1 to it. This gives us total number of numbers. Learn more here about extracting text using LEFT and RIGHT functions of Excel.
So the LEFT and RIGHT function part is simple. The Tricky part is MIN and SEARCH Part that gives us the position of first found number. Let’s understand that.
How It Works
We know how LEFT and RIGHT function work. We will explore the main part of this formula that gets the position of first number found and that is: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789")
The SEARCH function returns the position of a text in string. SEARCH(‘text’,’string’) function takes two argument, first the text you want to search, second the string in which you want to search.
Let’s examin our example. In A5 we have the string that has street name and house number. We need to separate them in different cells.
First let’s see how we got our position of first number in string.
Now, as I explained search will search each number in array {0,1,2,3,4,5,6,7,8,9} in Monta270123456789 and will return its position in an array form. The returned array will be {8,9,6,11,12,13,14,7,16,17}. How?
0 will be searched in string. It is found at 8 position. Hence our first element is 8. Note that our original text is only 7 characters long. Get it. 0 is not a part of Monta27.
Next 1 will be searched in string and it is also not part of original string, and we get it’s position 9.
Next 2 will be searched. Since it is the part of original string, we get its index as 6.
Similarly each element is found at some position.
Extract Number and Text from a String When Number is in Beginning of String
In Above example, Number was in the end of the string. How do we extract number and text when number is in the beginning.
I have prepared a similar table as above. It just has number in the beginning.
Here we will use a different technique. We will count the length of numbers (which is 2 here) and will extract that number of characters from left of String.
So the method is =LEFT (string, count of numbers)
To Count the Number of characters this is the Formula.
Generic Formula to Count Number of Numbers:
Here,
Extract Number Part from String
Now since we know the length of numbers in string, we will substitute this function in LEFT.
Since We have our string an A11 our:
Formula to Extract Numbers from LEFT
Extract Text Part from String
Since we know number of numbers, we can subtract it from total length of string to get number alphabets in string and then use right function to extract that number of characters from right of the string.
Formula to Extract Text from RIGHT
How it Works
The main part in both formula is SUM(LEN(A11)-LEN(SUBSTITUTE(A11,{"0","1","2","3","4","5","6","7","8","9"},""))) that calculates the first occurance of a number. Only after finding this, we are able to split text and number using LEFT function. So let’s understand this.
Now using this we can extract the texts and number and split them in different cells. This method will work with both type text, when number is in beginning and when its in end. You just need to utilize LEFT and RIGHT Function appropriately.
Use SplitNumText function To Split Numbers and Texts from A String
The above to methods are little bit complex and they are not useful when text and numbers are mixed. To split text and numbers use this user defined function.
Syntax:
String: The String You want to split.
Op: this is boolean. Pass 0 or false to get text part. For number part, pass true or any number greater then 0.
For example, if the string is in A20 then,
Formula for extracting numbers from string is:
And
Formula for extracting text from string is:
Copy below code in VBA module to make above formula work.
Function SplitNumText(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 SplitNumText = num Else SplitNumText = txt End If End Function
This code simply checks each character in string, if its a number or not. If it is a number then it is stored in num variable else in txt variable. If user passes true for op then num is returned else txt is returned.
This is the best way to split number and text from a string in my opinion.
You can download the workbook here if you want.
So yeah guys, these are the ways to split text and numbers in different cells. Let me know if you have any doubts or any better solution in the comments section below. Its always fun to interact with guys.
Click the below link to download the working file:
Related Articles:
Extract Text From A String In Excel Using Excel’s LEFT And RIGHT Function
How To Extract Domain Name from EMail in Excel
Split Numbers and Text from String in Excel
Popular Articles:
50 Excel Shortcut’s to Increase Your Productivity
The VLOOKUP Function in Excel
COUNTIF in Excel 2016
How to Use SUMIF Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.