Hello,
Please can you help me urgently.
Attached macro have two modules and I need your help in module 1 "Splitdatatosheets", this module get first sheet "Template" and Split it into many sheets based on Company Name column.
As you know the sheet name should not exceed this length "31", and there are some names exceed this length, so macro can't create sheet with name.
Please can you help me to update the macro module 1 "Splitdatatosheets" as below.
1- If the company name contain Special Characters, when macro create sheets names, should remove Special Characters. "No need to remove Space"
Ex: Raychem RPG (P) Ltd. so the sheet name should be Raychem RPG P Ltd
2- After removing all Special Characters and macro found the name still exceed this length "31", macro can remove all Characters after last space.
Ex: Integrated Silicon Solutions, Inc. the length is 32 after removing Special Characters, so macro result should be Integrated Silicon Solutions
Ex: Visual Communications technology Company, LLC the length is 44 and if macro remove after last space will be Visual Communications technology Company and length is 40, so macro should remove all Characters after previous space and so on. so in this case the name should be Visual Communications
I hope you understand me. and thank you very much.
Thanks;
Marsil
Hi Marsil,
Here is a function that can clean the text and keep it under 30 chars:
Note that () are not illegal chars in a sheet name.
Function CleanName(ByVal NewName As String) As String
Dim Arr As Variant, Itm As Variant
Arr = Array("/", "", "?", "*", "[", "]")
For Each Itm In Arr
If InStr(1, NewName, Itm, vbTextCompare) > 0 Then NewName = Replace(NewName, Itm, "", , , vbTextCompare)
Next Itm
Do Until Len(NewName) <= 30
If InStrRev(NewName, " ", , vbTextCompare) = 0 Then Exit Do
NewName = Left(NewName, InStrRev(NewName, " ", , vbTextCompare) - 1)
Loop
CleanName = Left(NewName, 30) 'in case there are no more spaces and len is still over 30
End Function
Hi Catalin,
How kind you are to help me. Thank you very much.
Please can u add this code in attached macro as I face issue after I inserted the code.
Thank you very much; your support is greatly appreciated.
Thanks,
Marsil
It's a function, all you have to do is to paste it in any standard module, then call the function from your code to clean the text:
CleanedString=CleanName("The text string that contains illegal chars, longer than 30 chars")
You can even use it in excel cells, like any function.
That's a huge help - thanks!
