Hi Per, Once again thanks a lot for your help, this works perfecto!!

Cheers,
Chappy

"Per Jessen" wrote:

Hi Chappy

Thanks for your reply, I'm glad that you could use my UDF.

Here are two (three) ways to fill in the formula. The second macro is the
simplest, but it requieres that you enter the formula in AO2 before the
macro is ran. The first macro fill in the formula, you just have to change
TargetCol to the column holding your phone numbers.

Sub FillInFormula()
Dim StartRow As Long
Dim LastRow As Long
Dim FormulaCol As String
Dim TargetCol As String

StartRow = 2
LastRow = Range("A1").End(xlDown).Row
FormulaCol = "AO"
TargetCol = "AN" 'Change to phone num col
'Columns(FormulaCol).NumberFormat = "@" ' This line is needed if you choose
option # 2 below

For r = StartRow To LastRow
Range(FormulaCol & r).Formula = "=ExtractNum(" & TargetCol & r & ")" '
Option # 1
'Range(FormulaCol & r) = ExtractNum(Range(TargetCol & r))' Option # 2
Next
End Sub

Sub FillInForm()
' Requires the formula in A02 before this macro is started
LastRow = Range("A1").End(xlDown).Row
Range("AO2").Copy Range("AO2:AO" & LastRow)
End Sub


Best regards,
Per

"Chappy" skrev i meddelelsen
...
Hi Per,

Just one other query that you may be able to help with in relation to
this.

My data comes in with a variable number of rows each day. Is there a way
to
fill the relevant column down until it reaches the last line of data each
time?

In Column A there is always a value, so this will always indicate the
number
of rows of data in the file. Column AO contains the phone number formula,
beginning at AO2, that you posted. Is it possible to fill Column AO down
to
the last row as indicated by column A?