There is often a need to extract numbers from the start or end of text strings. It’s not too hard to do with on-sheet formulas, but after having done it a few thousand times I decided it would be worth spending 5 minutes writing two VBA User Defined Functions (UDFs) to do the job. Here they are:
NumRight() will extract a number from the right hand end of a text string, with the number delimited by a space by default, or optionally any other character:
Function NumRight(NumStrings As Variant, Optional Delim As String = " ") As Variant
Dim i As Long, numrows As Long, OutA() As Variant, j As Long, NumDig As Long, Numstring As String, StringLen As Long
If TypeName(NumStrings) = "Range" Then NumStrings = NumStrings.Value2
If TypeName(NumStrings) = "String" Then
numrows = 1
Else
numrows = UBound(NumStrings)
End If
ReDim OutA(1 To numrows, 1 To 1)
On Error Resume Next
For i = 1 To numrows
If IsArray(NumStrings) = True Then
Numstring = NumStrings(i, 1)
Else
Numstring = NumStrings
End If
StringLen = Len(Numstring)
j = 1
Do While Left(Right(Numstring, j), 1) <> Delim
If j >= StringLen Then Exit Do
j = j + 1
Loop
If j >= StringLen Then
OutA(i, 1) = ""
Else
OutA(i, 1) = CDbl(Right(Numstring, j - 1))
End If
Next i
NumRight = OutA
End Function
Numleft() is similar, but extracts a number from the left hand end.
Function NumLeft(NumStrings As Variant, Optional Delim As String = " ") As Variant
Dim i As Long, numrows As Long, OutA() As Variant, j As Long, NumDig As Long, Numstring As String, StringLen As Long
If TypeName(NumStrings) = "Range" Then NumStrings = NumStrings.Value2
If TypeName(NumStrings) = "String" Then
numrows = 1
Else
numrows = UBound(NumStrings)
End If
ReDim OutA(1 To numrows, 1 To 1)
On Error Resume Next
For i = 1 To numrows
If IsArray(NumStrings) = True Then
Numstring = NumStrings(i, 1)
Else
Numstring = NumStrings
End If
StringLen = Len(Numstring)
j = 1
Do While Right(Left(Numstring, j), 1) <> Delim
If j >= StringLen Then Exit Do
j = j + 1
Loop
If j >= StringLen Then
OutA(i, 1) = ""
Else
OutA(i, 1) = CDbl(Left(Numstring, j - 1))
End If
Next i
NumLeft = OutA
End Function
For both functions the input “NumStrings” may be either a single cell or a single column range. In the latter case the function must be entered as an array function to return all the results:
- Enter the function, with the input range being a single column range
- Select the range of output rows, with the function in the top row
- Press F2 (Edit)
- Press Ctrl-Shift-Enter
The functions may be found in GetNum.xlsb, and have also been added to Text-in2.xlsb.
Here’s what they look like in use:
NumLeft and NumRight functions
Newton Excel Bach, not (just) an Excel Blog 
Thought you might be interested in another way to tackle this problem. I usually initially “import” the data to the worksheet using Data – From text, Then choose the (csv or txt) file with the text and the “import wizard” opens up and allows you to choose either fixed length or delimited format. In your case, delimited files with the appropriate delimiter (” “, or “,”) Next the wizard shows the columns sensed and allows you to choose the format (number in this case) and It also allows you to select how far down the list to start looking for data. In this way you get all the data in separate columns (or you can lump the ones you don’t want) and in the correct format (numbers as numbers and not as characters). This works great for large amounts of data, especially if other columns from the data are needed
Lots of ways to accomplish things in excel…
.
LikeLike
I agree with the Lots of ways to accomplish things in Excel part of the comment. Another, quicker way of doing this would be to use the text-to-columns functionality in Excel with the Delimiter option.
.
http://office.microsoft.com/en-in/excel-help/split-names-by-using-the-convert-text-to-columns-wizard-HA010102340.aspx
However, my man here seems to be going for a LIVE version using User Defined Functions. Macros are a pain, you’d end up having to run it over an over again even if a single item changes in your data set. User Defined Functions are way more practical in this scenario.
LikeLike
Another problem with the Import/Text to column method is, it will fail if you want to extract number from the right. Considering the fact that there is no gaurentee that the “desired” split will end up in a single column.
LikeLike
Reblogged this on Sutoprise Avenue, A SutoCom Source.
LikeLike
Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog
Hey Buddy, I thought you might be interested in looking at the InStr() and InStrRev() functions. I would suggest converting the values in the text using CStr() and then using the above functions to extract the “numbers”, and finally converting them back to numbers using CDbl().
Alternatively you can leave it as a string in your function and use the Value() function externally to make it a number. This way you can use your function to extract text also: you can get the File name\last folder by using “\” as your delimiter. File extensions using “.” as your delimiter.
Finally, you could look into Regular Expressions to do WAaaaaY MORE COOLER STUFF! I am just saying that because I just got wind of it and wrote a post about it.: http://strugglingtoexcel.wordpress.com/2013/12/08/excel-worksheet-functions-and-vba-functions/
Check this out if you like a more versatile, but possibly slower, Implimentation using the Split() function.
http://strugglingtoexcel.wordpress.com/2013/11/14/extract-nth-word-excel/
LikeLike
I like what you have done with the array formula implementation. However, I am not sure why you took the trouble of actually doing it. I am not a big fan of the Array Formula setup! I prefer the function to pick up the corresponding value automatically.
If you are using a Range (more than one cell) and your formula fails if you declared your NumStrings As String, there is another quick solution.
You can tweak your formula (in the cell) to
=NumRight(A17:122 & “”,” “)
Add in the & “”
Excel automatically picks up the value from the corresponding row. This is how I usually do it if I need to write a quick function. This is similar to adding the “+0” to named columns in the MAX, MIN, SUM, PRODUCT functions.
Another concept would be to use the Intersection() function and parent object to find the entry from the corresponding row and use it in your function if the user selected a Range (with more than one cell). Consequently ditching the array formula set up. I have been meaning to try and see if this works, never actually got around to doing it.
LikeLike
Hi Ejaz, thanks for the comment and links.
You might like to have a look at: https://newtonexcelbach.wordpress.com/2012/10/20/daily-download-32-text-functions/ which has several other functions for importing and processing text strings.
The point of the functions in this post was to have a simple function for the specific task of extracting numbers from the start or end of the string.
The advantages of writing a UDF as an array function are that they are much faster in many cases, and also that once entered it is quicker to modify the formula and have it apply to the whole range, but if you don’t want to use these functions on a range they work equally well on a single cell.
LikeLike
I see now that an Array formula computes the entire range at one shot on our terms rather than relying on Excel’s recalculation process. Perhaps that is why they are faster. Thanks for that tip. I would also write all my UDFs as array functions hence forth.
LikeLike
Reblogged this on Struggling To Excel and commented:
A neat example for creating Array Functions in Excel VBA
LikeLike
Pingback: dataprose.org » Scrub Your Data Expressively
Pingback: Extracting numbers with regular expressions | Newton Excel Bach, not (just) an Excel Blog
Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog