Extract Numeric value from a string

77winston

New Member
Joined
Sep 11, 2013
Messages
31
Office Version
  1. 2013
Platform
  1. Windows
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.:confused:
IDINTERNAL NOTES
355104Customers 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 =========================================================
355489TIS 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>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Hi, just curious:
Are those in one cell or several cells?
Is the length of the string to be extracted fixed or variable?
Are there other values that should be excluded that may look similar to the string of interest?
Would you prefer VBA or formula.

This may help members giving you feedback.
 
Upvote 0
Hi cyrilbrd, thumbs up for your questions as it really helps clarify things before spend time drilling down...
 
Upvote 0
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).. :)

Code:
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
 
Upvote 0
Hi cyrilbrd, thumbs up for your questions as it really helps clarify things before spend time drilling down...

Accurate answer depends on accurate question...Better ask than assume, also I am just being curious at the construction of the string and the possible markers to be used for easier identification.
 
Upvote 0
Sorry if my question was vague. The Internal notes will be in one cell. Eg. Id 355104 will be in cell A2 and the internal notes will be in cell B2. The internal notes length will vary. but the # i'm trying to extract will be a fixed length of 9.
I would prefer a formula but if VBA is easier then happy to try that. I haven't used VBA before so might struggle to understand the VBA code though.
 
Upvote 0
Thanks for the code apo. I havent used VBA before so how would I go about inserting this code and execute it? Please help.
 
Upvote 0
Sorry if my question was vague. The Internal notes will be in one cell. Eg. Id 355104 will be in cell A2 and the internal notes will be in cell B2. The internal notes length will vary. but the # i'm trying to extract will be a fixed length of 9.
I would prefer a formula but if VBA is easier then happy to try that. I haven't used VBA before so might struggle to understand the VBA code though.
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
 
Upvote 0
Hi Rick..

I tried your formula (after adjusting it to work on Column B).. but it only returned the first 9 digit value found in each cell.. is there a way to adapt your formula so it picks up all 9 digit values in each cell?

For example.. the first cell it searches has two 9 digit values.. (130824435 and 130807425). but only 130824435 is being extracted..

Hi 77winston..

Assuming your data is in Column A:B and has a header row in row 1...

Add a command button (Developer Tab > Insert > Active X Command button) to your sheet. Then double click on the button (while in Designmode) and paste the code provided into it..

Hope that helps..
 
Last edited:
Upvote 0
apo,

The macro code by apo returned the following:


Excel 2007
ABC
1IDINTERNAL NOTES
2355104Customers 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 didnt 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 =========================================================130824435 130807425
3355489TIS job number is 130675537. Entered by: Patricia Mackin Entered Date: 19/06/2013 4:33:14 PM =========================================================130675537
Sheet1



apo,

Nicely done - one for my archives - thanks.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,254
Members
448,556
Latest member
peterhess2002

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