How to extract a 15 or 16 digit number from an email.

[kaushalnaman]

Hi there

I am new to this forum. I have been assigned a task at work for which i need your help.

I have a cell containing body of an email.How can i check for 15 or 16 digit number in the text and extract it. Basically i am checking for a credit card number in the email and if it is there then i need to extract it to adjacent colum.The sample data is like this below.

Sample Data Desired Result - 2390786123451234

I would like to lodge a complaint regarding the bad service received by one of your staff when I had visited your bank branch on 27th of March 2012. I am a credit card holder in your bank with my credit card number being 2390786123451234.What is the solution?

<tbody>
</tbody>

 

[oldbrewer]

	0	1	2	3	4	5	6	7	8	9	min
ng 2390786123451234.What is the solution?	7	11	4	5	14	15	10	8	9	6	4
the minimum value has to be the start of a series of numbers
so =mid(A2,4,16) finds your number

<colgroup><col><col span="13"></colgroup><tbody>
</tbody>

 

[Domenic]

If your number is always either a 15 or 16 digit number, and if there are no other numbers that occur after this number, maybe..

=TRIM(MID(A1,LEN(A1)-MATCH(TRUE,ISNUMBER(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)+0),0)-15+1,16))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Rick Rothstein]

I have a cell containing body of an email.How can i check for 15 or 16 digit number in the text and extract it. Basically i am checking for a credit card number in the email and if it is there then i need to extract it to adjacent colum.The sample data is like this below.

Sample Data Desired Result - 2390786123451234

I would like to lodge a complaint regarding the bad service received by one of your staff when I had visited your bank branch on 27th of March 2012. I am a credit card holder in your bank with my credit card number being 2390786123451234.What is the solution?

<tbody>
</tbody>

Here is a UDF (user defined function) that will find a 15 or 16 digit number in the text no matter where within the text it is located...

Function CCNum(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(" " & S, X, 18) Like "[!0-9]################[!0-9]" Then
      CCNum = Mid(S, X, 16)
      Exit Function
    ElseIf Mid(" " & S, X, 17) Like "[!0-9]###############[!0-9]" Then
      CCNum = Mid(S, X, 15)
      Exit Function
    End If
  Next
End Function

HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CCNum (short for Credit Card Number) just like it was a built-in Excel function. For example,

=CCNum(A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[kaushalnaman]

Many Thanks Rick That worked like a charm. You are a genius.
One question is if i need to modify this function to find also a 17 or 18 digit number (As credit card number can be maximum of 18 digits) then i can edit the above function and add more else if loops .

 

[Rick Rothstein]

Many Thanks Rick That worked like a charm. You are a genius.
One question is if i need to modify this function to find also a 17 or 18 digit number (As credit card number can be maximum of 18 digits) then i can edit the above function and add more else if loops .

Yes, but you would have to add the longer ones first in descending order of length (otherwise the If..Then would trap a smaller substring from within a longer number). But let me think about it for a little while (I wasn't aware credit card numbers varied between 15 and 18 digits)... there may be a more compact solution than adding the extra ElseIf's.

 

[Rick Rothstein]

But let me think about it for a little while (I wasn't aware credit card numbers varied between 15 and 18 digits)... there may be a more compact solution than adding the extra ElseIf's.

Okay, give this UDF a try (it should successfully pull out numbers that are made up of from 15 to 18 consecutive digits)...

Function CCNum(S As String) As String
  Dim X As Long, Z As Long, Min As Long, Max As Long
  Min = 15
  Max = 18
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, Min + 1) Like "[!0-9]" & String(Min, "#") Then
      For Z = X + Min - 2 To X + Max
        If Mid(S & " ", Z, 1) Like "[!0-9]" Then Exit For
      Next
      If Z - X < Max + 1 Then
        CCNum = Mid(S, X, Z - X)
        Exit Function
      End If
    End If
  Next
End Function

 

[kaushalnaman]

Thanks Rick that was awesome.This UDF worked great!

Many thanks.

 

[Rick Rothstein]

Okay, give this UDF a try (it should successfully pull out numbers that are made up of from 15 to 18 consecutive digits)...

Function CCNum(S As String) As String
  Dim X As Long, Z As Long, Min As Long, Max As Long
  Min = 15
  Max = 18
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, Min + 1) Like "[!0-9]" & String(Min, "#") Then
      For Z = X + Min - 2 To X + Max
        If Mid(S & " ", Z, 1) Like "[!0-9]" Then Exit For
      Next
      If Z - X < Max + 1 Then
        CCNum = Mid(S, X, Z - X)
        Exit Function
      End If
    End If
  Next
End Function

This is the exact same code as above except that instead of variables name Min and Max, I changed them to DigitsMinimum and DigitsMaximum for self-documenting purposes.

Function CCNum(S As String) As String
  Dim X As Long, Z As Long, DigitsMinimum As Long, DigitsMaximum As Long
  DigitsMinimum = 15
  DigitsMaximum = 18
  For X = 1 To Len(S)
    If Mid(" " & S & " ", X, DigitsMinimum + 1) Like "[!0-9]" & String(DigitsMinimum, "#") Then
      For Z = X + DigitsMinimum - 2 To X + DigitsMaximum
        If Mid(S & " ", Z, 1) Like "[!0-9]" Then Exit For
      Next
      If Z - X < DigitsMaximum + 1 Then
        CCNum = Mid(S, X, Z - X)
        Exit Function
      End If
    End If
  Next
End Function