Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Extract 8 to 10 digit numbers

0

Is there a formula that will extract  only 8 to 10 digits in a cell?

I have a scenario with thousands of cells in a column that is a summary of words but I need to pull out just the policy numbers that can range from 8-10 digit numbers. 

Thanks!

Answer
Discuss

Discussion

There are a LOT of different ways to do this depending on the setup of your data. Edit your question and include some sample rows of data and it will be easier to give you a helpful solution.
don (rep: 1989) Jan 4, '19 at 5:39 pm
I have added the file. 

Column K is where I need the data extracted that matched 8-10 number characters. K15 is an example where I need " AB03072970" extracted into the corresponding S column S15. Cell A22 is another example and I picked this one because it may or may not have the letters attached as well. I'm more worried about just the numbers that match 8-10 digits. 
Sroncey21 (rep: 66) Jan 4, '19 at 6:57 pm
Add to Discussion

Answers

0

The code below will extract a number like you describe - 8 to 10 digits - but only the first such number encountered. Install it in a standard code module.

Function ExtractNumber(Txt As String) As String
    ' 15 Jan 2019

    Dim Fun As String
    Dim Ch As String, Ln As Integer
    Dim n As Integer

    For n = 1 To Len(Txt)
        Ch = Mid(Txt, n, 1)
        If IsNumeric(Ch) Then
            Fun = Fun & Ch
        Else
            Ln = Len(Fun)
            If (Ln >= 8) And (Ln <= 10) Then Exit For
            Fun = ""
        End If
    Next n
    
    Ln = Len(Fun)
    If (Ln >= 8) And (Ln <= 10) Then ExtractNumber = Fun
End Function

My first idea was to let it work as a UDF. So, I programmed a UDF procedure to call it.

Function PolNum(Cell As Range) As String
    ' 05 Jan 2019
    PolNum = ExtractNumber(CStr(Cell.Value))
End Function

Install this function in the same standard code module as the one that does the work. Enter =PolNum($K2) in cell S2 and copy down. Make sure that S2 is formatted as Text. Otherwise you will lose leading zeroes.

Then I thought that this UDF will slow down your sheet quite a bit. Also, I don't much like UDFs because they have a tendency not to update just whenever you have started to trust that they do. The alternative is an event procedure which fires when a change is made in column K. Install the code in the code module of the worksheet on which you want the action.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 05 Jan 2019

    Const FirstDataRow As Long = 2
    Const TargetClm As String = "K"
    Const ResultClm As String = "T"

    Dim Rng As Range

    With Target
        On Error Resume Next
        If .Cells.Count = 1 Then                ' ignore multiple cell changes
            If Err Then Exit Sub

            On Error GoTo 0
            Set Rng = Range(Cells(FirstDataRow, TargetClm), _
                            Cells(Rows.Count, TargetClm).End(xlUp))
            If Not Application.Intersect(Target, Rng) Is Nothing Then
                Application.EnableEvents = False
                Cells(.Row, ResultClm).Value = ExtractNumber(CStr(.Value))
                Application.EnableEvents = True
            End If
        End If
    End With
End Sub

Set the 3 constants at the top of the code to the values that match your worksheet, remember to format the target column as Text, and observe that the procedure calls the same ExtractNumber function first above mentioned. So, in theory, you could have both the UDF and the event procedure in your workbook, although it's easier to imagine the harm that might do than the good.

Unless you wish to avoid changing several thousand cells in order to trigger several thousand events. I would use a loop to call ExtractNumber for all rows before first use but an alternative would be to install the UDF calls and copy/paste values.

Edit Jan 15, 2019   ================================

I now attach a workbook containing the above code. In the process of preparing it I found that the function ExtractNumber failed to return numbers found at the end of the string. This was corrected and the revised function published above.

Discuss

Discussion

I've attached myworkbook with your code above in the (1) file.

I entered the code and changed column K to text. I have not gotten to loops yet and when I tried to enter the UIDF you had above to extract the polnumber it had errored and said "#NoName" I'm still learning with VBA.
Sroncey21 (rep: 66) Jan 7, '19 at 8:59 am
#NoName indicates that the UDF isn't available. The function PolNum must be installed in a standard code module (like Module1) and the call must be like =PolNum($K2). The workbook you posted is of xlsx type and, therefore, contains no code. Make sure you save your workbook as xlsm type.
Variatus (rep: 4889) Jan 7, '19 at 7:22 pm
I have the VBA code in module 15. I saved the file as .xlsm - Entered the same formula. Still getting the #Name? error. (I've attached my updated workbook)
Sroncey21 (rep: 66) Jan 8, '19 at 9:35 am
Please take a look at the attached workbooks. They are of xlsx format. They don't contain macros.
Variatus (rep: 4889) Jan 8, '19 at 7:12 pm
Updated with macro enabled worksheet and formula.
Sroncey21 (rep: 66) Jan 9, '19 at 10:49 am
There is no code in your workbook. Open the VB Editor (Alt+F11), right-click on the Project in the Project Explorer panel on the left, select Insert > Module to create a standard code module. Some of the code goes there, the rest in the worksheet's code module. The UDF code needs to be in the standard module, the event procedure in the sheet's module.
Variatus (rep: 4889) Jan 9, '19 at 7:25 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login