VBA Help - Shorten String to be Used as Sheet Name

Johnny Thunder

Well-known Member
Joined
Apr 9, 2010
Messages
677
Office Version
  1. 2016
Platform
  1. MacOS
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!

VBA Code:
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

enter image description here


My strings Look like:
Daily Budget Log v2.3 - Transfer.xlsm
MNOP
1StringLengthProposed OutcomeNew Length
2The Orange Theory17The Orange Theory17
3A Long Conversation with Morrissey34A Long Conversation with Mrsy29
4A Bright Blue Moon18A Bright Blue Moon18
5Walking on The Beaten Path with Craig37Walking on The Beaten Path W C30
6The Catcher in The Rye22The Catcher in The Rye22
7Under Siege/ Trilogy20Under Siege Trilogy19
Lookups
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Watch MrExcel Video

Forum statistics

Threads
1,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top