<!-- https://learnsql.cpetoday.com/functions/m/text-positionof -->
# Text.PositionOf

Language: Power Query M
Category: Text
Syntax: Text.PositionOf(text as nullable text, substring as text, optional occurrence as nullable number, optional comparer as nullable function) as number
Summary: Returns the zero-based position where a piece of text first (or last) appears in a value.
Returns: The position as a number, or -1 when the text is not found.
Level: Intermediate
Related: Text.Start, Text.Middle, Text.BeforeDelimiter, Text.Contains, Text.Insert

## What it does
Text.PositionOf tells you where a piece of text sits in a value. It is the M way to find a separator before you cut a value with Text.Start, Text.Middle or Text.End. Positions start at 0, and a result of -1 means the text was not found, so the result must be checked before it is used.
## Syntax and arguments
- text: the value to search.
- substring: the text to look for.
- occurrence (optional): Occurrence.First (the default), Occurrence.Last, or Occurrence.All, which returns a list of every position.
- comparer (optional): Comparer.OrdinalIgnoreCase for a case insensitive search.
## Example
A subledger reference has a sub-account after its last dash. The position of the last dash is found so the sub-account can be cut out with Text.End.

```powerquery
let
    Reference = "1000-CASH-MAIN",
    LastDash = Text.PositionOf(Reference, "-", Occurrence.Last),
    SubAccount = Text.End(Reference, Text.Length(Reference) - LastDash - 1)
in
    {LastDash, SubAccount}
```

LastDash is 9, the position of the second dash. SubAccount is "MAIN", the text after it. The length of the end part is the total length minus the position minus one.
## Example: a second, different use
Find the position of the first "@" in an email address to check that the address has a domain part before sending a statement.
## Things to watch
- Positions are zero-based, and -1 means not found. Test for -1 before cutting a value.
- Occurrence.All returns a list, not a single number, so the result type changes.
- The default search is case sensitive.
- Null in gives null out, and a null position will fail a later step.
## In business terms
Reference codes with a separator in a known place are common in ledgers and bank files. Locating the separator and then cutting the value is how a team extracts a sub-account, a batch or a suffix without fixed positions.
