Search code examples
formulanetsuitesaved-searches

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

Solution

  • 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.