Formulas for extracting data from strings

Use the following formulas to:

Formula patterns

To extract USE
The length of a string $StrLen("string")$
The position of a specified string or substring (case-sensitive) $StrPos(where-to-search, what-to-search-for, idx)$
The position of a specified string or substring (not case-sensitive) $StrPosNoCase(where-to-search, what-to-search-for, idx)$
A substring $SubStrIdx(where-to-search, start-pos[, end-pos])$
A specified number of characters $SubStrLen((where-to-search, start-pos[, length])$
  1. The parameters
  2. The StrPos formula is case-sensitive.
  3. where-to-search the string in which to search — for example, "EngView Systems". Alternatively, you can use a formula that extracts a string, for example, #OneUp1.Material.Name.
  4. what-to-search-for a character or a substring to search for.
  5. idx indicates the index of the searched-for character or substring in the string*. See the IDX row in the table below.
  6. SubStrIdx returns the substring that starts from a specified position (start-pos**) and ends before an end position (end-pos**). If the end position is omitted, the formula returns all the string characters from the start position until the end of the string. The positions can be extracted with the StrPos or StrPosNoCase formulas.
  7. SubStrLen returns a substring that (1) starts at a specified position and (2) has as many characters as is the specified number. The position can be extracted with the StrPos or StrPosNoCase formulas.
  1. * Zero-based counting is applied: the first item is counted as 0, the second as 1, and so on. For example, in the string W-12xy EB-WB-00, the index of the first "-" is 0, the index of the second "-" is 1 and the index of the third "-" is 2. The following rules apply:
    1. You can omit the starting idx. In this case, the formula returns the first occurence of the searched-for item from left to right.
    2. If idx is a positive number or zero, the formula returns the respective position of the searched-for item from left to right.
    3. If idx is a negative number, the formula returns the position of the searched-for hen the search is from right to left.
  1. ** Zero-based counting is applied: the position of the first item is 0, the position of the second item is 1, and so on. For example, if you want to extract a substring between the first and second "–" from string W–12xy EB–WB-00, start-pos=2 and end-pos=9.

Examples

The n0 identifier removes the result's fractional part.

Characters' indices and positions in the string W–12xy EB–WB–00:

  W – 1 2 x y   E B – W B – 0 0
Idx 0 0 0 0 0 0 0 0 0 1 1 1 2 0 1
Pos 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14
To print Use Example Notes
The number of string characters in a string, spaces included $StrLen(#OneUp1.Material.Name), n0$ 15  
The position of the first dash $StrPos(#OneUp1.Material.Name, "–", 0), n0$ 1  
The position of the second dash $StrPos(#OneUp1.Material.Name, "–", 1), n0$ 9  
The position of the third dash $StrPos(#OneUp1.Material.Name, "–", 2), n0$ 12  
The position of the fourth dash $StrPos(#OneUp1.Material.Name, "–", 3), n0$ –1 There is no fourth dash in the string.
The position of the first dash from right to left $StrPos(#OneUp1.Material.Name, "–", –1), n0$ 12  
The position of the substring "EB" $StrPos(#OneUp1.Material.Name, "EB"), n0$ 7  
The position of the substring "eb" $StrPos(#OneUp1.Material.Name, "eb"), n0$ -1 Because case-sensitivity is respected, "eb" has not been found.
The position of the substring "EB" $StrPosNoCase(#OneUp1.Material.Name, "EB"), n0$ 7 The position of "EB". No case-sensitivity.
The position of the substring "eb" $StrPosNoCase(#OneUp1.Material.Name, "eb"), n0$ 7 The position of "EB". No case-sensitivity.
The substring between two specified character positions, start position included, end position excluded $SubStrIdx(#OneUp1.Material.Name, strpos(#OneUp1.Material.Name, "–", 0)+1, strpos(#OneUp1.Material.Name, "–", 1))$ 12xy EB The substring between the first and second specified dashes
Four characters from a specified position $SubStrLen(#OneUp1.Material.Name, strpos(#OneUp1.Material.Name, "–", 0)+1, 4)$ 12xy The four symbols after the specified dash