Splitting Text Into Description Line 1 & 2

Exporting creatives from Marin (Our third party tracking tool) has some variability between search engines.

• Bing & Yahoo ads have 70/0 (characters in description line 1/description line 2)
• Google, exports the ads 35/35

In order to take the 70 character length and split them into 35/35, you cannot simply cut it in half or words will get split between lines.

The formulas below take a text string and split it at the last word before the 35 mark and put the remainder in description line 2.

Assumptions:

• The text to be split is A2
• Description line 1 is B2
• Description line 2 is in C2
• Length of description line 2 is in D2 (checking purposes)

Formulas:

Cell B2:

=TRIM(LEFT(A2,FIND("@",SUBSTITUTE(MID(A2,1,36)," ","@",36-LEN(SUBSTITUTE(MID(A2,1,36)," ",""))),1)))

Cell C2:

=TRIM(MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2)))

Cell D2:

=len(c2)

*Check the length of D2 to make sure ads do not exceed 35 characters

**To modify the cut off length change all 36's to the length you want +1

***To modify reference cells, find replace A2 or B2 with (Cell w/ text to split) and (Description Line 1) respectively

Formula Fridays: Stripping Broad Match Modifier, Brackets & Quotation Marks from Keyword Lists

When using tiered bidding strategies, it is necessary to make sure keywords with different match types are identical in order to vlookup against each other. However, when exporting from AdWords, you may receive a bulk sheet looking like this:

The formula provided below will transform the match-specific keywords into generic ones:

- Assume B2 is match type and A2 is the keyword

=IF(B2="Broad",SUBSTITUTE(SUBSTITUTE(A2,"'+",""),"+",""),IF(OR(B2="Phrase",B2="Exact"),MID(A2,2,LEN(A2)-2),"Not Proper Match Type"))

If placed in Column D, it will result in the following:

+act naturally -> act naturally

"act naturally" -> act naturally

[act naturally] -> act naturally

Essentially, the formula is saying: If the match type is broad, then substitute the + sign for nothing, if it not broad, but is phrase or exact, then chop off the first and last character of the keyword. If it is not broad phrase or exact, display - Not Proper Match Type.

=IF(B2="Broad",SUBSTITUTE(SUBSTITUTE(A2,"'+",""),"+",""),IF(OR(B2="Phrase",B2="Exact"),MID(A2,2,LEN(A2)-2),"Not Proper Match Type"))

Have any questions? Drop a comment below.