+ Reply to Thread
Results 1 to 4 of 4

If is number search function

  1. #1
    Forum Contributor
    Join Date
    06-24-2013
    Location
    United Kingdom
    MS-Off Ver
    Office 365
    Posts
    114

    If is number search function

    I have some text in a cell that is

    Order 10
    Order 15
    25 copies
    10 copies

    I need a formula that is going to look at the cell with the text in it, see if these variables are in that cell and then depending what variable it is output a specific value

    So Order 10 would be 1:10
    Order 15 would be 1:15
    25 copies would be 1:25

    and so forth

    so far i have =IF(ISNUMBER(SEARCH('[Vlookup Tables VIP.xlsx]Variants'!$A$3,AL7)), "VARIANT", "IGNORE")

    with the Vlookkup tables VIP being my looktabs.

  2. #2
    Forum Contributor
    Join Date
    01-18-2013
    Location
    Prague
    MS-Off Ver
    Excel 2013
    Posts
    159

    Re: If is number search function

    try this formula (assuming that the revised text is in cell A2)"

    =IFERROR("1:"&ROUND(MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),4),0),"")

    One watch out, it works only till 9999.
    Last edited by igormigor; 04-29-2016 at 06:44 AM.

  3. #3
    Forum Expert
    Join Date
    06-08-2012
    Location
    Left the forum!
    MS-Off Ver
    Left the forum!
    Posts
    5,189

    Re: If is number search function

    This one works with all of the examples provided, no limit on the numeric value.

    ="1:"&IFERROR(VALUE(LEFT(A1,FIND(" ",A1)-1)),MID(A1,FIND(" ",A1)+1,LEN(A1)))

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: If is number search function

    another way:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    with Control+Shift+Enter
    retrive all numbers from cell
    Please Login or Register  to view this content.
    Last edited by sandy666; 04-29-2016 at 07:51 AM. Reason: typo

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using IS Number and Search function
    By MarieK in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-09-2014, 07:33 AM
  2. VBA Code - Search text & search number & search qty and result - Urgent Please
    By naresh73 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-15-2014, 10:51 AM
  3. IF(count(search) Function not working when search from text from a cell
    By joshnathan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-09-2013, 05:13 AM
  4. Replies: 1
    Last Post: 10-18-2012, 05:52 AM
  5. [SOLVED] Search in a row for a column number after a Vlookup search.
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-11-2012, 04:25 AM
  6. Replies: 13
    Last Post: 12-03-2010, 10:39 PM
  7. [SOLVED] How do I create a function to search a range for a number and...
    By NECCExcel in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-18-2005, 05:20 PM

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