We have some data an excel spreadsheet that contains uk phone numbers in text format. The phone data could be in of several columns, and be included along with other address fields like post codes.
For example (if A1, B1 and C1) are excel cells
A1 B1 C3
AB4 1LS (01706 882 261) Mr Smith Cardiff
or it could be in a different order and format
A2 B2 C2
Mr Smith Cardiff AB4 1LS Tel 01706 882 261
We want an excel function writing that will find the telephone number and return it as a number. Suppose the function was called =Serial_No(TEXT)
Serial_No(A1&B1&C1) should return 1706882261 and should Serial_No(A2&B2&C2).
UK phone numbers are usually 10 or 11 digits long including the lead 0. It is common to write the are code in brackets eg (01236) 556876.
So the phone number will be something like the only (or first) occurrence of a numeric string of this length.
I have some sample data - to some extent trial and error maybe required. I will email this to successful bidders. We want the function to be used repeatedly, it is not a one-off conversion exercise. Text without a number should return a an error code like -1.