Truncating Text To Full Words Based On Character Limit - Excel

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.

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.