Macro to extract 10 digit number from cell starting with 501 & 350

[amandeep08]

I have a macro that will fetch 10 digit number starting with 501.

I want to change the same so that it can also fetch the 10 digit number starting with 350.

Below is the code:

Sub Get501s()
Dim R As Long, LastRow As Long, XLXS As String, V As Variant, Arr As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For R = 1 To LastRow
Arr = Split(Cells(R, "A").Value & " ", 501)
XLXS = ""
For Each V In Arr
If V Like "#######[!0-9]*" Then XLXS = XLXS & ", 501" & Left(V, 7) & ".xlxs"
Next
Cells(R, "A").Value = Mid(XLXS, 3)
Next
End Sub

 

[Rick Rothstein]

I'll have to think about a solution for you, but using Split is probably not the way to go (it would miss values like 5019950199).

 

[amandeep08]

Sir,

I'll have to think about a solution for you, but using Split is probably not the way to go (it would miss values like 5019950199).

Its not split.

Now the data has 501 as well as 350 series and i want to get the data for both of them in the same sheet rather split.

Can't we apply or condition.

 

[alansidman]

Show us some sample data. Use XL2BB to upload your sample data.

 

[amandeep08]

Show us some sample data. Use XL2BB to upload your sample data.

Raw data	Required Data
C:\Users\amandeep.s\Desktop\5-Apr-21\BASF-5021209306.xlsm	5021209306.xlsm
C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500051959.xlsm	3500051959.xlsm
C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500051967.xlsm	3500051967.xlsm
C:\Users\amandeep.s\Desktop\5-Apr-21\qualcomn 5021209231 revised.xlsm	5021209231.xlsm
C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500053967/3500053234.xlsm	3500053967/3500053234.xlsm
C:\Users\amandeep.s\Desktop\5-Apr-21\qualcomn 5021207542/5021203245 revised.xlsm	5021207542/5021203245.xlsm
Raw data is available in Column A, data required in column B but once data B will get generated, it should overwrite column A

 

[Rick Rothstein]

I wish you would have shown us that earlier, then I would not have wasted time on a routine to do what your actual data was instead of what you said it was. Alright then...

1) Will you ever have data that does not begin with 502 (your thread title said 501 by the way) or 350? If so, what should happen?

2) Is there always only one number or number group (joined with a slash) in each cell?

3) Is that number or number group always at the end of the cell's text?

 

[amandeep08]

I wish you would have shown us that earlier, then I would not have wasted time on a routine to do what your actual data was instead of what you said it was. Alright then...

1) Will you ever have data that does not begin with 502 (your thread title said 501 by the way) or 350? If so, what should happen?

2) Is there always only one number or number group (joined with a slash) in each cell?

3) Is that number or number group always at the end of the cell's text?

I am really sorry that i have not updated you earlier. Below is the revert to your queries:


1) Will you ever have data that does not begin with 502 (your thread title said 501 by the way) or 350? If so, what should happen?: Going forward, multiple order series may come. I will change the series accordingly. As of now, its 502 and 350. In particular cell, either it will be 502 or 350 and both the series will not be there in one cell.

2) Is there always only one number or number group (joined with a slash) in each cell?: It can be joined either by slash or by comma.

3) Is that number or number group always at the end of the cell's text?: It can be in the middle or in the beginning also

 

[Rick Rothstein]

Is that number or number group always at the end of the cell's text?: It can be in the middle or in the beginning also

Can you show us some representative example of these other possibilities. One thing I am interested in know is could there be other numbers in the non-path part of the extra text and, if so, some idea of the size of such numbers.

 

[amandeep08]

Raw Data	Required Data
C:\Users\amandeep.s\Desktop\5-Apr-21\master Order form 5021276964 to 5021276986, 5021276990	5021276964, 5021276986, 5021276990

Can you show us some representative example of these other possibilities. One thing I am interested in know is could there be other numbers in the non-path part of the extra text and, if so, some idea of the size of such numbers.

 

[aRandomHelper]

Could this work? I don't know how important is it to attach the file extension to the output, but this extracts all those 10 digits in comma separated format.

Book1.xlsm
	A	B
1	Required Data
2	C:\Users\amandeep.s\Desktop\5-Apr-21\BASF-5021209306.xlsm	5021209306
3	C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500051959.xlsm	3500051959
4	C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500051967.xlsm	3500051967
5	C:\Users\amandeep.s\Desktop\5-Apr-21\qualcomn 5021209231 revised.xlsm	5021209231
6	C:\Users\amandeep.s\Desktop\5-Apr-21\Master Order Form for B2B - 3500053967/3500053234.xlsm	3500053967, 3500053234
7	C:\Users\amandeep.s\Desktop\5-Apr-21\qualcomn 5021207542/5021203245 revised.xlsm	5021207542, 5021203245
8	C:\Users\amandeep.s\Desktop\5-Apr-21\master Order form 5021276964 to 5021276986, 5021276990	5021276964, 5021276986, 5021276990
Sheet4

Sub ME1167190_numbers()
    Dim i As Long, j As Long, s, t
    
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        s = ""
        If Cells(i, 1).Value Like "*##########*" Then
            For j = 1 To Len(Cells(i, 1).Value) - 9
                t = ""
                If Mid(Cells(i, 1).Value, j, 10) Like "##########" Then t = Mid(Cells(i, 1).Value, j, 10)
                If t Like "502*" Or t Like "350*" Then s = s & t & ", "
            Next
        End If
        If s <> "" Then Cells(i, "B").Value = Left(s, Len(s) - 2) 'change "B" to "A" to overwrite column A directly.
    Next
End Sub