CODEWITHSUNDEEP
formulanetsuitesaved-searches
Vernita
Vernita

Reputation: 103

Netsuite Saved Search formula to extract text string

For a Netsuite account, I have noticed the entityid for a customer (the customer ID on the UI) appears as an alphanumeric value using the organisation's customer number format.

However, in a saved search, it appears in the following format,'ACC12345 Parent entity name: ACC67895 Child entity name'. I am trying to use a formula to get the child entity's customer ID number from this text string.

The following formula mostly works in a saved search to extract the child entity's customer ID number (i.e. the 'ACC67895 ' in the above example

SUBSTR({entityid},INSTR({entityid},'ACC',1,2),8)

Is there a way to replace the 8 in this formula with another formula so that it extracts everything from the second 'ACC' onwards until the first blank ' '?

The number of characters of the customer ID varies so I don't want to use 8 as a hard coded value in the formula.

Thanks

Upvotes: 0

Views: 3909

Answers (1)

Pierre Plourde
Pierre Plourde

Reputation: 1042

I would suggest having your first SUBSTR return everything from the start of the child customer's ID to the end, then nest that inside a second SUBSTR which would use an INSTR to find the space after the ID and cut off everything after that:

SUBSTR(SUBSTR({entityid},INSTR({entityid},'ACC',1,2)),1,INSTR(SUBSTR({entityid},INSTR({entityid},'ACC',1,2)),' '))

Ugly, but it works.

Upvotes: 2

Related Questions