{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Regex Remove - Examples","Remove all or one match","Remove characters","Remove special characters","Remove non-numeric","After first space","After certain character","Before last space","Before first space","Before character","Remove all except","Remove html tags","Remove text in brackets"],"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 Remove - Examples","index":0,"n_rows":26,"n_cols":7,"rows_kept":26,"cut":false,"hidden":false,"merged":[[15,1,15,2],[16,1,16,2],[17,1,17,2],[22,1,22,2],[3,1,3,2],[5,1,5,2],[8,2,8,3],[11,1,11,2],[12,1,12,2],[14,1,14,2],[18,1,18,2],[19,1,19,2],[20,1,20,2],[21,1,21,2]]}
[]
[]
[]
[null,"Sample Workbook to Regex Remove in Excel"]
[]
[null,"The workbook shows how to remove text strings in Excel using regular expressions."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-10-15"]
[null,"Tutorial URL","Remove characters or text in Excel using regular expressions"]
[]
[null,"Examples:"]
["• ","Delete all matches or specific match "]
["• ","Strip certain characters "]
["• ","Remove special characters"]
["• ","Delete non-numeric characters "]
["• ","Strip off everything after space "]
["• ","Remove text after certain character "]
["• ","Remove everything before last space "]
["• ","Strip off everything before first space "]
["• ","Delete everything before character "]
["• ","Remove everything except "]
["• ","Remove HTML tags "]
["• ","Remove text in brackets and parentheses "]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Remove all or one match","index":1,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["#\\d{5}\\b"]
[]
["String","Remove first match","Remove all matches"]
["Apples: $500.00 #10101 #10102 #10103",{"$f":"RegExpReplace(A5, $A$2, \"\", 1)","v":"Apples: $500.00  #10102 #10103"},{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"Apples: $500.00   "}]
["Oranges: $250.50 #20101 #20103",{"$f":"RegExpReplace(A6, $A$2, \"\", 1)","v":"Oranges: $250.50  #20103"},{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"Oranges: $250.50  "}]
["Lemons: N/A",{"$f":"RegExpReplace(A7, $A$2, \"\", 1)","v":"Lemons: N/A"},{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"Lemons: N/A"}]
["Grapes: $650 #30101 12/10/2022",{"$f":"RegExpReplace(A8, $A$2, \"\", 1)","v":"Grapes: $650  12/10/2022"},{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"Grapes: $650  12/10/2022"}]
[null,{"$f":"TRIM(RegExpReplace(RegExpReplace(A9, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":""}]
[null,{"$f":"TRIM(RegExpReplace(RegExpReplace(A10, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":""}]
[null,{"$f":"TRIM(RegExpReplace(RegExpReplace(A11, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":""}]

{"sheet":"Remove characters","index":2,"n_rows":11,"n_cols":3,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\(|\\)|-|\\.|\\s"]
[]
["String","Result","Standardized"]
["(541) 754-3010",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"5417543010"},{"$f":"\"(\"&LEFT(B5, 3)&\") \"&MID(B5, 4, 3)&\"-\"&RIGHT(B5, 4)","v":"(541) 754-3010"}]
["(541) 754 3010",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"5417543010"},{"$f":"\"(\"&LEFT(B6, 3)&\") \"&MID(B6, 4, 3)&\"-\"&RIGHT(B6, 4)","v":"(541) 754-3010"}]
["(021)1234567",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"0211234567"},"(021) 123-4567"]
["123-345-4567",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"1233454567"},"(123) 345-4567"]
["123 456 7899",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"1234567899"},"(123) 456-7899"]
["8914567899",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"8914567899"},"(891) 456-7899"]
["(789).345.4567",{"$f":"RegExpReplace(A11, $A$2, \"\")","v":"7893454567"},"(789) 345-4567"]

{"sheet":"Remove special characters","index":3,"n_rows":6,"n_cols":2,"rows_kept":6,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["[^0-9a-zA-Z\\., ]+"]
[]
["String","Result"]
["One**, two, #& three,   %%)four, five,(",{"$f":"TRIM(RegExpReplace(A5, $A$2, \"\"))","v":"One, two, three, four, five,"}]
["#Once @I caught  ~~    <>a fish alive.",{"$f":"TRIM(RegExpReplace(A6, $A$2, \"\"))","v":"Once I caught a fish alive."}]

{"sheet":"Remove non-numeric","index":4,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\D+"]
[]
["String","Result"]
["AA-100",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"100"}]
["BB/101",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"101"}]
["1/CC/02",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"102"}]
["DE-0132",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"0132"}]
["76/EFE/801",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"76801"}]

{"sheet":"After first space","index":5,"n_rows":10,"n_cols":3,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
[null,"Pattern","Pattern"]
[null," .*","\\s.*"]
[]
["String","Result","Result"]
["v.1 Successful\nCompleted on 4-Sep-21",{"$f":"RegExpReplace(A5, $B$2, \"\")","v":"v.1\nCompleted"},{"$f":"RegExpReplace(A5, $C$2, \"\")","v":"v.1"}]
["v1.1 Successful\nCompleted on 14-Oct-21",{"$f":"RegExpReplace(A6, $B$2, \"\")","v":"v1.1\nCompleted"},{"$f":"RegExpReplace(A6, $C$2, \"\")","v":"v1.1"}]
["v1.2 N/A",{"$f":"RegExpReplace(A7, $B$2, \"\")","v":"v1.2"},{"$f":"RegExpReplace(A7, $C$2, \"\")","v":"v1.2"}]
["v.2 ERROR_403\nFailed 22-Oct-22",{"$f":"RegExpReplace(A8, $B$2, \"\")","v":"v.2\nFailed"},{"$f":"RegExpReplace(A8, $C$2, \"\")","v":"v.2"}]
["v.2.1 ERROR_432\nFailed 22-Oct-22",{"$f":"RegExpReplace(A9, $B$2, \"\")","v":"v.2.1\nFailed"},{"$f":"RegExpReplace(A9, $C$2, \"\")","v":"v.2.1"}]
["v.2.3 ERROR_500\nFailed 4-Oct-22",{"$f":"RegExpReplace(A10, $B$2, \"\")","v":"v.2.3\nFailed"},{"$f":"RegExpReplace(A10, $C$2, \"\")","v":"v.2.3"}]

{"sheet":"After certain character","index":6,"n_rows":9,"n_cols":3,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
[null,"Pattern","Pattern"]
[null,",.*",",(.|\\n)*"]
[]
["String","Result","Result"]
["Analysis, Successful\nCompleted, 4-Jul-21",{"$f":"RegExpReplace(A5, $B$2, \"\")","v":"Analysis\nCompleted"},{"$f":"RegExpReplace(A5, $C$2, \"\")","v":"Analysis"}]
["Development, Successful\nCompleted, 14-Oct-21",{"$f":"RegExpReplace(A6, $B$2, \"\")","v":"Development\nCompleted"},{"$f":"RegExpReplace(A6, $C$2, \"\")","v":"Development"}]
["QA, No data",{"$f":"RegExpReplace(A7, $B$2, \"\")","v":"QA"},{"$f":"RegExpReplace(A7, $C$2, \"\")","v":"QA"}]
["Field tests, ERROR 403, 503\nFailed, 22-Oct-21, 25-Oct-21",{"$f":"RegExpReplace(A8, $B$2, \"\")","v":"Field tests\nFailed"},{"$f":"RegExpReplace(A8, $C$2, \"\")","v":"Field tests"}]
["Final test, ERROR 432\nFailed, 29-Oct-21",{"$f":"RegExpReplace(A9, $B$2, \"\")","v":"Final test\nFailed"},{"$f":"RegExpReplace(A9, $C$2, \"\")","v":"Final test"}]

{"sheet":"Before last space","index":7,"n_rows":8,"n_cols":3,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
[null,"Pattern","Pattern"]
[null,".* ",".*\\s"]
[]
["String","Before last space in each line","Before last space in last line"]
[" v.1 4-Sep-21\nERROR 400 Test_1",{"$f":"RegExpReplace(A5, $B$2, \"\")","v":"4-Sep-21\nTest_1"},{"$f":"RegExpReplace(A5, $C$2, \"\")","v":"Test_1"}]
["v1.1 22-Sep-24\nERROR 504 Test_2",{"$f":"RegExpReplace(A6, $B$2, \"\")","v":"22-Sep-24\nTest_2"},{"$f":"RegExpReplace(A6, $C$2, \"\")","v":"Test_2"}]
["v.2 1-Oct-21\nERROR 403 Test_3",{"$f":"RegExpReplace(A7, $B$2, \"\")","v":"1-Oct-21\nTest_3"},{"$f":"RegExpReplace(A7, $C$2, \"\")","v":"Test_3"}]
["v1.2 14-Oct-21\nNo errors Final_test",{"$f":"RegExpReplace(A8, $B$2, \"\")","v":"14-Oct-21\nFinal_test"},{"$f":"RegExpReplace(A8, $C$2, \"\")","v":"Final_test"}]

{"sheet":"Before first space","index":8,"n_rows":8,"n_cols":3,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^[^ ]* +"]
[]
["String","Before 1st space in each line","Before 1st space in line 1"]
["      v.1 4-Sep-21\nERROR 400: Bad request",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"v.1 4-Sep-21\n400: Bad request"},{"$f":"RegExpReplace(A5, $A$2, \"\", 1)","v":"v.1 4-Sep-21\nERROR 400: Bad request"}]
["    v1.1 22-Sep-21\nERROR 504 : Gateway timeout",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"v1.1 22-Sep-21\n504 : Gateway timeout"},{"$f":"RegExpReplace(A6, $A$2, \"\", 1)","v":"v1.1 22-Sep-21\nERROR 504 : Gateway timeout"}]
["       v.2 1-Oct-21\nERROR 403: Forbidden",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"v.2 1-Oct-21\n403: Forbidden"},{"$f":"RegExpReplace(A7, $A$2, \"\", 1)","v":"v.2 1-Oct-21\nERROR 403: Forbidden"}]
["v1.2\nN/A",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"v1.2\nN/A"},{"$f":"RegExpReplace(A8, $A$2, \"\", 1)","v":"v1.2\nN/A"}]

{"sheet":"Before character","index":9,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^[^:]*:\\s*"]
[]
["String","Result"]
["ERROR 45: Unexpected error: 1-Oct-22",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"Unexpected error: 1-Oct-22"}]
["ERROR 235: Authentication failed",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"Authentication failed"}]
["ERROR 400:Bad request: fixed",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"Bad request: fixed"}]
["ERROR 403: Forbidden: 12-Oct-22",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"Forbidden: 12-Oct-22"}]
["ERROR 432: Connection timed out",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"Connection timed out"}]
["ERROR 500: Internal server error",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"Internal server error"}]
["ERROR 504: Gateway timeout: 10-Oct-22",{"$f":"RegExpReplace(A11, $A$2, \"\")","v":"Gateway timeout: 10-Oct-22"}]

{"sheet":"Remove all except","index":10,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["[^a-z\\.]+"]
[]
["String","Result"]
["~~675somefile.doc",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"somefile.doc"}]
["098img.png",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"img.png"}]
["%010_flower.jpg%08",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"flower.jpg"}]
["C:\\123\\setup.exe",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"setup.exe"}]

{"sheet":"Remove html tags","index":11,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["<.*?>"]
[]
["String","Result1","Result2"]
["<h1>RegEx Rocks!</h1>",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"RegEx Rocks!"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A5, $A$2, \" \"), \" +\", \" \"))","v":"RegEx Rocks!"}]
["<td class=\"header\">Heading</td>",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"Heading"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A6, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"Heading"}]
["<li><a href=\"#1\">First item</a></li>",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"First item"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A7, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"First item"}]
["<div class=\"code\">Code here</div>",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"Code here"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A8, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"Code here"}]
["<div class=\"sm-tip\"><b>Useful tip</b>",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"Useful tip"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A9, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"Useful tip"}]
["<td>data1</td><td>data2</td>",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"data1data2"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A10, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"data1 data2"}]
["<b>A</b><i>1</i>",{"$f":"RegExpReplace(A11, $A$2, \"\")","v":"A1"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A11, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"A 1"}]
["<strong>Note!</strong><em>some text</em>",{"$f":"RegExpReplace(A12, $A$2, \"\")","v":"Note!some text"},{"$f":"TRIM(RegExpReplace(RegExpReplace(A12, \"<[^>]*>\", \" \"), \" +\", \" \"))","v":"Note! some text"}]

{"sheet":"Remove text in brackets","index":12,"n_rows":15,"n_cols":5,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["(\\(.*?\\))|(\\[.*?\\])"]
[]
["String","Result"]
["Apples: $500.00 (#10101) (#10102) Completed",{"$f":"TRIM(RegExpReplace(A5, $A$2, \"\"))","v":"Apples: $500.00 Completed"}]
["Oranges: $250.50 (#20101) (#20103) [10-Nov-2021]",{"$f":"TRIM(RegExpReplace(A6, $A$2, \"\"))","v":"Oranges: $250.50"}]
["[Waiting for confirmation] Lemons:  $650 (#30101)",{"$f":"TRIM(RegExpReplace(A7, $A$2, \"\"))","v":"Lemons: $650"}]
["Grapes: $650 (#30101) [12/10/2022] Open",{"$f":"TRIM(RegExpReplace(A8, $A$2, \"\"))","v":"Grapes: $650 Open"}]
[]
[]
[]
[]
[]
[]
[null,null,null,null," "]