Taking just numbers from a cell

[Welsh Mark]

Hi Guys,

Is there a formula or function that would allow me to take the first eleven numbers from a cell?

I have a spreadsheet with a colunm of telephone numbers, I only want the first 11 numbers (which make up a UK telephone no). There are quite a few different ways the telephone numbers appear in files.

02920886500 ext 459
020 87541925 - Mr Jones
07817799232 M 029 20886500
(029) 20885612
01200 443311 (ACCTS)
0161-796-7337 (07860) 328311

 

[pgc01]

Hi

In the examples you posted you just have to get rid of "(", ")", "-" and spaces.

Try:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"(",""),")","")," ",""),"-",""),11)

If it's not enough please post a rigorous specification.