+ Reply to Thread
Results 1 to 14 of 14

IF Statement where cells contain "/" plus more...

  1. 06-30-2015, 11:36 AM #1
    pupppu
    pupppu is offline
    Registered User
    Join Date
    06-30-2015
    Location
    Down South
    MS-Off Ver
    Office 2013
    Posts
    8

    IF Statement where cells contain "/" plus more...

    Hello

    Ok, here we go, my first post.

    I would be really grateful if someone could help me with this IF statement.

    I need in column 'CODE_1' to show the first digits BEFORE the '/' OR to show the text 'Not in poplation' OR 'N/A', (depending on the information in the CODE column)

    In column 'CODE_2', I need to show the digits AFTER the '/'.

    The problem I am having is the digits before and after the '/' are different lengths (2, 3 or 4 characters long).

    Any help is much appreciated

    Thank you

    BasCapture.JPG
    Reply With QuoteRegister To Reply
  2. 06-30-2015, 11:41 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: IF Statement where cells contain "/" plus more...

    Assuming your data start in A1, use this:

    =IF($A1="N/A","N/A",TRIM(MID(SUBSTITUTE("/"&$A1,"/",REPT(" ",125)),125*COLUMNS($A:A),125))+0)

    Drag across and then down.
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 06-30-2015, 11:43 AM #3
    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,929

    Re: IF Statement where cells contain "/" plus more...

    For Code1...
    =IF(OR(A2="N/A",A2="Not in Population"),A2,LEFT(A2,FIND("/",A2,1)-1))
    For code2...
    =IF(OR(A2="N/A",A2="Not in Population"),A2,MID(A2,FIND("/",A2,1)+1,99))
    both copied down
    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
  4. 06-30-2015, 11:43 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: IF Statement where cells contain "/" plus more...

    Sorry... missed the not in population bit...

    =IF($A1="N/A","N/A",IF($A1="Not in population","Not in population",TRIM(MID(SUBSTITUTE("/"&$A1,"/",REPT(" ",125)),125*COLUMNS($A:A),125))+0))
    Reply With QuoteRegister To Reply
  5. 06-30-2015, 11:46 AM #5
    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,929

    Re: IF Statement where cells contain "/" plus more...

    Glen, you left out the "Not in Pop" part

    edit: no you didnt
    Reply With QuoteRegister To Reply
  6. 06-30-2015, 11:48 AM #6
    mahju
    mahju is offline
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: IF Statement where cells contain "/" plus more...

    My be something like this

    Please Login or Register  to view this content.
    Copied down
    Last edited by mahju; 06-30-2015 at 11:53 AM.
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju
    Reply With QuoteRegister To Reply
  7. 06-30-2015, 11:52 AM #7
    pupppu
    pupppu is offline
    Registered User
    Join Date
    06-30-2015
    Location
    Down South
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: IF Statement where cells contain "/" plus more...

    Glenn, many thanks my friend. That works. You sir are a legend.

    +1 reputation and same goes to the others as well

    Reply With QuoteRegister To Reply
  8. 06-30-2015, 11:52 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: IF Statement where cells contain "/" plus more...

    Hi Pupppu... just noticed your address... I'm not that far away, mid-Down....
    Reply With QuoteRegister To Reply
  9. 06-30-2015, 11:56 AM #9
    pupppu
    pupppu is offline
    Registered User
    Join Date
    06-30-2015
    Location
    Down South
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: IF Statement where cells contain "/" plus more...

    Do you know John Smith?
    Reply With QuoteRegister To Reply
  10. 06-30-2015, 01:02 PM #10
    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,929

    Re: IF Statement where cells contain "/" plus more...

    Happy to help, thanks for the feedback
    Reply With QuoteRegister To Reply
  11. 07-02-2015, 04:44 AM #11
    pupppu
    pupppu is offline
    Registered User
    Join Date
    06-30-2015
    Location
    Down South
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: IF Statement where cells contain "/" plus more...

    Hi All

    Many thanks again for your help with this.

    UPDATE:

    There are now some cells in CODE column (column A) where there are no "/". And unfortunately the formula doesn't work for these, and I can't figure out to change the formula:

    Capture.JPG

    The formula I am using is:

    =IF($A1="N/A","N/A",IF($A1="Not in population","Not in population",TRIM(MID(SUBSTITUTE("/"&$A1,"/",REPT(" ",125)),125*COLUMNS($A:A),125))+0))

    Any help is much appreciated.

    Thank you
    Last edited by pupppu; 07-02-2015 at 04:47 AM.
    Reply With QuoteRegister To Reply
  12. 07-02-2015, 05:03 AM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: IF Statement where cells contain "/" plus more...

    If you want to remove the error in column C, amend to:

    =iferror(IF($A1="N/A","N/A",IF($A1="Not in population","Not in population",TRIM(MID(SUBSTITUTE("/"&$A1,"/",REPT(" ",125)),125*COLUMNS($A:A),125))+0)),"")
    Reply With QuoteRegister To Reply
  13. 07-02-2015, 05:08 AM #13
    pupppu
    pupppu is offline
    Registered User
    Join Date
    06-30-2015
    Location
    Down South
    MS-Off Ver
    Office 2013
    Posts
    8

    Re: IF Statement where cells contain "/" plus more...

    Thank you so much Glenn
    Reply With QuoteRegister To Reply
  14. 07-02-2015, 05:24 AM #14
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,036

    Re: IF Statement where cells contain "/" plus more...

    You're welcome... and thanks (BtW, don't know any John Smiths)
    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)

Similar Threads

  1. [SOLVED] IF Statement Evaluates as "TRUE" but Returns "FALSE" Value (Excel 2007)
    By Simcik in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-11-2014, 12:43 PM
  2. [SOLVED] IF statement to return value when cell contains a specific word finding "1" for "10"
    By arisphaedrus in forum Excel General
    Replies: 12
    Last Post: 06-12-2014, 02:11 PM
  3. How to check if a range of cells contains the word "unresponsive" and return "yes" or "no"
    By benitopjuarez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. if statement see "Male" or "Female" returning "Y" otherwise leave blank
    By qwervbnmasdf in forum Excel General
    Replies: 3
    Last Post: 12-14-2006, 01:36 PM

Tags for this Thread

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