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