Formula to look up multiple values and extract text from string - Facebook Advertising

Hi Excel Forum,


Is there a formula that searches a column for multiple values to extract specified text? The (Mid, Right, Left) formulas only work when the character limit is equal for each string in the column.

Here is an example:

A1 - 2012 Campaign - CPC - Acquisition - Sponsored Story - W18+
A2 - 2012 Campaign - oCPM - Acquisition - Standard Ad - W18+
A3 - 2012 Campaign - CPM - Acquisition - Application Ad - A18+

Now in columns B I want to extract "Sponsored Story" or "Application Ad" for all strings that include that word.

IN column C I want to extract all ads that were "oCPM" "CPC" or "CPM"

These attributes will allow us to pivot the data properly. This is a common issue for Facebook Advertisers right now as there are so many attributes that one can export within ads manager without having access to the ADS API. If you can answer this I am sure it would help many people stuck in the same situation I am in. Many thanks in advance.

Regards,
Excel Forum Newbie

Hey Soren,

Thanks for the quick response. Could you provide me with an example of what formula to use for the following string:

2012 Campaign - CPC - Sponsored Story - A18+ - Mobile - Precise Interest - Cooking

So there would need to be 7 fields per delimiter.

Thanks!

Soren,

Thank you so much this will save me a ton of time. Now, I am stuck on one more thing the "Destination Cells" where I want the string to be pasted into is not right after the original string i.e.in the example below I would want to paste the delimiter fields into the column beginning at D2 - Is this possible?

A2: The - Sky - Is - Blue
B2: Metric 1
C2: Metric 2
D2:


Thanks again.

Originally Posted by Søren Larsen

It's not a formula. "Text to columns" is found under "Data" in the ribbon. Mark the area with your strings and then click it.

Else I provided a formula solution to a similar problem here:
http://www.excelforum.com/excel-form...59#post2911859
You'll need to adapt to your specific case by inter alia replacing "." with " - " in the formula.