Results 1 to 5 of 5

extracting numbers with the same word in diferrent position

Threaded View

  1. #1
    Forum Contributor
    Join Date
    07-20-2009
    Location
    philippines
    MS-Off Ver
    Excel 2007
    Posts
    203

    extracting numbers with the same word in diferrent position

    wahhhh! need help from you guys, almost done with my new payoff worksheet and then this small bug appears and my excitement like "poof"(gone)!.



    with this code --> =if(search("ins",i27,),LOOKUP(99^99,--("0"&MID(I27,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},I27&"0123456789")),ROW($1:$10000))))+0

    i can get the value if the word contains "ins" like this --> "Ins 723.00" return 723.00 which is correct, and then i've encountered a word which is like"723.00 Ins", and martin gave me the code to get from both sides which is this one --> =IF(ISNUMBER(SEARCH("Ins",A1)),LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000))))+0,"") . Work perfectly. and my big problem is that the word Ins 723.00 means Insurance and the 723.00 means Inspection, so it means they're different. and it means I cant use martin's code with this.

    So is there any way i can get only the value if the word Ins is in the right side of the amount and also the code if the value is in the left side of the amount. I need two formula for this. Thanks guys!!
    Last edited by darkhangelsk; 08-16-2009 at 07:45 AM.

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