Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Help with Split function 1

Status
Not open for further replies.

alaasallam

IS-IT--Management
May 16, 2012
2
US
I have a field called "OrgPath" that has Path for different orgs with "\" as separator as follows:

\Org1\Org2\Org3\Org4
Some records may have 4 items (orgs) and some may have more up to 9 orgs with the "\" as separator.
\Org1\Org2\Org3\Org4\Org5\Org6\Org7\Org8\Org9
I want to split each org in a field. I created 9 formula fields (Org1 to Org9) to have each populated with the appropriate org and used the following formula in each field:
For “Org1” formula field:
Split({OrgPath},"\")[1]....This should be populated with "Org1"
For “Org2” formula field:
Split({OrgPath},"\")[2]....This should be populated with "Org2"
And so on.
the formula works fine as long as the reference to the item does not exceed the number of items in the array. However, if the subscript that references an item that is higher than the number of items in the array, I get an error msg; i.e. if I enter a subscript that references the 6th or 8th item, for example, in a five item array, I get the following error:
"You have entered a subscript that specifies an array item that does not exist"
So, if a record is showing \Org1\Org2, then formula fields “Split({OrgPath},"\")[1]” and “Split({OrgPath},"\")[2]” will be fine but “Split({OrgPath},"\")[3]” will error out.
Is there a way to fix this so that, all formula fields that are referencing items that are higher than the upper bound of the array in one record, would just show blank; if, for example, I have 2 items in the array, then the rest of the formula fields from 3 to 9 will be blank.
thanks
Al
 

The ubound function returns the number of elements in the array, so each formula will look something like this:

if ubound(Split({OrgPath},"\")) >= 2

then

Split({OrgPath},"\")[2]

else
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top