Credit Card Number

I have a file contain cells having 16 digit credit card number with junk data in between as well. As an example
3486824861529530=0
77681879192405=0
12);3917692181846980
101)980735609630572
95)6040002796620470=0
^7838705501761190
6114013383107810*/
8421 7777 9057 6370
9330 64 94 7640 6740
7137482677881720
6982488973900610

What I want to do is as follows
1) Remove all spaces in the cells.
2) After removing the spaces, check in the cell for sixteen consecutive numeric characters which would be the credit card number
3) Populate the credit card number in a separate column.

Will you be able to help?

The Excel Add-in called ASAP Utilities has features that will perform most of
the tasks you need. It's available free at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3



"raj_srini" wrote:

>
> I have a file contain cells having 16 digit credit card number with junk
> data in between as well. As an example
> 3486824861529530=0
> 77681879192405=0
> 12);3917692181846980
> 101)980735609630572
> 95)6040002796620470=0
> ^7838705501761190
> 6114013383107810*/
> 8421 7777 9057 6370
> 9330 64 94 7640 6740
> 7137482677881720
> 6982488973900610
>
> What I want to do is as follows
> 1) Remove all spaces in the cells.
> 2) After removing the spaces, check in the cell for sixteen consecutive
> numeric characters which would be the credit card number
> 3) Populate the credit card number in a separate column.
>
> Will you be able to help?
>
>
> --
> raj_srini
> ------------------------------------------------------------------------
> raj_srini's Profile: http://www.excelforum.com/member.php...o&userid=32588
> View this thread: http://www.excelforum.com/showthread...hreadid=549038
>
>

The following UDF should meet your needs:
It needs a reference (VBE Tools>References) to :
Microsoft VBScrit Regular Expressions 1.0

HTH
--
AP

'-----------------------------------
Function CreditCardNo(ByVal sRef As String) As String
Static re As RegExp
Dim mc As MatchCollection

If re Is Nothing Then
Set re = New RegExp
re.Pattern = "\d{16}"
End If
sRef = Replace(sRef, " ", "")
Set mc = re.Execute(sRef)
If mc.Count <> 1 Then
CreditCardNo = CVErr(xlErrValue)
Else
CreditCardNo = mc(0).Value
End If
End Function

"raj_srini" <raj_srini.28zmlp_1149609310.7622@excelforum-nospam.com> a écrit
dans le message de news:
raj_srini.28zmlp_1149609310.7622@excelforum-nospam.com...
>
> I have a file contain cells having 16 digit credit card number with junk
> data in between as well. As an example
> 3486824861529530=0
> 77681879192405=0
> 12);3917692181846980
> 101)980735609630572
> 95)6040002796620470=0
> ^7838705501761190
> 6114013383107810*/
> 8421 7777 9057 6370
> 9330 64 94 7640 6740
> 7137482677881720
> 6982488973900610
>
> What I want to do is as follows
> 1) Remove all spaces in the cells.
> 2) After removing the spaces, check in the cell for sixteen consecutive
> numeric characters which would be the credit card number
> 3) Populate the credit card number in a separate column.
>
> Will you be able to help?
>
>
> --
> raj_srini
> ------------------------------------------------------------------------
> raj_srini's Profile:
> http://www.excelforum.com/member.php...o&userid=32588
> View this thread: http://www.excelforum.com/showthread...hreadid=549038
>