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

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