Multiple Contains formula to show text

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!

Try this.

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

e.g

in say X1:Y10,


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.

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' :/

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

Bob Phillips said:

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

Click to expand...

I did consider it was case sensitive, however it hasn't seemed to have rectified anything.

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

The table is as follows:

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...

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!