Taking just numbers from a cell

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Taking just numbers from a cell

  1. #1
    Board Regular
    Join Date
    Aug 2009
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    18,998
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    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.

User Tag List

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