Skip to main content
Support is Offline
Today is our off day. We are taking some rest and will come back stronger tomorrow
Official support hours
Monday To Friday
From 09:00 To 17:30
  Saturday, 28 August 2021
  2 Replies
  5.2K Visits
0
Votes
Undo
Hi there,
Brilliant function to split data to new worksheets. 
Sometimes inconsistent naming of the sheets when selecting "Values of Column" as  new worksheet name. Reverts to "Sheet 1" etc. 
What is the rule that determines this?
Are there any workarounds so that the split data is named the value of the column each time?
Many thanks
Naomi
 
2 years ago
·
#2284
0
Votes
Undo
Hello,
Thanks for the feedback.
Good thinking, we will consider enhancing it in the upcoming versions.
1 year ago
·
#3211
0
Votes
Undo
Hey Naomi, I noticed the function will use "Sheet #" if the value does not meet the naming rules within Excel. I was trying to use company names and these often violated at least 1 of these rules. I started creating a new column that was a 'safe' version of the company name and this behavior went away. Official rules from Microsoft

  • Be blank.
  • Contain more than 31 characters.
  • Contain any of the following characters: / \ ? * : [ ]
  • Begin or end with an apostrophe ('), but they can be used in between text or numbers in a name.
  • Be named "History". This is a reserved word Excel uses internally.


As an example this account name is too long: IBM (International Business Machines). This is a standard naming scheme for our customers. Abbreviated name with expanded information within parentheses. In our case we created a new (hidden) column for the 'Account Name Safe'. Cleaning the account can be done in a lot of ways. In our case the following did what we needed:

Easiest way to resolve is to crop at 30 characters
=LEFT(A2,30)
IBM (International Business Machines) becomes IBM (International Business M

As a more elaborate solution was to crop if a coma or parentheses was seen
=MIN(30,IFERROR(FIND("(",A2-1,100),IFERROR(FIND(",",A2)-1,100))
IBM (International Business Machines) becomes IBM
  • Page :
  • 1
There are no replies made for this post yet.