+ Reply to Thread
Results 1 to 14 of 14

if formula looking at value in middle of text string

  1. #1
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    if formula looking at value in middle of text string

    Hi folks,

    I have a column of data as follows:

    ab10 5 for sale
    ab20 0 for sale
    ab30 0 for sale
    ab30 3 for sale
    ab40 0 for sale
    ab30 19 for sale
    ab40 4 for sale

    Any item that is ab30 or ab40 is no longer fit for purpose, but some still have items for sale. So, what I need is a formula that looks to see if a text string starts with either ab30 or ab40 and if it has 0 for sale it should return "REMOVE". If the value is greater than 0 than "USE UP". However, anything that is ab10 or ab20 should be "GOOD".

    So, the results should be:

    ab10 5 for sale GOOD
    ab20 0 for sale GOOD
    ab30 0 for sale REMOVE
    ab30 3 for sale USE UP
    ab40 0 for sale REMOVE
    ab30 19 for sale USE UP
    ab40 4 for sale USE UP

    Any ideas!?

    SA
    Last edited by SAsplin; 04-25-2013 at 06:58 AM.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: if formula looking at value in middle of text string

    is each one all in one cell?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Contributor
    Join Date
    04-23-2013
    Location
    Abu Dhabi United Arab Emirates
    MS-Off Ver
    Excel 2010
    Posts
    178

    Re: if formula looking at value in middle of text string

    try this formula
    =IF(AND(LEFT(A1,4)<>"ab30",LEFT(A1,4)<>"ab40"),"GOOD","REMOVE")

  4. #4
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Yup - so, for example, cell A1 could be "ab10 5 for sale", cell A2 "ab20 0 for sale" etc... etc...

  5. #5
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Thanks acerrhod, but that doesn't fulfill the 'use up' part of my problem.

  6. #6
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: if formula looking at value in middle of text string

    Give a try to this.

    =IF(OR(LEFT(A1,4)="ab10",LEFT(A1,4)="ab20"),"Good",IF(OR(LEFT(A1,6)="ab30 0",LEFT(A1,6)="ab40 0"),"remove","use up"))
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  7. #7
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Fotis1991 - that's a great shout, but just to throw a spanner in the works I've noticed some of the cells don't have a numeric value, e.g. "ab20 for sale" and these too should be marked "Good".

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: if formula looking at value in middle of text string

    =IF(AND(MID(A1,6,1)="0",OR(LEFT(A1,4)={"ab40","ab30"})),"remove",IF(OR(LEFT(A1,4)={"ab40","ab30"}),"use up","good"))

  9. #9
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: if formula looking at value in middle of text string

    I beleieve that my formula does this.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Brilliant guys - almost there. My apologies for changing the goal posts once more but with your formulas an item "ab30 for sale" or "ab40 for sale" comes back as "use up" - it would be better if this came back as "remove".

    That's a big spanner in the works, I know... If this isn't possible I could possibly still work with your answers.

  11. #11
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: if formula looking at value in middle of text string

    .
    . but with your formulas an item "ab30 for sale" or "ab40 for sale" comes back as "use up"
    I didn't tested Martin's formula but mine gives "Good" as answer in this case.

    So try this one.

    =IF(OR(LEFT(A1,6)="ab30 0",LEFT(A1,6)="ab40 0",LEFT(A1,6)="ab10"&" ",LEFT(A1,6)="ab20"&" "),"remove",IF(OR(LEFT(A1,4)="ab10",LEFT(A1,4)="ab20"),"Good","use up"))

  12. #12
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Thanks for your help guys. Kudos left in reputation.

  13. #13
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: if formula looking at value in middle of text string

    ...............................

  14. #14
    Forum Contributor
    Join Date
    08-17-2009
    Location
    West Midlands
    MS-Off Ver
    Excel 2016
    Posts
    213

    Re: if formula looking at value in middle of text string

    Thanks for your help guys. Kudos left in reputation.

+ 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