Results 1 to 2 of 2

Taking just numbers from a cell

This is a discussion on Taking just numbers from a cell within the Excel Questions forums, part of the Question Forums category; Hi Guys, Is there a formula or function that would allow me to take the first eleven numbers from a ...

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    82

    Default Taking just numbers from a cell

    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

  2. #2
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,452

    Default Re: Taking just numbers from a cell

    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.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com