Truncating Text To Full Words Based On Character Limit - Excel

Question

I'm working with some data (DataSet#1) which has a text field truncated using some unconventional logic:

• If "Service Type Description" is > 60 Characters, Trim the name down to < 60 characters, but only full words

My problem is that I need to format some other data (DataSet#2) in excel to match this logic which is being applied on the back-end on our reporting server (outside my control). No one can seem to find a list of all the potential truncated descriptions either.

Dataset#1 is live and can be re-pulled with updated data at any time, so I need to create a template that allows me to pull in information from the list in DataSet#2 (which currently has the full length descriptions) into any copy of Dataset#1 based on the trimmed Service Type Description in DataSet#1.

---

Example: The following is the full product name, and the product name in my DataSet#2:

• "FNMA 1025 Small Residential Income Property Appraisal & FNMA 216 Addendum" (73 characters, including spaces)

Simply trimming this text to be <60 characters (59) would yield:

• "FNMA 1025 Small Residential Income Property Appraisal & FNM"

However, this same product, in the main data (DataSet#1) is named as follows:

• "FNMA 1025 Small Residential Income Property Appraisal & " (56 characters, 8 "words", including &)

The logic on the back-end for DataSet#1 has trimmed the full product name to under 60 characters, but retains only full words (removes the "FNM" partial word).

---

Ideally I have to be able to take a list that has the full description name - and apply logic in Excel (or VBA) that will yield the same result as the trimmed data from the other dataset - which then allows me to pull information from dataset #2 (full product names) into dataset#1 based on the service type description.

Answer 1

You could use something like so

Function truncate_string(strInput As String, Optional lngChars As Long = 60)

Dim lngCharInstance As Long


lngCharInstance = Len(strInput)

While lngCharInstance > lngChars 
   lngCharInstance = InStrRev(strInput, " ", _
                    IIf(lngCharInstance >= Len(strInput), _
                    Len(strInput), lngCharInstance - 1))
Wend

truncate_string = Mid(strInput, 1, lngCharInstance)

End Function

This would be called like so

truncate_string("FNMA 1025 Small Residential Income Property Appraisal & FNMA 216 Addendum")

and would return as follows

FNMA 1025 Small Residential Income Property Appraisal &

or like so for, 30 chars for example

truncate_string("FNMA 1025 Small Residential Income Property Appraisal & FNMA 216 Addendum",30)

which gives

FNMA 1025 Small Residential

Hope this helps, as there is a loop in there, i'd look at the possibilities of any potential for infinite loops.

Answer 2

You can use Regular Expressions for this.

Option Explicit
Function trimLength(S As String, Optional Length As Long = 60) As String
    Dim RE As Object, MC As Object
    Dim sPat As String

sPat = "^.{1," & Length - 1 & "}(?=\s|$)"

If Len(S) > 60 Then
    Set RE = CreateObject("vbscript.regexp")
    With RE
        .Pattern = sPat
        .MultiLine = True
        Set MC = .Execute(S)
            trimLength = MC(0)
    End With
Else
    trimLength = S
End If

End Function

Note that, in accord with your question, we subtract one from the desired length.

Explanation of the Regex

Trim Length to Whole Word

^.{1,59}(?=\s|$)

Options: ^$ match at line breaks

• Assert position at the beginning of a line ^
• Match any single character that is NOT a line break character .{1,59}
  • Between one and 59 times, as many times as possible, giving back as needed (greedy) {1,59}
• Assert that the regex below can be matched starting at this position (positive lookahead) (?=\s|$)
  • Match this alternative \s
    • Match a single character that is a “whitespace character” \s
  • Or match this alternative $
    • Assert position at the end of a line $

Created with RegexBuddy