{"text_format":"1","workbook":"xlsx","n_sheets":10,"sheets":["Regex Match - Examples","Match string","Match number","Match phone number","Not match character","Not match string","Case-insensitie match","Match valid email address","Excel IF Match regex","Count regex match"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Regex Match - Examples","index":0,"n_rows":23,"n_cols":7,"rows_kept":23,"cut":false,"hidden":false,"merged":[[19,1,19,2],[16,1,16,2],[17,1,17,2],[18,1,18,2],[13,1,13,2],[14,1,14,2],[15,1,15,2],[3,1,3,2],[5,1,5,2],[8,2,8,3],[11,1,11,2],[12,1,12,2]]}
[]
[]
[]
[null,"Sample Workbook to Regex Match in Excel"]
[]
[null,"The workbook shows how to use regular expressions to match strings in Excel."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-09-29"]
[null,"Tutorial URL","How to use Regex Match in Excel"]
[]
[null,"Examples:"]
["• ","Regex to match strings "]
["• ","Regex to match numbers"]
["• ","Regex to match phone numbers "]
["• ","Regext to NOT match character"]
["• ","Regext to NOT match string"]
["• ","Regex for case insensitive matching "]
["• ","Regex to match valid email addresses "]
["• ","Excel IF Match regex "]
["• ","Excel COUNTIF regex "]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Match string","index":1,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\b[A-Z]{2}-\\d{3}\\b"]
[]
["String","SKU available"]
["Chain frame CH-110 I/O 1034567 $10.99",{"$f":"RegExpMatch(A5, $A$2)","v":true}]
["20x Leaf puzzle LF-020 I/O 1034567 $7.49",{"$f":"RegExpMatch(A6, $A$2)","v":true}]
["Metal shelf $49.99  I/O 1033129 23-MAR-2022",{"$f":"RegExpMatch(A7, $A$2)","v":false}]
["GL 100 Golder Units, I/O 1034567, $25.50",{"$f":"RegExpMatch(A8, $A$2)","v":false}]
["Case sandpaper, I/O 1033877, CS-190, $15.00",{"$f":"RegExpMatch(A9, $A$2)","v":true}]

{"sheet":"Match number","index":2,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
[null,"Pattern","Pattern"]
[null,"\\d+","\\b\\d{7}\\b"]
[]
["String","Any number","Invoice no."]
["Chain frame CH-110 I/O 1034567 $10.99",{"$f":"RegExpMatch(A5, $B$2)","v":true},{"$f":"RegExpMatch(A5, $C$2)","v":true}]
["20x Leaf puzzle  $7.49",{"$f":"RegExpMatch(A6, $B$2)","v":true},{"$f":"RegExpMatch(A6, $C$2)","v":false}]
["Metal shelf - N/A",{"$f":"RegExpMatch(A7, $B$2)","v":false},{"$f":"RegExpMatch(A7, $C$2)","v":false}]
["GL 100 Golder Units I/O 1034567 $25.50",{"$f":"RegExpMatch(A8, $B$2)","v":true},{"$f":"RegExpMatch(A8, $C$2)","v":true}]
["Case sandpaper I/O 1033877 CS-190",{"$f":"RegExpMatch(A9, $B$2)","v":true},{"$f":"RegExpMatch(A9, $C$2)","v":true}]

{"sheet":"Match phone number","index":3,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["(\\(\\d{3}\\)|\\d{3})[-\\.\\s]?\\d{3}[-\\.\\s]?\\d{4}\\b"]
[]
["Phone number","Phone number"]
["Chain frame CH-110 I/O 1034567 $10.99\n+1 123.237.4567 ",{"$f":"RegExpMatch(A5, $A$2)","v":true}]
["20x Leaf puzzle  $7.49\n(541) 754-3010",{"$f":"RegExpMatch(A6, $A$2)","v":true}]
["Metal shelf $49.99\n(123).345.4567 ",{"$f":"RegExpMatch(A7, $A$2)","v":true}]
["GL 100 Golder Units tel: 1234567899\n I/O 1034567 $25.50",{"$f":"RegExpMatch(A8, $A$2)","v":true}]
["Case sandpaper\n I/O 1033877, CS-190",{"$f":"RegExpMatch(A9, $A$2)","v":false}]

{"sheet":"Not match character","index":4,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^[^\\+]*$"]
[]
["Phone number","Country code missing"]
["+1 123.237.4567 ",{"$f":"RegExpMatch(A5, $A$2)","v":false}]
["\n(541) 754-3010",{"$f":"RegExpMatch(A6, $A$2)","v":true}]
["\n(123).345.4567 ",{"$f":"RegExpMatch(A7, $A$2)","v":true}]
["+44 7911 123456\n",{"$f":"RegExpMatch(A8, $A$2)","v":true}]
["+375 (232) 311289",{"$f":"RegExpMatch(A9, $A$2)","v":false}]

{"sheet":"Not match string","index":5,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^((?!lemons).)*$"]
[]
["String","The are NO \"lemons\""]
["2 apples and 3 oranges",{"$f":"RegExpMatch(A5, $A$2,FALSE)","v":true}]
["Sold: 20 oranges, 30 lemons,  10 apples",{"$f":"RegExpMatch(A6, $A$2,FALSE)","v":false}]
["Grapes: qty. 10, price $9.99",{"$f":"RegExpMatch(A7, $A$2,FALSE)","v":true}]
["Lemons: in stock 300, ordered 100",{"$f":"RegExpMatch(A8, $A$2,FALSE)","v":false}]
["pears $50.49, oranges $100.00, lemons",{"$f":"RegExpMatch(A9, $A$2,FALSE)","v":false}]

{"sheet":"Case-insensitie match","index":6,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[[1,0,1,3],[0,0,0,3]]}
["Pattern"]
["\\b\\d{1,2}-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)-(\\d{4}|\\d{2})\\b"]
[]
["String","Date"]
["Chain frame CH-110 I/O 1034567 $10.99\n5-May-22",{"$f":"RegExpMatch(A5, $A$2, FALSE)","v":true}]
["20x Leaf puzzle  $7.49\nShips on 23-MAR-2022",{"$f":"RegExpMatch(A6, $A$2, FALSE)","v":true}]
["Metal shelf $49.99 01-XYZ-22\n(123).345.4567 ",{"$f":"RegExpMatch(A7, $A$2, FALSE)","v":false}]
["GL 100 Golder Units, shipped on 04-dec-2021 \nI/O 1034567 $25.50",{"$f":"RegExpMatch(A8, $A$2, FALSE)","v":true}]
["Case sandpaper\n I/O 1033877, CS-190",{"$f":"RegExpMatch(A9, $A$2, FALSE)","v":false}]

{"sheet":"Match valid email address","index":7,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,1],[1,0,1,1]]}
["Pattern"]
["\\b[\\w\\.\\-]+@[A-Za-z0-9]+[A-Za-z0-9\\.\\-]*[A-Za-z0-9]+\\.[A-Za-z]{2,24}\\b"]
[]
["String","Valid email address"]
["Chain frame CH-110 I/O 1034567 $10.99\nsales@chain-frame.com",{"$f":"RegExpMatch(A5, $A$2)","v":true}]
["20x Leaf puzzle  john_d@.company.net\n(541) 754-3010",{"$f":"RegExpMatch(A6, $A$2)","v":false}]
["Metal shelf $49.99\njohn_d@company.net",{"$f":"RegExpMatch(A7, $A$2)","v":true}]
["GL 100 Golder Units sales@s_top.com\n I/O 1034567 $25.50",{"$f":"RegExpMatch(A8, $A$2)","v":false}]
["Case sandpaper 123@outlook.com\n I/O 1033877, CS-190",{"$f":"RegExpMatch(A9, $A$2)","v":true}]

{"sheet":"Excel IF Match regex","index":8,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[[0,0,0,1],[1,0,1,1]]}
["Pattern"]
["\\b[\\w\\.\\-]+@[A-Za-z0-9]+[A-Za-z0-9\\.\\-]?[A-Za-z0-9]+\\.[A-Za-z]{2,24}\\b"]
[]
["String","Valid email address"]
["Chain frame CH-110 I/O 1034567 $10.99\nsales@chain-frame.com",{"$f":"IF(RegExpMatch(A5, $A$2,), \"Yes\", \"No\")","v":"Yes"}]
["20x Leaf puzzle  john_d@.company.net\n(541) 754-3010",{"$f":"IF(RegExpMatch(A6, $A$2,), \"Yes\", \"No\")","v":"No"}]
["Metal shelf $49.99\njohn_d@company.net",{"$f":"IF(RegExpMatch(A7, $A$2,), \"Yes\", \"No\")","v":"Yes"}]
["GL 100 Golder Units sales@s_top.com\n I/O 1034567 $25.50",{"$f":"IF(RegExpMatch(A8, $A$2,), \"Yes\", \"No\")","v":"No"}]
["Case sandpaper 123@outlook.com\n I/O 1033877, CS-190",{"$f":"IF(RegExpMatch(A9, $A$2,), \"Yes\", \"No\")","v":"Yes"}]

{"sheet":"Count regex match","index":9,"n_rows":11,"n_cols":6,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["(\\(\\d{3}\\)|\\d{3})[-\\.\\s]?\\d{3}[-\\.\\s]?\\d{4}\\b"]
[]
["String","Phone number",null,null,"String"]
["Chain frame CH-110 I/O 1034567 $10.99\n+1 123.237.4567 ",{"$f":"RegExpMatch(A5, $A$2)","v":true},null,null,"Chain frame CH-110 I/O 1034567 $10.99\n+1 123.237.4567 "]
["20x Leaf puzzle  $7.49\n(541) 754-3010",{"$f":"RegExpMatch(A6, $A$2)","v":true},null,null,"20x Leaf puzzle  $7.49\n(541) 754-3010"]
["Metal shelf $49.99\n(123).345.4567 ",{"$f":"RegExpMatch(A7, $A$2)","v":true},null,null,"Metal shelf $49.99\n(123).345.4567 "]
["GL 100 Golder Units tel: 1234567899\n I/O 1034567 $25.50",{"$f":"RegExpMatch(A8, $A$2)","v":true},null,null,"GL 100 Golder Units tel: 1234567899\n I/O 1034567 $25.50"]
["Case sandpaper\n I/O 1033877, CS-190",{"$f":"RegExpMatch(A9, $A$2)","v":false},null,null,"Case sandpaper\n I/O 1033877, CS-190"]
[]
["How many cells contain phone numbers? ",{"$f":"COUNTIF(B5:B9, TRUE)","v":4},null,null,"How many cells contain phone numbers? ",{"$f":"SUM(--RegExpMatch(E5:E9, $A$2))","v":4}]