All Collections
Excel For NetSuite Applications
CloudExtend Excel Data Management for NetSuite
FAQ
FAQ: Using a formula to remove the bracketed internal ID of a field
FAQ: Using a formula to remove the bracketed internal ID of a field

XLNS | Building a pivot table or report off your data and don't want to see the [[nnnn]] referenced in the data?

Updated over a week ago

CloudExtend Excel Data Management for NetSuite masks the NetSuite internalID for picklist fields such as company name, item, etc. with your data by inserting a line break into a row. This means for any given row that is expanded you will also see the internal ID for certain fields expressed as [[nnnn]].

Customers that want to remove the reference to the internalID for reporting purposes can easily remove it by following the steps below.

Step 1: Load your template.

Step 2: Download your data using Reload feature.

Step 3: Insert a column anywhere in the template being sure not to add any value in Row 1 (it's fine to change the name of the column from ColumnN to something more descriptive in Row 2).

Step 4: Use a formula to look up the field and only import the characters to the left of [[.
โ€‹=LEFT(A1,FIND("[[",A1)-2) is an example formula that assumes the field you want to remove the brackets from is in cell A1.ย 

Step 5: Excel should automatically copy your formula to the bottom of the table column and your new column will have just the value for your field without the [[nnnn]].

๐Ÿ’ก Pro tip - Create/record a macro that you can run on-demand that inserts the column and then inserts the formula as well. Moving forward running the macro will be super fast. See this article for more information.

Did this answer your question?