Formula/VBA to get multiple set of 5 digit numbers from a cell starting with special characters

imran1059

Board Regular
Joined
Sep 28, 2014
Messages
111
Hello Everyone,

I have only one column A with thousands of rows like following (with different length and text): My required output is available in column B and on-wards


Column AColumn BColumn CColumn DColumn EColumn E
11101&11051 INV#295961110111051
GV65245/98765&67543 06978 rh#05234 5476565245987656754354765
BKO-7975/7893/8013/7878/07946/8094/806
FR-7470/7424/7472/-FR-7503/7348/ 7433/7344573445
7530/ DXW/76170/7624 INV#E6610476170

<tbody>
</tbody>


I need a formula/vba code that will start reading from left most character of the cell in column A and check if its between 1 to 9, if it is then check if it is just a five digit number, if it is then put it into column B and then look for another 5 digit number in the same cell and upon finding any other 5 digit number put in other columns. Please also keep in consideration that the five digits number that I am looking for cannot start with zero at all. If some number is starting with zero and its five digit then ignore. Please also keep in consideration that it cannot be started with # sign. 5 digit numbers are started with any alphabet character or with space or with these special characters &/-

5 digit number can also be started with nothing. I mean if the cell itself is starting from a 5 digit number


Thanks,
Imran.
 
Last edited:

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Hi,

try with the "Like"-Operstor (pattern) "#####" or with "Regular Expression".

regards
 
Upvote 0
Give this macro a try...
Code:
Sub FiveDigitNumbers()
  Dim R As Long, X As Long, Z As Long, Data As Variant, Nums As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9#]" Then Mid(Data(R, 1), X) = " "
    Next
    Nums = Split(Application.Trim(Data(R, 1)))
    For X = 0 To UBound(Nums)
      If Not Nums(X) Like "[1-9]####" Then Nums(X) = ""
    Next
    Data(R, 1) = Join(Nums)
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , True, False, False, False, True, False
End Sub
 
Upvote 0
Too late figuring this one out, but it was certainly fun. I like Rick's solution better but here's another option.

Code:
Sub FiveDigNumbers()
    Dim row As Integer, colcount As Integer, i As Integer, lastRow As Integer
    Dim s As String, subS As String
    Dim notValidNumber As Boolean
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).row
    For row = 1 To lastRow
        colcount = 2
        s = Range("A" & row).Value
        subS = vbNullString
        For i = 1 To Len(s)
            notValidNumber = False
            If Mid(s, i, 1) Like "#" Then
                If Mid(s, i, 1) = "0" Then
                    If Len(subS) = 0 Then
                        notValidNumber = True
                    End If
                End If
                If i <> 1 Then
                    If Mid(s, i - 1, 1) = "#" Then
                        notValidNumber = True
                    End If
                End If
                If Not notValidNumber Then
                    subS = subS & Mid(s, i, 1)
                    If Len(subS) = 5 Then
                        Cells(row, colcount) = subS
                        subS = vbNullString
                        notValidNumber = False
                        colcount = colcount + 1
                    End If
                End If
            Else
                subS = vbNullString
            End If
        Next i
    Next row
End Sub
 
Upvote 0
Hi Ric,

Thanks a lot. It works perfect. However, I need one more thing that my 5 digit number should be less than 30000, it means that the 1st digit of my 5 digit number is always 1 or 2. If its more than 2 then ignore.
 
Upvote 0
with your new rule...


Code:
Sub FiveDigNumbers()
    Dim row As Integer, colcount As Integer, i As Integer, lastRow As Integer
    Dim s As String, subS As String
    Dim notValidNumber As Boolean
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).row
    For row = 1 To lastRow
        colcount = 2
        s = Range("A" & row).Value
        subS = vbNullString
        For i = 1 To Len(s)
            notValidNumber = False
            If Mid(s, i, 1) Like "#" Then
                If [COLOR=#ff0000]Mid(s, i, 1) Like "[3-9]" Or [/COLOR]Mid(s, i, 1) = "0" Then
                    If Len(subS) = 0 Then
                        notValidNumber = True
                    End If
                End If
                If i <> 1 Then
                    If Mid(s, i - 1, 1) = "#" Then
                        notValidNumber = True
                    End If
                End If
                If Not notValidNumber Then
                    subS = subS & Mid(s, i, 1)
                    If Len(subS) = 5 Then
                        Cells(row, colcount) = subS
                        subS = vbNullString
                        notValidNumber = False
                        colcount = colcount + 1
                    End If
                End If
            Else
                subS = vbNullString
            End If
        Next i
    Next row
End Sub
 
Upvote 0
Hi Tygrrboi,

Thanks for the effort but it didn't work as required. Because your code is giving me output as follows. The output should not give me anything here. but it is giving me two 5 digit numbers.

R&R-ND SATCOM INV#E661106171 FOR INSURANCE CHARGES AWB#123-454565646611045456

<tbody>
</tbody>
 
Upvote 0
Hi Rick,

Thanks a lot. It works perfect. However, I need one more thing that my 5 digit number should be less than 30000, it means that the 1st digit of my 5 digit number is always 1 or 2. If its more than 2 then ignore.
This should do what you are now asking for...
Code:
Sub FiveDigitNumbers()
  Dim R As Long, X As Long, Z As Long, Data As Variant, Nums As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp))
  For R = 1 To UBound(Data)
    For X = 1 To Len(Data(R, 1))
      If Mid(Data(R, 1), X, 1) Like "[!0-9#]" Then Mid(Data(R, 1), X) = " "
    Next
    Nums = Split(Application.Trim(Data(R, 1)))
    For X = 0 To UBound(Nums)
      If Not Nums(X) Like "[12]####" Then Nums(X) = ""
    Next
    Data(R, 1) = Trim(Join(Nums))
  Next
  Range("B1").Resize(UBound(Data)) = Data
  Columns("B").TextToColumns , xlDelimited, , True, False, False, False, True, False
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

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