Your examples are conflicting about whether the string you want starts with
[Lit:. I have assumed that the upper-case
In LibreOffice (and presumably other Excel equivalents, though I have no idea about Apple Pages or Google Docs), the
FIND() function allows you locate a substring within a text field, but it returns an error if the substring is not found, so you need to use
IFERROR() as well.
I shall consider first the simple example where any
[LIT: field is always at the end of the string, with
] as the final character. If the data are in column
A, starting at
A1, then the following formula will do what you want:
FIND() returns a value, then the substring from that position to the end of the string is returned; otherwise,
FIND() and therefore
MID() will generate an error, and a blank string is returned.
In the more complex case, where the
[LIT: field may occur in the middle of the string, the formula must be elaborated:
In this case the substring
[LIT: to end of string is found, but the number of characters generated from the original cell is limited by the position of
] within the substring; again, any error will generate a blank string.
Whichever formula you use, you copy the cell it's in and paste it down the rest of the column. If you need to handle either
[Lit:, then replace
FIND() looks for a literal, case-sensitive match,
SEARCH() uses regular expression matching.
If you need to remove the
[LIT: substring from the original column
A, then put the extracted
[LIT: field into column
C, and put into
Now copy this down the rest of column
B and hide column
A. Of course any columns and start rows could be used; for my examples I have used adjacent columns with no header rows.
=SUBSTITUTE() doesn't generate errors, so there is no need to use