NetSuite Tips & Tricks: Removing/Adding spaces to a string using REPLACE and SUBSTR

Today we have another guest post from one of our Senior Consultants, Casey Andrews.  If Casey’s name looks familiar to you, there is good reason for that! Casey is one of our top NetSuite Tips & Tricks contributors and has a ton of useful NetSuite support advice to share.  Today’s topic: data field manipulation.

If you're a small or medium-sized company, you know that managing data integrity can be a challenge. If you're using NetSuite, you've probably encountered situations where formatting issues create a ripple effect and cause confusion. REPLACE and SUBSTR functions can handle these types of challenges. When done right, they can help you correct formatting inconsistencies, simplify your data entry process and maintain consistent and reliable records. 

REPLACE and SUBSTR Definition

When working with data in NetSuite, managing text fields is often an overlooked yet vital task. If you're managing multiple records, each file must follow a consistent format to maintain data integrity. REPLACE and SUBSTR help you maintain this integrity. These functions help businesses clean, standardize and manipulate strings of text in the following ways:

REPLACE: The REPLACE function replaces a sequence of characters in a string with another set of characters. For example -

REPLACE('123123TVG', '123');
Returned Result: 'TVG'

SUBSTR: This NetSuite substring formula allows you to extract a substring from a string. For example - 
SUBSTR('123123TVG', 7 (the starting position in the string), 3 (the number of characters that you would like to extract));
Returned Result: 'TVG'

Using these two functions in conjunction with one another allows you to replace, and then rebuild a desired string how you would like it.

REPLACE and SUBSTR Use Case:

We have a client who wanted to ensure that the UK Postal Codes being entered in the system adhered to a specific format. Some of the data had the correct format, but some did not. In this case, we needed to ensure that there was a space before the last three digits of the postal code. Sample data looked like this:

As you can see, some of the data has the space before the last three digits, but other data does not. We can't assume that the field has the correct placement of the space, so we will need to use REPLACE to remove it altogether. We also need to account for the different lengths of the strings to ensure that we are inserting the space in the correct place. We will do this with the following formula: 

First we will remove the space:

REPLACE({zipcode}, ' ');

Once we ensure that the function delivers the desired result, we need to get the length of the string to find out where to insert the space using something like this -

LENGTH(REPLACE({zipcode}, ' '));

Now we have the information we need to insert the new space. The string has a Length of 5, so we know that we will need to insert the space between the second and third characters of the string. We can accomplish this by using the NetSuite SUBSTR function that I mentioned earlier and using the double pipes to concatenate a space between the two parts of the string. It should look like this: 

CASE WHEN LENGTH(REPLACE({zipcode}, ' ')) = '5' THEN SUBSTR(REPLACE({zipcode}, ' '),1,2) || ' ' || SUBSTR(REPLACE({zipcode}, ' '),3,3) END

Using the five-character example above, the first SUBSTR will return 'W7' and the second SUBSTR will return '3H6'. By concatenating the two, we get the desired result, which is 'W7 3H6'. This allows us to achieve consistent and clean data formatting. 

This example focuses on postal codes, but the versatility of REPLACE and SUBSTR makes them valuable tools for the following scenarios:

• Standardize phone number formats by removing or inserting dashes, spaces or country codes. 
• Remove spaces from strings to clean customer names or correct capitalization issues. 
• Provide uniformity in how product identifiers are formatted on product codes or SKU management.

REPLACE and SUBSTR Formula

Once you understand the potential of REPLACE and SUBSTR, the next step is mastering the formula. Whether you're a NetSuite administrator, a data manager or an operations lead, this formula is a game-changer. Anyone responsible for managing data consistency will appreciate how these functions automate tedious tasks. 

Here's the full formula for your reference: 

case when LENGTH (REPLACE ({shipzip}, ‘ ’)) = ‘5’ then SUBSTR(REPLACE({shipzip}, ‘ ’),1,2) | | ‘ ’ | | SUBSTR (REPLACE({shipzip}, ‘ ’),3,3) when LENGTH (REPLACE({shipzip}, ‘ ’))= ‘6’ then SUBSTR(REPLACE({shipzip}, ‘ ’),1,3) | | ‘ ’ | | SUBSTR(REPLACE({shipzip}, ‘ ’),4,3) when LENGTH(REPLACE({shipzip}, ‘ ’)) = ‘7’ then SUBSTR(REPLACE({shipzip}, ‘ ’),1,4) || ‘ ’|| SUBSTR(REPLACE({shipzipzip}, ‘ ’),5,3) end

Additional Useful Formula Functions in NetSuite

NetSuite's formula functions provide powerful tools to manipulate and analyze data within your system. REPLACE and SUBSTR are versatile, but they're just the tip of the iceberg. Here are other NetSuite formula functions to simplify your workflow, with brief examples of their usage: 

• TO_DATE: TO_DATE converts strings into data values. This function is useful when for integrations or reports requiring uniform date formats. For example, TO_DATE(20250101, ‘YYYYMMDD’).
• TO_NUMBER: This function converts text fields into numeric values to allow arithmetic operations on data stored as text. For instance, TO_NUMBER ('125.00, L7H878D85').
• NVL: NVL replaces null or empty values with a default value so formulas don't break when encountering missing data. An example of this function in action would be NVL (phone), ('Not Applicable').
• INSTR: INSTR locates the position of a substring within a string. It is useful for identifying patterns or breaking down strings. For instance, INSTR ('email', '@').
• CONCAT: CONCAT merges multiple strings into one, which makes it easier to create unique identifiers or customized text fields. For example, CONCAT ((first_name), (last_name)).
• TRIM: This function is used to trim characters in NetSuite. It removes leading and trailing spaces from text fields for clean and consistent data. An example of its use would be TRIM (LEADING '0' FROM ‘000452’).
• UPPER/LOWER: UPPER converts text to uppercase, while LOWER converts text to lowercase. These functions help you maintain uniformity in fields like email addresses or product codes. For instance, UPPER (first_name) “Uppercase”.

Contact The Vested Group to Implement NetSuite Into Your Operations

The Vested Group has over 11 years of experience helping companies maximize NetSuite's full potential. Our team of experts specializes in implementing the software suite and tailoring it to meet your business's needs. We create solutions that work seamlessly with your processes to ensure data consistency and reporting. We can also equip your team with knowledge of NetSuite's capabilities and provide support to keep your system running smoothly. Contact us today to get started.

If you are reading Casey's post and thinking, "I wish I had that guy on my team..." - consider it done! Working with The Vested Group's NetSuite Support practices is a snap. Click below to contact us today and we can get the ball rolling immediately.