+ Reply to Thread
Results 1 to 3 of 3

Formula needed to extract number

  1. 07-01-2013, 02:18 PM #1
    MHamid
    MHamid is offline
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    65

    Formula needed to extract number

    Hello,

    I am hoping that someone could help me figure out a formula that would extract the number from a given text in one column. The data is in one column. Text to columns will not work because the data is not aligned for all. I was looking for a formula that will extract just the account number from the given data. The account number will be located before the name of the state. Also the account number vaires in count. Some are 6 characters long and others are 10 characters long.
    The sample data is below. When copied over, it should be 4 rows of data in one column (column A).


    OCWEN LOAN SERVICING, LLC ITF GMACM HOME 796686400 Illinois Market 2013-06-28 Statement
    OCWEN LOAN SERVICING LLC 976601 Illinois Market 2013-06-28 Statement
    OCWEN LOAN SERVICING LLC 29553194 Arizona Market 2013-06-28 Statement
    OCWEN LOAN SERVICING LLC 85070241 Kentucky Market 2013-06-28 Statement


    Please let me know if further information is needed.



    Thank you,
    ~Miriam
    Reply With QuoteRegister To Reply
  2. 07-01-2013, 02:20 PM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,956

    Re: Formula needed to extract number

    assuming your data is in A1, use this in B1, copied down....

    =LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 07-01-2013, 02:43 PM #3
    MHamid
    MHamid is offline
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Formula needed to extract number

    Thank you!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1