The following UDF should meet your needs:
It needs a reference (VBE ToolsReferences) 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" a écrit
dans le message de news:
...

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