Extracting numbers from text strings

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

NumLeft and NumRight functions

Advertisements
This entry was posted in Excel, UDFs, VBA and tagged , , , , , , . Bookmark the permalink.

13 Responses to Extracting numbers from text strings

  1. T Dziubakowski says:

    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…

    .

    Like

  2. Pingback: Daily Download 32: Text functions | Newton Excel Bach, not (just) an Excel Blog

  3. Ejaz Ahmed says:

    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/

    Like

  4. Ejaz Ahmed says:

    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.

    Like

  5. dougaj4 says:

    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.

    Like

    • Ejaz Ahmed says:

      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.

      Like

  6. Ejaz Ahmed says:

    Reblogged this on Struggling To Excel and commented:
    A neat example for creating Array Functions in Excel VBA

    Like

  7. Pingback: dataprose.org » Scrub Your Data Expressively

  8. Pingback: Extracting numbers with regular expressions | Newton Excel Bach, not (just) an Excel Blog

  9. Pingback: Importing Text Files; Unix Format | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s