{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Regex Replace - Examples","Replace string","Replace numbers","Replace amounts of money","Replace all","Replace one instance","AblebitsRegexReplace"],"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":"Regex Replace - Examples","index":0,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[[16,1,16,2],[3,1,3,2],[5,1,5,2],[8,2,8,3],[11,1,11,2],[12,1,12,2],[15,1,15,2]]}
[]
[]
[]
[null,"Sample Workbook to Regex Replace in Excel"]
[]
[null,"The workbook shows how to use regular expressions to find and replace strings in Excel."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-10-06"]
[null,"Tutorial URL","Excel Regex function to find and replace strings"]
[]
[null,"Examples:"]
["• ","Regex to replace string "]
["• ","Regex to replace numbers in a string"]
["• ","Regex to replace amounts of money"]
["• ","Regex to find and replace all matches"]
["• ","Regex to replace a specific instance"]
["• ","AblebitsRegexReplace function"]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Replace string","index":1,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\d{3}-\\d{2}-\\d{4}","XXX-XX-XXXX"]
[]
["String","Result"]
["Jane Smith, SSN:123-45-7890",{"$f":"RegExpReplace(A5, $A$2, $B$2)","v":"Jane Smith, SSN:XXX-XX-XXXX"}]
["Ronnie Anderson, SSN:190-56-5210",{"$f":"RegExpReplace(A6, $A$2, $B$2)","v":"Ronnie Anderson, SSN:XXX-XX-XXXX"}]
["Big corporation EIN: 12-1234567",{"$f":"RegExpReplace(A7, $A$2, $B$2)","v":"Big corporation EIN: 12-1234567"}]
["Sally Brooke, KS, 123-06-7890",{"$f":"RegExpReplace(A8, $A$2, $B$2)","v":"Sally Brooke, KS, XXX-XX-XXXX"}]
["Jeremy Hill, tel: 190-456-7690, CA",{"$f":"RegExpReplace(A9, $A$2, $B$2)","v":"Jeremy Hill, tel: 190-456-7690, CA"}]
["Robert Furlan, SSN 053-56-7690",{"$f":"RegExpReplace(A10, $A$2, $B$2)","v":"Robert Furlan, SSN XXX-XX-XXXX"}]

{"sheet":"Replace numbers","index":2,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\d+","*"]
[]
["String","Result"]
["2x apples $6 and 3x oranges $6.50",{"$f":"RegExpReplace(A5,  $A$2, $B$2)","v":"*x apples $* and *x oranges $*.*"}]
["Sold: 20 oranges, 30 lemons,  10 apples",{"$f":"RegExpReplace(A6,  $A$2, $B$2)","v":"Sold: * oranges, * lemons,  * apples"}]
["Grapes: qty. 10, price $9.99",{"$f":"RegExpReplace(A7,  $A$2, $B$2)","v":"Grapes: qty. *, price $*.*"}]
["Lemons: in stock 300, ordered 100",{"$f":"RegExpReplace(A8,  $A$2, $B$2)","v":"Lemons: in stock *, ordered *"}]
["pears $50.49, oranges $100.00, lemons $350",{"$f":"RegExpReplace(A9,  $A$2, $B$2)","v":"pears $*.*, oranges $*.*, lemons $*"}]

{"sheet":"Replace amounts of money","index":3,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\$\\d+\\.?\\d{0,2}\\b","*"]
[]
["String","Result"]
["2x apples $6 and 3x oranges $6.50",{"$f":"RegExpReplace(A5,  $A$2, $B$2)","v":"2x apples * and 3x oranges *"}]
["Sold: 20 oranges, 30 lemons,  10 apples",{"$f":"RegExpReplace(A6,  $A$2, $B$2)","v":"Sold: 20 oranges, 30 lemons,  10 apples"}]
["Grapes: qty. 10, price $9.99",{"$f":"RegExpReplace(A7,  $A$2, $B$2)","v":"Grapes: qty. 10, price *"}]
["Lemons: in stock 300, ordered 100",{"$f":"RegExpReplace(A8,  $A$2, $B$2)","v":"Lemons: in stock 300, ordered 100"}]
["pears $50.49, oranges $100.00, lemons $350",{"$f":"RegExpReplace(A9,  $A$2, $B$2)","v":"pears *, oranges *, lemons *"}]

{"sheet":"Replace all","index":4,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\d{3}-\\d{2}-\\d{4}","CONF"]
[]
["String","Result"]
["Jane Smith SSN:123-45-7890 ITIN:912-67-2345",{"$f":"RegExpReplace(A5, $A$2, $B$2)","v":"Jane Smith SSN:CONF ITIN:CONF"}]
["Ronnie Wolf SSN: 190-56-5210 ITIN: 190-56-5210",{"$f":"RegExpReplace(A6, $A$2, $B$2)","v":"Ronnie Wolf SSN: CONF ITIN: CONF"}]
["Big corporation EIN: 12-1234567",{"$f":"RegExpReplace(A7, $A$2, $B$2)","v":"Big corporation EIN: 12-1234567"}]
["Sally Brooke, 123-06-7890 923-06-7890",{"$f":"RegExpReplace(A8, $A$2, $B$2)","v":"Sally Brooke, CONF CONF"}]
["Jeremy Hill tel: 190-456-7690 SSN: 453-34-7654",{"$f":"RegExpReplace(A9, $A$2, $B$2)","v":"Jeremy Hill tel: 190-456-7690 SSN: CONF"}]
["Robert Furlan SSN 053-56-7690 ITIN 953-56-7690",{"$f":"RegExpReplace(A10, $A$2, $B$2)","v":"Robert Furlan SSN CONF ITIN CONF"}]

{"sheet":"Replace one instance","index":5,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern","Replacement"]
["\\d{3}-\\d{2}-\\d{4}","CONF"]
[]
["String","Result"]
["Jane Smith SSN:123-45-7890 ITIN:912-67-2345",{"$f":"RegExpReplace(A5, $A$2, $B$2, 1)","v":"Jane Smith SSN:CONF ITIN:912-67-2345"}]
["Ronnie Wolf SSN: 190-56-5210 ITIN: 190-56-5210",{"$f":"RegExpReplace(A6, $A$2, $B$2, 1)","v":"Ronnie Wolf SSN: CONF ITIN: 190-56-5210"}]
["Big corporation EIN: 12-1234567",{"$f":"RegExpReplace(A7, $A$2, $B$2, 1)","v":"Big corporation EIN: 12-1234567"}]
["Sally Brooke, 123-06-7890 923-06-7890",{"$f":"RegExpReplace(A8, $A$2, $B$2, 1)","v":"Sally Brooke, CONF 923-06-7890"}]
["Jeremy Hill tel: 190-456-7690 SSN: 453-34-7654",{"$f":"RegExpReplace(A9, $A$2, $B$2, 1)","v":"Jeremy Hill tel: 190-456-7690 SSN: CONF"}]
["Robert Furlan SSN 053-56-7690 ITIN 953-56-7690",{"$f":"RegExpReplace(A10, $A$2, $B$2, 1)","v":"Robert Furlan SSN CONF ITIN 953-56-7690"}]

{"sheet":"AblebitsRegexReplace","index":6,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Replacement","Greedy search","Lazy search"]
["*","\\[(.*)\\]","\\[(.*?)\\]"]
[]
["String","Result","Result"]
["V.1.1 tested [error 100]",{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A5, $B$2, $A$2)","v":"V.1.1 tested [*]"},{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A5, $C$2, $A$2)","v":"V.1.1 tested [*]"}]
["V.1.3 [error 101], [error 104] passed to AQ",{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A6, $B$2, $A$2)","v":"V.1.3 [*] passed to AQ"},{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A6, $C$2, $A$2)","v":"V.1.3 [*], [*] passed to AQ"}]
["V.1.4  [error 102];[error 103];[error104]",{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A7, $B$2, $A$2)","v":"V.1.4  [*]"},{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A7, $C$2, $A$2)","v":"V.1.4  [*];[*];[*]"}]
["V.1.5 [no info]",{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A8, $B$2, $A$2)","v":"V.1.5 [*]"},{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A8, $C$2, $A$2)","v":"V.1.5 [*]"}]
["V.1.6 no info",{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A9, $B$2, $A$2)","v":"V.1.6 no info"},{"$f":"_xll.AblebitsUDF.AblebitsRegexReplace(A9, $C$2, $A$2)","v":"V.1.6 no info"}]
[]
[]
["Note! Columns B and C contain a custom function that only works on machines with Ultimate Suite for Excel installed."]