Extract Value Across Multiple Columns

ChrisGAEG

Hello, 

I have a value that can be housed in 4 different columns. Not only that but the value is within a text string. Here is an example of the string - 

1-12: ESCC02SPL01, 1-12

I am trying to write an evaluation or batch calculation that searches the 4 columns for the value (contains 'SPL') and and populates a new column with the middle string (in between spaces) as the return. Such that the example above would evaluate to 

ESCC02SPL01

Another detail is that I need the first instance of when SPL is in the value. So if it is present in columns 3 & 4, I need to take the value that is in column 3. 

So far I am trying to put the columns in a list and then use Find to iterate over the list. I am more of a Python person so I'm not exactly sure how to do this with Arcade. What I have so far is this 

var row1 = $feature.f0;
var row2 = $feature.f0spare;
var row3 = $feature.dead
var row4 = $feature.f1

var rowlist = [row1, row2, row3, row4]

var splitter = Find('SPL', rowlist, 0)

if (splitter > 0) {
  return splitter
};

But this is returning incorrect values. 

Any help or opinions on this is greatly appreciated. Thanks for reading. 

JohannesLindner

var values = [
  $feature.f0,
  $feature.f0spare,
  $feature.dead,
  $feature.f1
  ]
for(var v in values) {
  var value = values[v]
  if(Count(value) > Count(Replace(value, "SPL", ""))) {
    value = Replace(value, ":", "")
    value = Replace(value, ",", "")
    return Split(value, " ")[1]
    // you return the first found column, the next columns won't be evaluated.
  }
}
// return a default value if no column contains a value with "SPL"
return null