+ Reply to Thread
Results 1 to 6 of 6

If Cell contains variuos Text, logical yes output cell text, logical No Trim

  1. #1
    Registered User
    Join Date
    12-13-2018
    Location
    Reading
    MS-Off Ver
    MS Office 2016
    Posts
    5

    If Cell contains variuos Text, logical yes output cell text, logical No Trim

    Hi All,

    Been Struggling to work out but basicall I have the following Cell text and want to do the following:

    Nti09:14/id1/OP/ARgemeen Disarm Level A
    Nti16:13/id1/CL/AAlgemeen Armed Level A
    Nti02:01/RP/APeriodic Test

    Now unless the Cell contains AP for example I want to trim and leave the following Text, I can get this to work on one Find, but there are multiple condition and tried adding an OR function with no success:

    Output:

    Nti09:14/id1/OP
    Nti16:13/id1/CL
    Nti02:01/RP/APeriodic Test

    =IF(OR(FIND("/AA",$H259,1),(FIND("/AN",$H259,1)),(FIND("/AR",$H259,1)),(FIND("/AS",$H259,1)),(FIND("/AT",$H259,1))),$H259,
    TRIM(LEFT(SUBSTITUTE($H259,"/A",REPT(" ",100)),100)))

    Individual that works is as followed, but if this doesn't find AR I get a #Value! error.

    =IF(FIND("/AP",$H258,1),$H258,
    (TRIM(LEFT(SUBSTITUTE($H258,"/A",REPT(" ",100)),100))))

    Any help is much appriciated
    Last edited by mwilliams400; 02-12-2019 at 12:01 PM. Reason: solved

  2. #2
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.84 for Mac MS 365
    Posts
    8,499

    Re: If Cell contains variuos Text, logical yes output cell text, logical No Trim

    I would do it more like this... (I like search better than find but that is personal preference)
    =IF(OR(ISNUMBER(SEARCH("/aa",$H259)),ISNUMBER(SEARCH("/an",$H259))...
    I think this
    =IF(OR(FIND("/AA",$H259,1),(FIND("/AN",$H259,1)),(
    is stopping the formula after one though I couldn't test it.
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: If Cell contains variuos Text, logical yes output cell text, logical No Trim

    Hi

    You can try
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: If Cell contains variuos Text, logical yes output cell text, logical No Trim

    I simplify the formula
    try this
    =IFERROR(IF(FIND("/A",SUBSTITUTE($H259,"/AP","")),LEFT($H259,FIND("/A",SUBSTITUTE($H259,"/AP",""))-1)),$H259)

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: If Cell contains variuos Text, logical yes output cell text, logical No Trim

    Or try

    =TRIM(LEFT(SUBSTITUTE($H259,"/",REPT(" ",90),3),90))

  6. #6
    Registered User
    Join Date
    12-13-2018
    Location
    Reading
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: If Cell contains variuos Text, logical yes output cell text, logical No Trim

    Thks alls got the information needed and all work in different ways, gone with the ISnumber Search option for the moment

+ 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. Replies: 7
    Last Post: 01-07-2015, 06:29 AM
  2. [SOLVED] constructing IF logical test based on text string stored in other cell
    By 7344Robin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-29-2014, 11:27 AM
  3. [SOLVED] How do I use a logical IF statement with TEXT in EXCEL
    By SchnidRoCK in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-16-2014, 03:13 PM
  4. Text to particular logical format
    By akashmgr in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-21-2013, 03:50 AM
  5. Logical formula with output based on the text input
    By rusoo7 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-02-2009, 09:03 AM
  6. Using Text As The Logical Value in IF Functionn
    By redshark in forum Excel General
    Replies: 1
    Last Post: 07-18-2008, 04:43 AM
  7. CREATE A LOGICAL TEST CONTAINING TEXT
    By help needed in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-17-2006, 10:05 AM

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