Results 1 to 7 of 7

Thread: Multiple Contains formula to show text

  1. #1

    Question Multiple Contains formula to show text



    Register for a FREE account, and/
    or Log in to avoid these ads!

    ok, so my excel knowledge is poor, other than a pivot table and simple formulas I get lost quite easily.

    What I am trying to do is make a weekly report work a lot more automatically than manual by using the core column to fill in several others.

    The core column will show a namel

    WE_010115_F_PC
    (Brand name, date, audience, format)
    There are 10 brand names, around 5 different audience types, and 8 formats.

    Each should have it's own column, so I would want a formula along the lines of:

    IF Cell contains '_PC' then show "POSTCARD" or IF Cell contains '_REG' then show "REGULAR" or if cell contains '_1s' then show "1SHOT" .... and so on...

    Is this possible? if so, any and all help will be greatly appreciated.

    Thanks in advance!

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Try this.

    Create a table on the side somewhere of all the abbreviations and their corresponding actual text.

    e.g

    in say X1:Y10,

    _PC POSTCARD
    _REG REGULAR
    _1S 1SHOT

    etc...

    then assuming your first string, WE_010115_F_PC, is in A2, then in B2 use formula:

    =LOOKUP(10^10,FIND($X$1:$X$3&"_","_"&A2&"_"),$Y$1:$Y$3)

    adjust the references to suit.

    You can copy the formula down the column.


  3. #3
    Thank you - This generally seems to work, but it doesn't seem to like the numbers in _1shot etc..
    those fields are just returned with a '0' :/

  4. #4
    NBVC used _1S in his table in X1:Y10, he should have used _1s (lower-case) as per your data.

  5. #5
    Quote Originally Posted by Bob Phillips View Post
    NBVC used _1S in his table in X1:Y10, he should have used _1s (lower-case) as per your data.
    I did consider it was case sensitive, however it hasn't seemed to have rectified anything.

    Currently. in cell A661 I have:
    CI_020115_F_3Step

    Then in Cell D661 I have:
    =LOOKUP(10^10,FIND($Z$1:$Z$18&"_","_"&A661&"_"),$AA$1:$AA$18)

    The table is as follows:
    Click image for larger version. 

Name:	table.JPG 
Views:	12 
Size:	29.9 KB 
ID:	3026

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,429
    Articles
    0
    Excel Version
    Excel 2016
    Should that be that you have something more like:

    CI_020115_F_3S in A661?

    as per your original you were going from the abbreviation to the long form...


  7. #7
    Perfect, that's done it.
    We just need to adjust our original naming convention to ensure we abbreviate at the source.

    Thank you so much!

Posting Permissions

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