{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Excel Regex - Examples","Regex Match","Regex Extract","Regex Replace","Regex Remove"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":0,"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":"Excel Regex - Examples","index":0,"n_rows":18,"n_cols":7,"rows_kept":18,"cut":false,"hidden":false,"merged":[[13,1,13,2],[14,1,14,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 Using Regex in Excel"]
[]
[null,"The workbook shows how to use regular expressions to match, extract, replace and remove strings in Excel."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-10-20"]
[null,"Tutorial URL","Excel RegEx: using regular expressions in formulas"]
[]
[null,"Examples:"]
["• ","Match strings using regular expressions "]
["• ","Extract strings matching a regex"]
["• ","Replace strings matching a regex"]
["• ","Remove strings using regular expressions "]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Regex Match","index":1,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\b[A-Z]{2}-\\d{4}\\b"]
[]
["String","SKU available?","SKU available?"]
["Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \n4111 1111 4555 1142",{"$f":"RegExpMatch(A5, $A$2)","v":true},{"$f":"IF(RegExpMatch(A5, $A$2), \"Yes\", \"No\")","v":"Yes"}]
["20x Leaf puzzle  LP-4900\n4166 6766 6766 6746\n $7.49 8(541) 754-3010",{"$f":"RegExpMatch(A6, $A$2)","v":true},{"$f":"IF(RegExpMatch(A6, $A$2), \"Yes\", \"No\")","v":"Yes"}]
["Metal shelf $49.99 MS-7009\n(123).345.4567  \n4988 4388 4388 4305",{"$f":"RegExpMatch(A7, $A$2)","v":true},{"$f":"IF(RegExpMatch(A7, $A$2), \"Yes\", \"No\")","v":"Yes"}]
["GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa 4000 6200 0000 0007",{"$f":"RegExpMatch(A8, $A$2)","v":false},{"$f":"IF(RegExpMatch(A8, $A$2), \"Yes\", \"No\")","v":"No"}]
["Case sandpaper\n I/O 1033877\nCS-1901",{"$f":"RegExpMatch(A9, $A$2)","v":true},{"$f":"IF(RegExpMatch(A9, $A$2), \"Yes\", \"No\")","v":"Yes"}]

{"sheet":"Regex Extract","index":2,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\b\\d{7}\\b"]
[]
["String","Invoice"]
["Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \n4111 1111 4555 1142",{"$f":"RegExpExtract(A5, $A$2)","v":"1034567"}]
["20x Leaf puzzle  LP-4900\n4166 6766 6766 6746\n $7.49 8(541) 754-3010",{"$f":"RegExpExtract(A6, $A$2)","v":""}]
["Metal shelf $49.99 MS-7009\n(123).345.4567  \n4988 4388 4388 4305",{"$f":"RegExpExtract(A7, $A$2)","v":""}]
["GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa 4000 6200 0000 0007",{"$f":"RegExpExtract(A8, $A$2)","v":"1038765"}]
["Case sandpaper\n I/O 1033877\nCS-1901",{"$f":"RegExpExtract(A9, $A$2)","v":"1033877"}]

{"sheet":"Regex Replace","index":3,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\b\\d{4} \\d{4} \\d{4} \\d{4}\\b","XXXX XXXX XXXX XXXX"]
[]
["String","Card numbers replaced"]
["Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \n4111 1111 4555 1142",{"$f":"RegExpReplace(A5, $A$2, $B$2)","v":"Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \nXXXX XXXX XXXX XXXX"}]
["20x Leaf puzzle  LP-4900\n4166 6766 6766 6746\n $7.49 8(541) 754-3010",{"$f":"RegExpReplace(A6, $A$2, $B$2)","v":"20x Leaf puzzle  LP-4900\nXXXX XXXX XXXX XXXX\n $7.49 8(541) 754-3010"}]
["Metal shelf $49.99 MS-7009\n(123).345.4567  \n4988 4388 4388 4305",{"$f":"RegExpReplace(A7, $A$2, $B$2)","v":"Metal shelf $49.99 MS-7009\n(123).345.4567  \nXXXX XXXX XXXX XXXX"}]
["GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa 4000 6200 0000 0007",{"$f":"RegExpReplace(A8, $A$2, $B$2)","v":"GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa XXXX XXXX XXXX XXXX"}]
["Case sandpaper\n I/O 1033877\nCS-1901",{"$f":"RegExpReplace(A9, $A$2, $B$2)","v":"Case sandpaper\n I/O 1033877\nCS-1901"}]
[null,{"$f":"RegExpReplace(A10, $A$2, \"--\")","v":""}]

{"sheet":"Regex Remove","index":4,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\b\\d{4} \\d{4} \\d{4} \\d{4}\\b"]
[]
["String","Card numbers removed"]
["Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \n4111 1111 4555 1142",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"Chain frame CH-1101 I/O 1034567 $10.99\n+1 123.237.4567 \n"}]
["20x Leaf puzzle  LP-4900\n4166 6766 6766 6746\n $7.49 8(541) 754-3010",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"20x Leaf puzzle  LP-4900\n\n $7.49 8(541) 754-3010"}]
["Metal shelf $49.99 MS-7009\n(123).345.4567  \n4988 4388 4388 4305",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"Metal shelf $49.99 MS-7009\n(123).345.4567  \n"}]
["GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa 4000 6200 0000 0007",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"GL 100 Golder Units tel: 1234567899\n I/O 1038765 $25.50\nVisa "}]
["Case sandpaper\n I/O 1033877\nCS-1901",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"Case sandpaper\n I/O 1033877\nCS-1901"}]
[null,{"$f":"RegExpReplace(A10, $A$2, \"--\")","v":""}]