Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using wildcards with 'if' (2007)

    I have an Excel spreadsheet with an Address 1 and Address 2 column. PO Boxes should always be in Adress 1. There are a number located in Address 2. I am looking for a way to create a formula in a new column that uses address 1--unless address 2 is a PO Box. I would love to be able to use this: =If(adr2="po8", adr2,adr1), but I can't use wildcards. Any suggestions? Thanks in advance! <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>
    egghead

  2. #2
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcards with 'if' (2007)

    Would it be possible for you to add a third column? In Col3 you could enter a formula based on Col2 ie If(B1=POBox,1,"").

    <<I would love to be able to use this: =If(adr2="po8", adr2,adr1)>>

    Your formula could be rewritten to something like: If(C1=1,Adr2,Adr1)

    Regards,
    John

  3. #3
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcards with 'if' (2007)

    The PO Boxes are all followed by a number like "483" or "220", so there are no matches to "PO Box". That's why I'm looking for a "wildcard."
    egghead

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using wildcards with 'if' (2007)

    How about:

    =If(left(adr2,2)="po", adr2,adr1)

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using wildcards with 'if' (2007)

    Are there non-PO Box addresses that start with "PO"? If not, you can use this:
    <code>
    IF(LEFT(UPPER(adr2),2)="PO",adr2,adr1)
    </code>
    If there are other addresses starting with "PO", I'd have to know more about the addresses to see whether it is possible to distinguish them from PO Box addresses.

  6. #6
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcards with 'if' (2007)

    If the first two characters are PO then you could use:

    =if(left(adr2,2)="PO",adr2,adr1)

  7. #7
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using wildcards with 'if' (2007)

    Yes--This worked! Thank you for teaching me something new to use today!
    egghead

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •