In Excel there is no a direct function named “TEXTBETWEEN” , but we can make it by LAMBDA
TEXTBETWEEN is a custom function to extract text between delimiters.
TEXTBETWEEN = TEXTAFTER + TEXTBEFORE
Syntax
=TEXTBETWEEN(text,delimiter_1,[num_1],[case_1],[delimiter_2],[num_2],[case_2])num : the instance num
case : 0 or 1
1 : Case Sensitive
0 : Case insensitive
How Does it Work?
- select the cell
- determine your first delimiter
- specify the instance_num
- specify the case sensitivity
- then, do the same with the second delimiter
Example 1: Extract the Numbers Between “*” and “/”
=TEXTBETWEEN(D5:D7,"*",,,"/")
Example 2: Extract the Text Between “a” and “B”
=TEXTBETWEEN(D5:D7,"a",,1,"B",,1)
Example 3: Extract the Text Between the Third “a” ( ignore case ) and the second delimiter could be “-” or “b”
=TEXTBETWEEN(D5:D7,"a",3,,{"-","b"})
LAMBDA :
=LAMBDA(text,delimiter_1,[num_1],[case_1],[delimiter_2],[num_2],[case_2],
TEXTBEFORE(
TEXTAFTER(
text,
delimiter_1,
IF(num_1 = 0, 1, num_1),
IF(case_1 = 0, 1, 0)
),
delimiter_2,
IF(num_2 = 0, 1, num_2),
IF(case_2 = 0, 1, 0)
)
)
The Excel File With Lambda: