Hope this will get you started
First insert a new sheet (Cleaned contacts partial) and use the UDF
hereafter to extract only digits from the phone numbers in col 1 to 4 in
this new sheet
Code:
--------------------
=IF(ISERROR(personal.xls!DigitsDashesAll(Contacts! A2)+0);"";personal.xls!DigitsDashesAll(Contacts!A2 )+0)
--------------------
Link the new sheet to the original one to retrieve whatever data
needed
Use VLOOKUP to retrieve your data in Sheet1
Code:
--------------------
Function DigitsDashesAll(ByVal s As String) As String
'Harlan Grove, worksheet.functions, 2003-10-20
'concatenate all digits and dashes found in a string
Dim i as long, n as long
n = Len(s)
For i = 1 To n
If Mid(s, i, 1) Like "[!-0-9]" Then Mid(s, i, 1) = " "
Next i
DigitsDashesAll = Application.WorksheetFunction.Substitute(s, " ", "")
End Function
--------------------
Don't forget to replace semi-colons with commas where needed
+-------------------------------------------------------------------+
|Filename: Copy of export june 2009-2.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=157|
+-------------------------------------------------------------------+
--
Pecoflyer
Cheers -
------------------------------------------------------------------------
Pecoflyer's Profile:
http://www.thecodecage.com/forumz/member.php?userid=14
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=104270