VBA Help - Shorten String to be Used as Sheet Name

[Johnny Thunder]

Hello All,
I am working on a project that has long strings that will become sheet names. I am in need of a Function that can identify the string length and modify if needed. I also have 1 or two strings that may contain special characters like "/" or "*" that can not be used in excel sheet name parameters so would need those removed before creating the new string that will be used as a title

Code Needs to:

• Remove special characters that can't be used in a sheet name
• Validate that the string is not more than 31 Characters
  • If it is longer than abbreviate the last or 2 last words to shorten length

Here is the sample of code I found but not sure how to adapt. Any help on this is appreciated!

Function Abbrev(p As String) As String
    Dim res As String, w1, w2

    res = Split(Split(p, ",")(0), " ")(0)
    If res = Split(p, ",")(0) Then res = res & "_"

    For Each w1 In Split(Mid(Replace(p, " &", ","), Len(res) + 1), ",")
        For Each w2 In Split(w1, " ")
            res = res & Left(w2, 1)
        Next w2
        res = res & "_"
    Next w1

    Abbrev = IIf(Right(res, 1) <> "_", res, Left(res, Len(res) - 1))
End Function

My strings Look like:

Daily Budget Log v2.3 - Transfer.xlsm
	M	N	O	P
1	String	Length	Proposed Outcome	New Length
2	The Orange Theory	17	The Orange Theory	17
3	A Long Conversation with Morrissey	34	A Long Conversation with Mrsy	29
4	A Bright Blue Moon	18	A Bright Blue Moon	18
5	Walking on The Beaten Path with Craig	37	Walking on The Beaten Path W C	30
6	The Catcher in The Rye	22	The Catcher in The Rye	22
7	Under Siege/ Trilogy	20	Under Siege Trilogy	19
Lookups