How to Manipulate Sub-strings from an Excel-Imported Dataset

Questions & Answers
1

Hi guys,

I am working on generating reports using the CSV import method.

One of the columns in my CSV file contains strings for each row, as shown below:

1

I need to create a calculated member formula to filter and display only the data where the strings contain the substring “ABC.”

How can I define a calculated member formula to extract and filter rows based on the presence of “ABC” in the dataset?

Thank you in advance.

2

Hi Kyung,
To filter rows containing “ABC”, you add a calculated column using JavaScript. Here’s how:

  1. During data mapping, click Add calculated column
  2. Add JavaScript code like this:
// If your column name contains spaces, use: doc["Your Column Name"]
if (doc.your_column_name && doc.your_column_name.includes("ABC")) {
  return doc.your_column_name;  // Returns original value if contains "ABC"
} else {
  return "";  // Returns empty if no "ABC"
}

For example, if your column is named “Part Number”, the code would be:

if (doc["Part Number"] && doc["Part Number"].includes("ABC")) {
  return doc["Part Number"];
} else {
  return "";
}

This will create a new column that:

  • Contains the original value when “ABC” is present
  • Is empty when “ABC” is not present

See Data mapping documentation

Let me know if you need any clarification!

Best regards,
Gerda // support@eazybi.com

3

@gerda.grantina Thank you so much!

I now have an additional question, it will be appreciated if you can provide a solution for this as well.

Now let’s say I have the following dataset:
100

Using JavaScript, is there a way to display only the data where the first character from the third sub-string is 3?

For example,
463-ABC-356
735-ABC-386
573-ABC-496
386-ABC-467
866-ABC-489
985-ABC-578

I want to display only the data has 3 in the first position in the third sub-string : 463-ABC-356 and 735-ABC-386.

Thank you in advance!

4

Hello @Kyung_Park ,
You can use JavaScript code to modify the values as you need.
You can try this code to return values if the third sub-string starts with ‘3’:

// If your column name contains spaces, use: doc["Your Column Name"]
if (doc.your_column_name) {
  let value = doc.your_column_name;
  // Check if value contains "ABC" and third part starts with "3"
  if (value.includes("ABC")) {
    let parts = value.split("-");
    if (parts.length >= 3 && parts[2].charAt(0) === "3") {
      return value;  // Returns original value if both conditions are met
    }
  }
}
return "";  // Returns empty if conditions are not met

Kindly,
Gerda

1 Like
5

@gerda.grantina
Thank you so much!!

1 Like