Extracting a 7 number string from text

walkinginheels24

New Member
Joined
Jun 28, 2019
Messages
3
Hi,

I would like to find a macro that will extract a string a set of seven numbers from a string of text e.g.

WELDING COPPER WIRE PO: 6822835 5.0KG 0.8MM


Ideally, the macro will extract 6722835. However, the set of 7 numbers will not be in the same place in each string of text, nor will it be followed or preceded by the same qualitative data.

Thank you in advance.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Here's a UDF you can try. Use it like a worksheet function as in the example below.
Excel Workbook
AB
1WELDING COPPER WIRE PO: 6822835 5.0KG 0.8MM6822835
Sheet3


Code:
Function SevenDigitNumber(S As String) As Double
Dim V As Variant
V = Split(S, " ")
For i = LBound(V) To UBound(V)
    If Val(V(i)) > 0 And Len(V(i)) = 7 Then
        SevenDigitNumber = Val(V(i))
        Exit Function
    End If
Next i
SevenDigitNumber = CVErr(xlErrValue)
End Function
To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
 
Upvote 0
Another UDF option.

Code:
Function SEVEN(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "\d{7}"
    SEVEN = .Execute(s)(0)
End With
End Function
 
Upvote 0
Both of the above posted codes are not entirely robust. Joe's code will report incorrect values for things like 12.34e9, 12/3/45 and even &heeded... lrobbo314's code accepts any number with 7 or more digits, for example, 123456789.

It is always possible that I am wrong, but I believe this function to be fully robust...
Code:
Function GetSevenDigits(S As String) As Variant
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, 9) Like "[!0-9]#######[!0-9]" Then
      GetSevenDigits = Mid(S, X, 7)
      Exit Function
    End If
  Next
  GetSevenDigits = ""
End Function
 
Upvote 0
This formula does not work well:
Code:
=LOOKUP(10^8,MID(A1,ROW(INDIRECT("1:"&LEN(A1)-6)),7)
/ISERROR(--MID("x"&A1,ROW(INDIRECT("1:"&LEN(A1)-6)),1))
/ISERROR(--MID(A1&"x",ROW(INDIRECT("8:"&LEN(A1))),1)))
 
Last edited:
Upvote 0
Here's a formula that seems to work pretty well:

=MID(A1,MATCH(7,MMULT(--(ISNUMBER(MID(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),7),{1,2,3,4,5,6,7},1)+0)),{1;1;1;1;1;1;1}),0),7)
confirmed with Control+Shift+Enter.

About the only drawback I see with it is if there is an 8- (or more) digit number before the 7-digit number you want, it will pick up the first 7 digits of that number.
 
Upvote 0
Another UDF (variation of post 4)
Code:
Function GetNum(S As String, Optional lngth As Long = 7) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(\D|^)(\d{" & lngth & "})(\D|$)"
    If .Test(S) Then GetNum = .Execute(S)(0).SubMatches(1)
  End With
End Function

This one also has the option to search for a different number of consecutive digits by specifying a second argument, as shown in C1

Excel Workbook
ABC
1WELDING COPPER WIRE PO: 6822835 5.0KG 0.8MM 650M6822835650
Sheet2
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,207
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top