+ Reply to Thread
Results 1 to 6 of 6

Formula to Extract Numbers from Text Cell

  1. #1
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    61

    Formula to Extract Numbers from Text Cell

    Hello,

    I was wondering if someone could help me. I need a formula to extract numbers. The problem is that what I need are loan numbers and some numbers within the cell is not part of the loan number. For instance:

    DATA (Column A)
    B012.T01234TOPSHEET
    .1234567890T22222ADM
    B1235678901234T22222

    RESULT (Column B)
    BLANK (No Loan Number to extract)
    1234567890
    5678901234


    Is there a formula than can do this for me?

    The amount of data changes every month. Sometimes there is one day of activity and sometimes there are 5 to 10 days worth of activity. I need to do the same formula for multiple spreadsheet and would also like to know if I can create a macro for this as well.


    Thank you!
    ~MHamid
    Last edited by MHamid; 12-03-2012 at 03:49 PM.

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to Extract Numbers from Text Cell

    I'm not entirely clear about the criteria. It appears that you want to find the first "T" and extract the 10-digit value that precedes it.
    If that's true, try this regular formula:
    B1: =IF(COUNT(-MID(A1,FIND("T",A1)-10,10)),MID(A1,FIND("T",A1)-10,10),"")

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Banned User!
    Join Date
    10-14-2006
    Posts
    1,211

    Re: Formula to Extract Numbers from Text Cell

    Maybe this...

    =MID(A2,FIND("T",A2)-10,10)

  4. #4
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    61

    Re: Formula to Extract Numbers from Text Cell

    AWESOME!! This works perferctly!! I shoudl have just asked sooner instead of spending hours trying to figure it out.

  5. #5
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    61

    Re: Formula to Extract Numbers from Text Cell

    Teethless mama, yours worked great as well!! Thanks for your help.

  6. #6
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Formula to Extract Numbers from Text Cell

    Quote Originally Posted by MHamid View Post
    AWESOME!! This works perferctly!! I shoudl have just asked sooner instead of spending hours trying to figure it out.
    Great!
    Don't forget to mark this post SOLVED so others will know. (Select the first post...then: Thread_tools.Mark_this_thread_solved.)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1