Description
Returns text that occurs before a given character or string.
NOTE
- By default, TEXTBEFORE is case sensitive and does not support wildcards.
- Common error:
– If instance_num greater than the length of text, the function returns the #VALUE error.
– If the text input does not contain a delimiter, or [instance_num] is greater than the number of occurrences of delimiter, the function returns the #NA error.
Syntax
Copied!=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
EXAMPLE
Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦_𝘯𝘶𝘮=[>0,<0], 𝘮𝘢𝘵𝘤𝘩_𝘮𝘰𝘥𝘦=[0,1], 𝘮𝘢𝘵𝘤𝘩_𝘦𝘯𝘥=[0,1], 𝘪𝘧_𝘯𝘰𝘵_𝘧𝘰𝘶𝘯𝘥=[#𝘕𝘈] 𝘈1="𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2024" ------------------------------------------------------------ //𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘣𝘦𝘨𝘪𝘯, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 =TEXTBEFORE(A1, " ", 2) //𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘦𝘯𝘥, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 =TEXTBEFORE(A1, " ", -2) //𝘶𝘴𝘦 𝘦𝘮𝘱𝘵𝘺 𝘴𝘵𝘳𝘪𝘯𝘨 𝘢𝘴 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2024 =TEXTBEFORE(A1, "", -1) //𝘪𝘧 𝘯𝘰 𝘮𝘢𝘵𝘤𝘩 𝘪𝘴 𝘧𝘰𝘶𝘯𝘥, 𝘵𝘢𝘬𝘦 𝘪𝘵 𝘣𝘢𝘤𝘬, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2024 =TEXTBEFORE(A1, " ", 4, , , A1) //𝘵𝘳𝘦𝘢𝘵𝘴 𝘵𝘩𝘦 𝘦𝘯𝘥 𝘰𝘧 𝘵𝘦𝘹𝘵 𝘢𝘴 𝘢 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2024 =TEXTBEFORE(A1, " ", 3, , 1) //𝘢𝘭𝘭𝘰𝘸 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘴𝘦𝘱𝘢𝘳𝘢𝘵𝘰𝘳 𝘱𝘰𝘴𝘴𝘪𝘣𝘪𝘭𝘪𝘵𝘪𝘦𝘴 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳 =TEXTBEFORE(A1, {" ", "o"})