Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- 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:
Here is the sample of code I found but not sure how to adapt. Any help on this is appreciated!
My strings Look like:
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
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 |