Excel for Google PPC: How to Break Up a Text String Into 2 Cells Based on Character Limits & Spaces

[jeffgibson55]

I'm using Google PPC to promote some live training events. I have a database full of upcoming event titles. I need to build dynamic ads using these title values as well as some other parameters. Google gives you 2 description fields to populate both with a character limit of 35 each. Many of my titles exceed 35 characters. So I am looking for a way to break up any title value across these 2 fields. The trick though is that I need to make sure I break the title up at a space not mid-word so essentially I need a formula that will find the 35th character in the string then backtrack to the nearest preceding space prior to the 35th character.

For the second field I will pick up where the first field left off but if that value also exceeds 35 characters I'll need to end it with an "..." again at a space not mid-word. So in that scenerio I'll need to allow for the "..." (3 characters) in the total 35 character limit. So when I need to use the "..." I'll only have 32 characters to play with in that field.

Any help would be most appreciated. A hypothetical title could be: "The Secrets of Leadership: How Leaders Inspire, Influence and Achieve Results". For my purposes I'd like the formula to give me the following 2 values:
*Line 1: The Secrets of Leadership: How (30 characters)
* Line 2: Leaders Inspire, Influence and... (31 characters)*

Thanks!
Jeff Gibson

 

[Gary McMaster]

Hi Jeff,

Here's something you can try with VBA. Perhaps someone else will know how to do it with a formula.

Hope it helps.

Gary

Public Sub Test()

Dim iCount As Integer
Dim vSplit As Variant
Dim sFullString As String
Dim s1stHalf As String
Dim s2ndHalf As String
Dim bLimit As Boolean

sFullString = "The Secrets of Leadership: How Leaders Inspire, Influence and Achieve Results"

vSplit = Split(sFullString, " ")

For iCount = LBound(vSplit) To UBound(vSplit)

    If Len(s1stHalf) + Len(vSplit(iCount)) >= 35 Then bLimit = True
    
    If Not bLimit Then
        s1stHalf = s1stHalf & vSplit(iCount) & " "
    Else
        If Len(s2ndHalf) + Len(vSplit(iCount)) >= 31 Then
            s2ndHalf = s2ndHalf & " ..."
            Exit For
        Else
            s2ndHalf = s2ndHalf & vSplit(iCount) & " "
            
        End If
    End If
Next iCount

MsgBox "1st half: " & s1stHalf & vbCrLf & "2nd half: " & s2ndHalf


End Sub

 

[jeffgibson55]

Gary,

Thanks for the help - I tried inserting various titles into the macro and the VB logic works really well. The problem is it's very manual to try different titles (paste a new value into the macro) and the output is in a message box so I can't copy and paste it.

I'm admittedly very weak on the VB side. Is there a way to setup your program as a custom function so I could use it within my database using a reference cell to call different titles and then have the output go into a cell with a delimiter between the 2 output values or alternatively to have the output go into 2 separate cells.

I'm not sure if that's possible or easy to do or not but basically it'd look like this where I would tell the custom function to run your VB code on a referenced cell:
Event Title | CustomStringSplitFunction | Output1 | Output2
Title 1 | =GoogleStringSplit (a2) | [vsplit 1] | [vsplit 2]
Title 2 | =GoogleStringSplit (a3) | [vsplit 1] | [vsplit 2]
Title 3 | =GoogleStringSplit (a4) | [vsplit 1] | [vsplit 2]

Thanks again for the help!

Jeff Gibson

 

[Gary McMaster]

Is there a way to setup your program as a custom function

Yes, as a UDF.

Place the following code in a standard module:

Public Function BreakMiddle(sLongString As String, iWhichHalf As Integer)

Dim iCount As Integer
Dim vSplit As Variant
Dim s1stHalf As String
Dim s2ndHalf As String
Dim bLimit As Boolean

vSplit = Split(sLongString, " ")

For iCount = LBound(vSplit) To UBound(vSplit)

    If Len(s1stHalf) + Len(vSplit(iCount)) >= 35 Then bLimit = True
    
    If Not bLimit Then
        s1stHalf = s1stHalf & vSplit(iCount) & " "
    Else
        If Len(s2ndHalf) + Len(vSplit(iCount)) >= 31 Then
            s2ndHalf = s2ndHalf & " ..."
            Exit For
        Else
            s2ndHalf = s2ndHalf & vSplit(iCount) & " "
            
        End If
    End If
Next iCount

If iWhichHalf = 1 Then
    BreakMiddle = s1stHalf
Else
    BreakMiddle = s2ndHalf
End If

End Function

Place the string you want to split in cell "A1"

In cell "B1":

=BreakMiddle(A1,1)

In cell "C1":

=BreakMiddle(A1,2)

Then fill down B1 & C1

The number 2 in cell C1 can actually be any integer other than 1 due to the "Else" clause in the function.

Gary

 

[jeffgibson55]

Works perfectly! Thank you so much!

Jeff Gibson

 

[Gary McMaster]

You're welcome. Thanks for the feedback.

Gary