Extract Numeric value from a string

[77winston]

Hi,
I hope someone can help me with excel issue i seem to be having.
I have the following data.
Column A is for ID and column B is for Notes.
I need find a formula that will extract the number that is highlighted in red and place it in column C.
would this be possible?
Please help.

ID INTERNAL NOTES 355104 Customers Grandson Shaun called in and had questions about the scheme. Verified personal details which identified him as a family member and explained the process. Also asked if he could aske someone to be in attendence on the day to assist with interpreting. He would try to assist. Entered by: Shalom Kaa Entered Date: 25/07/2013 9:42:34 AM ========================================================= Job confirmed to take place on 6/08 with son Andrew. Entered by: Patricia Mackin Entered Date: 24/07/2013 10:31:32 AM ========================================================= I rang only number listed, it rang out, I tried again and cust answered phone, in broken English she gave me her son Andrew's mobile number. Entered by: Patricia Mackin Entered Date: 24/07/2013 10:24:58 AM ========================================================= Called customer using TIS (job #130824435), customer advised she will get her grandson to call and interpret for her as she didn’t want to make the appointment until she spoke to him. Waiting for him to call back now. Entered by: Shalom Kaa Entered Date: 23/07/2013 2:45:16 PM ========================================================= The cust appeared to answer, I had expalined to the interpreter that the cust is a bit deaf as per our notes but cust hung up. Entered by: Patricia Mackin Entered Date: 19/07/2013 11:51:21 AM ========================================================= TIS job number is 130807425. Entered by: Patricia Mackin Entered Date: 19/07/2013 11:48:17 AM ========================================================= 355489 TIS job number is 130675537. Entered by: Patricia Mackin Entered Date: 19/06/2013 4:33:14 PM ========================================================= <colgroup><col><col></colgroup><tbody> </tbody>

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Hi..

Yeah.. some more (a lot more) info would be Gold..

In any case.. I'd love to learn Regex so here's a beginners try at it..

This just assumes there is always 9 digits in the value you want extracted.. if that's not the case.. the Pattern will need to be changed (that's the hard part.. for me anyway)..

Private Sub CommandButton1_Click()
    Dim objRegex, n
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .MultiLine = False
        .Global = True
        .IgnoreCase = False
        .Pattern = "\b\d{9}\b"


        For i = 2 To Range("B" & Rows.Count).End(xlUp).Row
            Set myMatches = .Execute(Cells(i, 2))
            For Each n In myMatches
                If Cells(i, 3).Value = "" Then
                    Cells(i, 3).Value = n
                Else
                    Cells(i, 3).Value = Cells(i, 3).Value & Chr(10) & n
                End If
            Next n
        Next i
    End With
End Sub

For those who might be interested, here is a non-RegExp macro to do the same thing....

Private Sub CommandButton1_Click()
  Dim X As Long, Z As Long, CellVal As String, Text As String
  For X = 2 To Cells(Rows.Count, "B").End(xlUp).Row
    Text = ""
    CellVal = Cells(X, "B").Value
    For Z = 1 To Len(CellVal) - 9
      If Mid(CellVal, Z, 9) Like "#########" Then Text = Text & vbLf & Mid(CellVal, Z, 9)
    Next
    Cells(X, "C").NumberFormat = "@"
    Cells(X, "C").Value = Mid(Text, 2)
  Next
End Sub

 

[cyrilbrd]

Give this array-entered** formula a try...

=TEXT(MAX(0+IF(ISNUMBER(0+MID(A1,ROW(INDIRECT("A1:A"&LEN(A1)-9)),9)),MID(A1,ROW(INDIRECT("A1:A"&LEN(A1)-9)),9),0)),"000000000")

** Commit this formula using CTRL+SHIFT+ENTER, not just Enter by itself

If we wanted the value to be a number, would that be alright? =TEXT(MAX(0+IF(ISNUMBER(0+MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-9)),9)),MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-9)),9),0)),"000000000")*1 CSE.

In the event of two values within one cell, how would you suggest to modify the formula?

Example :
TIS job number is 130675537. Entered by: Patricia Mackin Entered Date: 19/06/2013 4:33:14 PM 307255457=========================================================
Would return 130675537 and 307255457.

Thanks for your feedback.

 

[Rick Rothstein]

If we wanted the value to be a number, would that be alright? =TEXT(MAX(0+IF(ISNUMBER(0+MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-9)),9)),MID(B2,ROW(INDIRECT("A1:A"&LEN(B2)-9)),9),0)),"000000000")*1 CSE.

In the event of two values within one cell, how would you suggest to modify the formula?

Example :
TIS job number is 130675537. Entered by: Patricia Mackin Entered Date: 19/06/2013 4:33:14 PM 307255457=========================================================
Would return 130675537 and 307255457.

That formula cannot be modified to find multiple occurrences of 9-digit numbers and concatenate them together with a delimiter between them. Truthfully, I do not believe such a formulas can be devised without using multiple helper cells per row.

 

[cyrilbrd]

That formula cannot be modified to find multiple occurrences of 9-digit numbers and concatenate them together with a delimiter between them. Truthfully, I do not believe such a formulas can be devised without using multiple helper cells per row.

Noted and understood.
But what about having each number in a separate cell rather than concatenated in one cell with a delimiter?
Thanks for the feedback.

 

[Rick Rothstein]

But what about having each number in a separate cell rather than concatenated in one cell with a delimiter?

Array-enter** this formula in cell C2 and copy it across for as many columns as you think you might have separate job numbers for, then copy all those cells down to the end of your data...

=SUBSTITUTE(TEXT(LARGE(0+IF(ISNUMBER(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B2,"/","X"),".","X")," ","X"),ROW(INDIRECT("A1:A"&LEN($B2)-9)),9)),MID($B2,ROW(INDIRECT("A1:A"&LEN($B2)-9)),9)),COLUMN(A1)),"000000000"),"000000000","")

** Commit this formulas using CTRL+SHIFT+ENTER, not just Enter by itself

 

[cyrilbrd]

Array-enter** this formula in cell C2 and copy it across for as many columns as you think you might have separate job numbers for, then copy all those cells down to the end of your data...

=SUBSTITUTE(TEXT(LARGE(0+IF(ISNUMBER(0+MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($B2,"/","X"),".","X")," ","X"),ROW(INDIRECT("A1:A"&LEN($B2)-9)),9)),MID($B2,ROW(INDIRECT("A1:A"&LEN($B2)-9)),9)),COLUMN(A1)),"000000000"),"000000000","")

** Commit this formulas using CTRL+SHIFT+ENTER, not just Enter by itself

Thanks Rick, very nice...

 

[hiker95]

77winston, apo, Rick Rothstein, cyrilbrd,

More good stuff for my archives - thanks.

Have a great Holiday Season.

 

[77winston]

Hi Apo,

Thanks for the instructions on how to insert the code you provided. I followed it but the code doesn't seem to be execute once I hit run after pasting code in the VBA window.
I'm sorry to be a pain, but is there another step i need to do before i can execute the code? I'm using excel 2010 if that helps.
Please let me know.

 

[cyrilbrd]

Hi Apo,

Thanks for the instructions on how to insert the code you provided. I followed it but the code doesn't seem to be execute once I hit run after pasting code in the VBA window.
I'm sorry to be a pain, but is there another step i need to do before i can execute the code? I'm using excel 2010 if that helps.
Please let me know.

Hi, you might want to read those:
link1
link2

 

[apo]

Hi..

Take a look at the test Workbook ..
Regex - Extract multiple Numerical Values from String

Just right click on the Sheet1 Tab and select "View Code"..

There is a an ActiveX CommandButton on Sheet1. In the VB Editor.. you will see the code for the CommandButton in the sheet1 code section..