Formulas and Functions
ਇਸ ਕੰਟਰੋਲ ਦੀ ਸੋਧ ਕਰਨ ਨਾਲ ਇਹ ਪੰਨਾ ਆਪਣੇ ਆਪ ਅੱਪਡੇਟ ਹੋ ਜਾਵੇਗਾ

REGEX

The REGEX function enables usage of regular expressions in other text and conditional functions. It can be used with all functions that expect a condition (IF, COUNTIF, …) or a string match (SUBSTITUTE, TEXTBEFORE, …). When not used as a condition or to match text, REGEX returns the underlying regular expression as a string value.

REGEX(regular-expression-string, case-sensitive)

regular-expression-string: A string value representing a regular expression.

case-sensitive: An optional modal value that determines whether the regular expression should be considered case-sensitive or not.

Case-sensitive (TRUE or omitted): The regular expression should be considered case-sensitive.

Ignore case (FALSE): The regular expression should not be considered case-sensitive.

Notes

  • The regular-expression-string has to conform to the ICU standard.

  • Just as it can be concatenated with regular string values, a REGEX expression can be concatenated with another REGEX. However, if a REGEX is concatenated with a regular string, the result is a regular string and is no longer a REGEX.

Examples

=COUNTMATCHES("To count all words, use regex.", REGEX("\w+")) returns 6.

=TEXTBEFORE("Get all the text before the first numbers 12345 - and nothing after.", REGEX("[0-9]+")) returns "Get all the text before the first numbers ".

Let each cell in the range A1:A10 contain a word, 5 of which contain the letter a.

=COUNTIF(A1:A10, REGEX("a+",FALSE)) returns 5.

=SUBSTITUTE("example@example.com: Marina Email", REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}") & REGEX(": *"), "") returns "Marina Email".

=SUBSTITUTE("marina@example.com", REGEX("([A-Z0-9a-z._%+-]+)@([A-Za-z0-9.-]+)(\.[A-Za-z]{2,4})"), "$2@$1$3") returns "example.com@marina.com".

=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]")) returns 2.

=COUNTMATCHES("Item1, item2, item3", REGEX("item[0-9]", FALSE)) returns 3.

Let A1 be "Client email: client@example.com".

=IF(COUNTMATCHES(A1, REGEX("[A-Z0-9a-z._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}")), "We have an email", "No email") returns "We have an email".

See alsoFINDSEARCHSUBSTITUTETEXTAFTERTEXTBEFORETEXTBETWEENCOUNTMATCHESAVERAGEIFAVERAGEIFSCOUNTIFCOUNTIFSIFIFSMINIFSMAXIFSREGEX.EXTRACTSUMIFSUMIFSMATCHXMATCHLOOKUPVLOOKUPHLOOKUPXLOOKUP
Morty Proxy This is a proxified and sanitized view of the page, visit original site.
ਕੋਈ ਮਦਦ ਮਿਲੀ?
ਅੱਖਰ ਸੀਮਾ: 250
ਵੱਧ ਤੋਂ ਵੱਧ ਅੱਖਰ ਸੀਮਾ 250 ਹੈ
ਫ਼ੀਡਬੈਕ ਲਈ ਤੁਹਾਡਾ ਧੰਨਵਾਦ।
Morty Proxy This is a proxified and sanitized view of the page, visit original site.