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.

How to Install VBA Macros in Excel

Many of my future posts will include macros scripting (VBA) in Excel. Below is a quick tutorial on how to set up and incorporate Macros in your Excel Document.

1. Open up Excel and save as .xlsm
   
   Select File, Save As. For "Save as type:" change it to Excel Macro-Enabled Workbook (*.xlsm) - (I tend to do this first so I don't forget later)
   
   In this example I saved it as "Fake Macro Doc.xlsm"
   
   


2. Check macro Trust Center Security Settings
   
   Select File, Options, Trust Center, Trust Center Settings (Button).
   
   Then in Macro Settings select "Disable all macros with notification" or if you are bold - "Enable all macros"
   
   In Message bar select "Show the Message Bar in all applications when active content, such as ActiveX controls and macros, has been blocked. Then hit OK twice.
   
   




3. Pull up the VBA Editor window
   
   Hit Alt+F11 or go to view, macros, edit.
   
   
4. Insert a new module
   
   With the document selected in the left window (in this case "Fake Macro Doc.xlsm") go to the top bar and select Insert, Module. This should make the new module appear in the left window. Double click it and the white blank doc is where you paste your code.
   
   Quick Video
   
   


5. Run your code
   
   On the top toolbar right under debug is the play button. Have your cursor somewhere within the code and hit play.

And that's it! If this post was helpful leave a comment!

Thanks,
Jon