TEXTAFTER Function

Description

Returns text that occurs after a given character or string.

NOTES

• By default, TEXTAFTER 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!=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

EXAMPLE-1

Copied!//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦_𝘯𝘶𝘮=[>0,<0], 𝘮𝘢𝘵𝘤𝘩_𝘮𝘰𝘥𝘦=[0,1], 𝘮𝘢𝘵𝘤𝘩_𝘦𝘯𝘥=[0,1], 𝘪𝘧_𝘯𝘰𝘵_𝘧𝘰𝘶𝘯𝘥=[#𝘕𝘈] 

𝘈1="𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 {year}"
------------------------------------------------------------
//𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘣𝘦𝘨𝘪𝘯, 𝘳𝘦𝘵𝘶𝘳𝘯: {year}
=TEXTAFTER(A1, " ", 2)

//𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘦𝘯𝘥, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘌𝘹𝘤𝘦𝘭 {year}
=TEXTAFTER(A1, " ", -2)

//𝘶𝘴𝘦 𝘦𝘮𝘱𝘵𝘺 𝘴𝘵𝘳𝘪𝘯𝘨 𝘢𝘴 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 {year}
=TEXTAFTER(A1, "", --1)

//𝘪𝘧 𝘯𝘰 𝘮𝘢𝘵𝘤𝘩 𝘪𝘴 𝘧𝘰𝘶𝘯𝘥, 𝘵𝘢𝘬𝘦 𝘪𝘵 𝘣𝘢𝘤𝘬, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 {year}
=TEXTAFTER(A1, " ", 4, , , A1)

//𝘵𝘳𝘦𝘢𝘵𝘴 𝘵𝘩𝘦 𝘦𝘯𝘥 𝘰𝘧 𝘵𝘦𝘹𝘵 𝘢𝘴 𝘢 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 {year}
=TEXTAFTER(A1, " ", -3, , 1)

//𝘢𝘭𝘭𝘰𝘸 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘴𝘦𝘱𝘢𝘳𝘢𝘵𝘰𝘳 𝘱𝘰𝘴𝘴𝘪𝘣𝘪𝘭𝘪𝘵𝘪𝘦𝘴 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 {year}
=TEXTAFTER(A1, {" ", "o"})
//[𝘰𝘱𝘵𝘪𝘰𝘯𝘢𝘭]: 𝘪𝘯𝘴𝘵𝘢𝘯𝘤𝘦_𝘯𝘶𝘮=[>0,<0], 𝘮𝘢𝘵𝘤𝘩_𝘮𝘰𝘥𝘦=[0,1], 𝘮𝘢𝘵𝘤𝘩_𝘦𝘯𝘥=[0,1], 𝘪𝘧_𝘯𝘰𝘵_𝘧𝘰𝘶𝘯𝘥=[#𝘕𝘈] 

𝘈1="𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2026"
------------------------------------------------------------
//𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘣𝘦𝘨𝘪𝘯, 𝘳𝘦𝘵𝘶𝘳𝘯: 2026
=TEXTAFTER(A1, " ", 2)

//𝘴𝘦𝘢𝘳𝘤𝘩𝘪𝘯𝘨 space ' ' 𝘧𝘳𝘰𝘮 𝘵𝘩𝘦 𝘦𝘯𝘥, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘌𝘹𝘤𝘦𝘭 2026
=TEXTAFTER(A1, " ", -2)

//𝘶𝘴𝘦 𝘦𝘮𝘱𝘵𝘺 𝘴𝘵𝘳𝘪𝘯𝘨 𝘢𝘴 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2026
=TEXTAFTER(A1, "", --1)

//𝘪𝘧 𝘯𝘰 𝘮𝘢𝘵𝘤𝘩 𝘪𝘴 𝘧𝘰𝘶𝘯𝘥, 𝘵𝘢𝘬𝘦 𝘪𝘵 𝘣𝘢𝘤𝘬, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2026
=TEXTAFTER(A1, " ", 4, , , A1)

//𝘵𝘳𝘦𝘢𝘵𝘴 𝘵𝘩𝘦 𝘦𝘯𝘥 𝘰𝘧 𝘵𝘦𝘹𝘵 𝘢𝘴 𝘢 𝘥𝘦𝘭𝘪𝘮𝘪𝘵𝘦𝘳, 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘔𝘪𝘤𝘳𝘰𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2026
=TEXTAFTER(A1, " ", -3, , 1)

//𝘢𝘭𝘭𝘰𝘸 𝘮𝘶𝘭𝘵𝘪𝘱𝘭𝘦 𝘴𝘦𝘱𝘢𝘳𝘢𝘵𝘰𝘳 𝘱𝘰𝘴𝘴𝘪𝘣𝘪𝘭𝘪𝘵𝘪𝘦𝘴 𝘳𝘦𝘵𝘶𝘳𝘯: 𝘴𝘰𝘧𝘵 𝘌𝘹𝘤𝘦𝘭 2026
=TEXTAFTER(A1, {" ", "o"})

SAMPLE-2

EXAMPLE-2.1

Copied!//𝘴𝘩𝘰𝘳𝘵𝘦𝘯 𝘵𝘩𝘦 𝘩𝘦𝘢𝘥𝘦𝘳 𝘰𝘧 𝘦𝘢𝘤𝘩 𝘤𝘰𝘭𝘶𝘮𝘯
=APPLY.λ(A3#, 1, , LAMBDA(a, TEXTAFTER(a, ".", -1, , 1)))
//𝘴𝘩𝘰𝘳𝘵𝘦𝘯 𝘵𝘩𝘦 𝘩𝘦𝘢𝘥𝘦𝘳 𝘰𝘧 𝘦𝘢𝘤𝘩 𝘤𝘰𝘭𝘶𝘮𝘯
=APPLY.λ(A3#, 1, , LAMBDA(a, TEXTAFTER(a, ".", -1, , 1)))

RELATED POST