{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Regex Extract - Examples","Extract number","Extract all matches","Extract text","Extract email","Extract domain from email","Extract phone numbers","Extract dates","Extract time","Extract text between chars","Extract text between strings","Extract domain from URL"],"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 Extract - Examples","index":0,"n_rows":25,"n_cols":7,"rows_kept":25,"cut":false,"hidden":false,"merged":[[15,1,15,2],[16,1,16,2],[17,1,17,2],[21,1,21,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]]}
[]
[]
[]
[null,"Sample Workbook to Regex Extract Function"]
[]
[null,"The workbook shows how to use regular expressions in Excel to extract substrings."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-10-21"]
[null,"Tutorial URL","Regex to extract strings in Excel "]
[]
[null,"Examples:"]
["• ","Extract number from string "]
["• ","Get all matches "]
["• ","Extract text from string"]
["• ","Get email address from string "]
["• ","Extract email domain "]
["• ","Extract phone numbers "]
["• ","Extract date "]
["• ","Get time "]
["• ","Get text between two characters "]
["• ","Extract text between two strings "]
["• ","Get domain name from URL "]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Extract number","index":1,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Pattern for 1st number"]
["\\d+"]
[]
["Pattern for last number"]
["(\\d+)(?!.*\\d)"]
[]
["String","First number","Last number"]
["apples 5; oranges 3",{"$f":"RegExpExtract(A8, $A$2, 1)","v":"5"},{"$f":"RegExpExtract(A8, $A$5)","v":"3"}]
["Sold: 20 oranges, 30 lemons,  10 apples",{"$f":"RegExpExtract(A9, $A$2, 1)","v":"20"},{"$f":"RegExpExtract(A9, $A$5)","v":"10"}]
["grapes_30 lemons_40 oranges_50",{"$f":"RegExpExtract(A10, $A$2, 1)","v":"30"},{"$f":"RegExpExtract(A10, $A$5)","v":"50"}]
["40lemons 30grapes 20oranges",{"$f":"RegExpExtract(A11, $A$2, 1)","v":"40"},{"$f":"RegExpExtract(A11, $A$5)","v":"20"}]
["pears $500, oranges $100, lemons $300",{"$f":"RegExpExtract(A12, $A$2, 1)","v":"500"},{"$f":"RegExpExtract(A12, $A$5)","v":"300"}]

{"sheet":"Extract all matches","index":2,"n_rows":9,"n_cols":2,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\d+"]
[]
["String","All numbers"]
["apples 5; oranges 3",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE, RegExpExtract(A5, $A$2))","v":"5, 3"}]
["Sold: 20 oranges, 30 lemons,  10 apples",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE, RegExpExtract(A6, $A$2))","v":"20, 30, 10"}]
["grapes_30 lemons_40 oranges_50",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE, RegExpExtract(A7, $A$2))","v":"30, 40, 50"}]
["40lemons 30grapes 20oranges",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE, RegExpExtract(A8, $A$2))","v":"40, 30, 20"}]
["pears $500, oranges $100, lemons $300",{"$f":"_xlfn.TEXTJOIN(\", \",TRUE, RegExpExtract(A9, $A$2))","v":"500, 100, 300"}]

{"sheet":"Extract text","index":3,"n_rows":8,"n_cols":2,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["[^\\d]+"]
[]
["String","Text"]
["apples 5 oranges 3 lemons 10",{"$f":"_xlfn.TEXTJOIN(\"\",TRUE, RegExpExtract(A5, $A$2))","v":"apples  oranges  lemons "}]
["grapes_30 lemons_40 oranges_50",{"$f":"_xlfn.TEXTJOIN(\"\",TRUE, RegExpExtract(A6, $A$2))","v":"grapes_ lemons_ oranges_"}]
["40lemons, 30grapes, 20oranges",{"$f":"_xlfn.TEXTJOIN(\"\",TRUE, RegExpExtract(A7, $A$2))","v":"lemons, grapes, oranges"}]
["pears 500/oranges 100/lemons 300",{"$f":"_xlfn.TEXTJOIN(\"\",TRUE, RegExpExtract(A8, $A$2))","v":"pears /oranges /lemons "}]

{"sheet":"Extract email","index":4,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["[\\w\\.\\-]+@[A-Za-z0-9\\.\\-]+\\.[A-Za-z]{2,24}"]
[]
["String","Email address"]
["Jane Smith, USA, jane@gmail.com",{"$f":"RegExpExtract(A5, $A$2)","v":"jane@gmail.com"}]
["Ronnie Anderson, ronnie.a@university.edu, UK",{"$f":"RegExpExtract(A6, $A$2)","v":"ronnie.a@university.edu"}]
["Tom Boone, Canada, tom-boone@outlook.com",{"$f":"RegExpExtract(A7, $A$2)","v":"tom-boone@outlook.com"}]
["Sally Brooke, USA, Sally.B@big-company.com",{"$f":"RegExpExtract(A8, $A$2)","v":"Sally.B@big-company.com"}]
["Jeremy Hill, j123@outlook.com, Australia",{"$f":"RegExpExtract(A9, $A$2)","v":"j123@outlook.com"}]
["Robert Furlan, UK, robert_r@company.net",{"$f":"RegExpExtract(A10, $A$2)","v":"robert_r@company.net"}]
["z",{"$f":"RegExpExtract(A11, $A$2)","v":""}]

{"sheet":"Extract domain from email","index":5,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["@([A-Za-z0-9\\.\\-]+\\.[A-Za-z]{2,24})"]
[]
["String","Domain"]
["name.surname@domain.com",{"$f":"REPLACE(RegExpExtract(A5, $A$2), 1, 1, \"\")","v":"domain.com"}]
["name@subdomain.maindomain.com",{"$f":"REPLACE(RegExpExtract(A6, $A$2), 1, 1, \"\")","v":"subdomain.maindomain.com"}]
["name@company.uk",{"$f":"REPLACE(RegExpExtract(A7, $A$2), 1, 1, \"\")","v":"company.uk"}]
["john.doe@gmail.com",{"$f":"REPLACE(RegExpExtract(A8, $A$2), 1, 1, \"\")","v":"gmail.com"}]
["mailto:jane.doe@outlook.com",{"$f":"REPLACE(RegExpExtract(A9, $A$2), 1, 1, \"\")","v":"outlook.com"}]
["email at support@Ablebits.com or call",{"$f":"REPLACE(RegExpExtract(A10, $A$2), 1, 1, \"\")","v":"Ablebits.com"}]

{"sheet":"Extract phone numbers","index":6,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\(?\\d{3}[-\\. \\)]*\\d{3}[-\\. ]?\\d{4}\\b"]
[]
["String","Phone number"]
["Sales - 40% off! (541) 754-3010",{"$f":"RegExpExtract(A5, $A$2)","v":"(541) 754-3010"}]
["Mr. John Doe, (541) 754 3010",{"$f":"RegExpExtract(A6, $A$2)","v":"(541) 754 3010"}]
["(021)1234567 - my number",{"$f":"RegExpExtract(A7, $A$2)","v":"(021)1234567"}]
["Call 123-345-4567 or email",{"$f":"RegExpExtract(A8, $A$2)","v":"123-345-4567"}]
["Sold for $100,000.00; 123 456 7899",{"$f":"RegExpExtract(A9, $A$2)","v":"123 456 7899"}]
["P.O. Box 350, tel: 1234567899",{"$f":"RegExpExtract(A10, $A$2)","v":"1234567899"}]
["(123).345.4567 ext.201",{"$f":"RegExpExtract(A11, $A$2)","v":"(123).345.4567"}]

{"sheet":"Extract dates","index":7,"n_rows":11,"n_cols":2,"rows_kept":11,"cut":false,"hidden":false,"merged":[[0,0,0,4],[1,0,1,4]]}
["Pattern"]
["\\b\\d{1,2}[\\/-](\\d{1,2}|(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec))[\\/-](\\d{4}|\\d{2})\\b"]
[]
["String","Date"]
["Launched on 1/1/2021 at 14:50 ",{"$f":"RegExpExtract(A5, $A$2, 1, FALSE)","v":"1/1/2021"}]
["Item AA-001 delivered on 3-Feb-2021",{"$f":"RegExpExtract(A6, $A$2, 1, FALSE)","v":"3-Feb-2021"}]
["Invoice 1234567 - paid on 14/20/21, confirmed",{"$f":"RegExpExtract(A7, $A$2, 1, FALSE)","v":"14/20/21"}]
["Beta 1 released on 05/03/21. Successful.",{"$f":"RegExpExtract(A8, $A$2, 1, FALSE)","v":"05/03/21"}]
["ID 11-ABC-222 checked",{"$f":"RegExpExtract(A9, $A$2, 1, FALSE)","v":""}]
["Items 11/22/333 not found",{"$f":"RegExpExtract(A10, $A$2, 1, FALSE)","v":""}]
[null,{"$f":"RegExpExtract(A11, $A$2, 1, FALSE)","v":""}]

{"sheet":"Extract time","index":8,"n_rows":13,"n_cols":2,"rows_kept":13,"cut":false,"hidden":false,"merged":[[1,0,1,4],[0,0,0,4]]}
["Pattern"]
["\\b(0?[0-9]|1[0-2]):[0-5]\\d(:[0-5]\\d)?\\s?(AM|PM)\\b|\\b([0-9]|[0-1]\\d|2[0-3]):[0-5]\\d(:[0-5]\\d)?(?!:)"]
[]
["String","Date"]
["Launched on 1/1/2021 at 14:50:15",{"$f":"RegExpExtract(A5, $A$2, 1, FALSE)","v":"14:50:15"}]
["Item AA-001 delivered at 6:30am",{"$f":"RegExpExtract(A6, $A$2, 1, FALSE)","v":"6:30am"}]
["Completed at 03:50 PM",{"$f":"RegExpExtract(A7, $A$2, 1, FALSE)","v":"03:50 PM"}]
["Beta 1 released at 12:00 yesterday",{"$f":"RegExpExtract(A8, $A$2, 1, FALSE)","v":"12:00"}]
["Wake up at 7:15",{"$f":"RegExpExtract(A9, $A$2, 1, FALSE)","v":"7:15"}]
["This is not time: 25:30",{"$f":"RegExpExtract(A10, $A$2, 1, FALSE)","v":""}]
["This is not time: 20:30:60",{"$f":"RegExpExtract(A11, $A$2, 1, FALSE)","v":""}]
[null,{"$f":"RegExpExtract(A12, $A$2, 1, FALSE)","v":""}]
[null,{"$f":"RegExpExtract(A13, $A$2, 1, FALSE)","v":""}]

{"sheet":"Extract text between chars","index":9,"n_rows":12,"n_cols":3,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\[(.*?)\\]"]
[]
["String",".NET fucntion","VBA fucntion"]
["V.1.1 tested [error 100]",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A5, $A$2)","v":"error 100"},{"$f":"SUBSTITUTE(SUBSTITUTE(_xlfn.TEXTJOIN(\", \", TRUE, RegExpExtract(A5, $A$2)), \"]\", \"\"),\"[\",\"\")","v":"error 100"}]
["V.1.3 [error 101], [error 104] passed to AQ",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A6, $A$2)","v":"error 101"},{"$f":"SUBSTITUTE(SUBSTITUTE(_xlfn.TEXTJOIN(\", \", TRUE, RegExpExtract(A6, $A$2)), \"]\", \"\"),\"[\",\"\")","v":"error 101, error 104"}]
["V.1.4  [error 102];[error 103];[error104]",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A7, $A$2)","v":"error 102"},{"$f":"SUBSTITUTE(SUBSTITUTE(_xlfn.TEXTJOIN(\", \", TRUE, RegExpExtract(A7, $A$2)), \"]\", \"\"),\"[\",\"\")","v":"error 102, error 103, error104"}]
["V.1.5 [no info]",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A8, $A$2)","v":"no info"},{"$f":"SUBSTITUTE(SUBSTITUTE(_xlfn.TEXTJOIN(\", \", TRUE, RegExpExtract(A8, $A$2)), \"]\", \"\"),\"[\",\"\")","v":"no info"}]
["V.1.6 no info",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A9, $A$2)","v":{"$error":"#N/A"}},{"$f":"SUBSTITUTE(SUBSTITUTE(_xlfn.TEXTJOIN(\", \", TRUE, RegExpExtract(A9, $A$2)), \"]\", \"\"),\"[\",\"\")","v":""}]
[]
[]
["Note! Column B contains a custom function that only works on the machines with Ultimate Suite for Excel installed."]

{"sheet":"Extract text between strings","index":10,"n_rows":12,"n_cols":2,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["test 1(.*?)test 2"]
[]
["String","Text between test 1 and test 2"]
["V.1.1 test 1 successful test 2 failed",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A5, $A$2)","v":" successful "}]
["V.1.2 test 1 no result test 2 failed",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A6, $A$2)","v":" no result "}]
["V.1.2.2 test 1 error:1001 test 2 failed test 3 N/A",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A7, $A$2)","v":" error:1001 "}]
["V.1.3 test 1 successful test 2 successful",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A8, $A$2)","v":" successful "}]
["V.2 test 1 successful",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A9, $A$2)","v":{"$error":"#N/A"}}]
[]
[]
["Note! Column B contains a custom function that only works on the machines with Ultimate Suite for Excel installed."]

{"sheet":"Extract domain from URL","index":11,"n_rows":17,"n_cols":3,"rows_kept":17,"cut":false,"hidden":false,"merged":[[0,0,0,1],[1,0,1,1],[3,0,3,1],[4,0,4,1]]}
["Pattern 1: full domain"]
["(?:https?\\:|^|\\s)\\/\\/((?:[A-Za-z\\d\\-\\.]{2,255}\\.)?[A-Za-z\\d\\-]{1,63}\\.[A-Za-z]{2,24})"]
[]
["Pattern 2: Second-level domain"]
["(?:https?\\:|^|\\s)\\/\\/(?:[A-Za-z\\d\\-\\.]{1,255}\\.)?([A-Za-z\\d\\-]{1,63}\\.[A-Za-z]{2,24})"]
[]
["URL","Full domain","Second-level domain"]
["http://en.wikipedia.org/wiki/",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A8, $A$2)","v":"en.wikipedia.org"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A8, $A$5)","v":"wikipedia.org"}]
["//google.com/search?q=extract+url&",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A9, $A$2)","v":"google.com"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A9, $A$5)","v":"google.com"}]
["https://www.ablebits.com/office-addins-blog?page=3",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A10, $A$2)","v":"www.ablebits.com"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A10, $A$5)","v":"ablebits.com"}]
["https://www.mobile.ablebits.com",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A11, $A$2)","v":"www.mobile.ablebits.com"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A11, $A$5)","v":"ablebits.com"}]
["https://www.facebook.com/ablebits",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A12, $A$2)","v":"www.facebook.com"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A12, $A$5)","v":"facebook.com"}]
["https://www.youtube.com/c/Ablebits_com/featured",{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A13, $A$2)","v":"www.youtube.com"},{"$f":"_xll.AblebitsUDF.AblebitsRegexExtract(A13, $A$5)","v":"youtube.com"}]
[]
[]
[]
["Note! Columns B and C contain a custom function that only works on the machines with Ultimate Suite for Excel installed."]