{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Remove Whitespaces - Examples","Remove all spaces","Remove all but one space","Leading & trailing spaces","Keep line breaks","Replace with another char","Remove empty lines"],"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":"Remove Whitespaces - Examples","index":0,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":false,"merged":[[15,1,15,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,"Remove Whitespaces in Excel with Regex"]
[]
[null,"The workbook shows how to remove whitespaces in Excel using regular expressions."]
[null,"Author","Ablebits.com"]
[null,"Last update","2021-10-11"]
[null,"Tutorial URL","How to remove whitespace in Excel using Regex"]
[]
[null,"Examples:"]
["• ","Remove all spaces"]
["• ","Strip more than one consecutive whitespaces "]
["• ","Trim leading and trailing whitespace "]
["• ","Remove extra whitespace but keep line breaks "]
["• ","Replace multiple spaces with one character "]
["• ","Remove empty lines "]
[]
[]
[null,null,null,null,null,null," "]

{"sheet":"Remove all spaces","index":1,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["\\s+"]
[]
["String","Result"]
[" 201     Newark, NJ",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"201Newark,NJ"}]
["  202     Washington, DC",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"202Washington,DC"}]
["203     New Haven, CT",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"203NewHaven,CT"}]
["     204     Yorkton, MB",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"204Yorkton,MB"}]
[" 205      Birmingham, AL",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"205Birmingham,AL"}]
["     206     Seattle, WA",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"206Seattle,WA"}]

{"sheet":"Remove all but one space","index":2,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern 1","Pattern 2"]
["\\s+","^[\\s]+|[\\s]+$"]
[]
["String","Result"]
[" 201     Newark, NJ",{"$f":"RegExpReplace(RegExpReplace(A5, \"\\s+\", \" \"), \"^[\\s]+|[\\s]+$\", \"\")","v":"201 Newark, NJ"}]
["  202     Washington, DC",{"$f":"RegExpReplace(RegExpReplace(A6, $A$2, \" \"), $B$2, \"\")","v":"202 Washington, DC"}]
["203     New Haven, CT",{"$f":"RegExpReplace(RegExpReplace(A7, $A$2, \" \"), $B$2, \"\")","v":"203 New Haven, CT"}]
["     204     Yorkton, MB",{"$f":"RegExpReplace(RegExpReplace(A8, $A$2, \" \"), $B$2, \"\")","v":"204 Yorkton, MB"}]
[" 205      Birmingham, AL",{"$f":"RegExpReplace(RegExpReplace(A9, $A$2, \" \"), $B$2, \"\")","v":"205 Birmingham, AL"}]
["     206     Seattle, WA",{"$f":"RegExpReplace(RegExpReplace(A10, $A$2, \" \"), $B$2, \"\")","v":"206 Seattle, WA"}]

{"sheet":"Leading & trailing spaces","index":3,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^[\\s]+|[\\s]+$"]
[]
["String","Result"]
["  201     Newark, NJ    ",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"201     Newark, NJ"}]
["  202     Washington, DC ",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"202     Washington, DC"}]
["203     New Haven, CT  ",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"203     New Haven, CT"}]
["     204     Yorkton, MB",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"204     Yorkton, MB"}]
[" 205      Birmingham, AL   ",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"205      Birmingham, AL"}]
["     206     Seattle, WA",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"206     Seattle, WA"}]

{"sheet":"Keep line breaks","index":4,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern 1","Pattern 2"]
["[\\t ]+","^[\\t ]+|[\\t ]+$"]
[]
["String","Result"]
[" Area code:   201    \n     Newark, NJ",{"$f":"RegExpReplace(RegExpReplace(A5, $A$2, \" \"), $B$2, \"\")","v":"Area code: 201\nNewark, NJ"}]
["  Area code:  202  \n    Washington, DC",{"$f":"RegExpReplace(RegExpReplace(A6, $A$2, \" \"), $B$2, \"\")","v":"Area code: 202\nWashington, DC"}]
["Area code:  203  \n   New Haven, CT",{"$f":"RegExpReplace(RegExpReplace(A7, $A$2, \" \"), $B$2, \"\")","v":"Area code: 203\nNew Haven, CT"}]
["  Area code:    204   \n    Yorkton, MB",{"$f":"RegExpReplace(RegExpReplace(A8, $A$2, \" \"), $B$2, \"\")","v":"Area code: 204\nYorkton, MB"}]
["Area code:   205   \n   Birmingham, AL",{"$f":"RegExpReplace(RegExpReplace(A9, $A$2, \" \"), $B$2, \"\")","v":"Area code: 205\nBirmingham, AL"}]
["     Area code: 206   \n  Seattle, WA",{"$f":"RegExpReplace(RegExpReplace(A10, $A$2, \" \"), $B$2, \"\")","v":"Area code: 206\nSeattle, WA"}]

{"sheet":"Replace with another char","index":5,"n_rows":12,"n_cols":2,"rows_kept":12,"cut":false,"hidden":false,"merged":[]}
["Pattern 1","Replacement 1"]
["^[\\t ]+|[\\t ]+$"]
[]
["Pattern 2","Replacement 2"]
["\\s+","-"]
[]
["String","Result"]
["AA   100  02",{"$f":"RegExpReplace(RegExpReplace(A8, $A$2,$B$2), $A$5, $B$5)","v":"AA-100-02"}]
[" BB 101    01",{"$f":"RegExpReplace(RegExpReplace(A9, $A$2,$B$2), $A$5, $B$5)","v":"BB-101-01"}]
["   1    CC 02   ",{"$f":"RegExpReplace(RegExpReplace(A10, $A$2,$B$2), $A$5, $B$5)","v":"1-CC-02"}]
["  DE   0132  ",{"$f":"RegExpReplace(RegExpReplace(A11, $A$2,$B$2), $A$5, $B$5)","v":"DE-0132"}]
["     76   EFE 801 ",{"$f":"RegExpReplace(RegExpReplace(A12, $A$2,$B$2), $A$5, $B$5)","v":"76-EFE-801"}]

{"sheet":"Remove empty lines","index":6,"n_rows":10,"n_cols":2,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Pattern"]
["^[\\t ]*\\n"]
[]
["String","Result"]
["v.1 ERROR 45\n    \nUnexpected error",{"$f":"RegExpReplace(A5, $A$2, \"\")","v":"v.1 ERROR 45\nUnexpected error"}]
["v1.1 \n\nERROR 235  \n  \nAuthentication failed",{"$f":"RegExpReplace(A6, $A$2, \"\")","v":"v1.1 \nERROR 235  \nAuthentication failed"}]
["v1.2\nNo data",{"$f":"RegExpReplace(A7, $A$2, \"\")","v":"v1.2\nNo data"}]
["v.2 \n\nERROR 403 \nForbidden",{"$f":"RegExpReplace(A8, $A$2, \"\")","v":"v.2 \nERROR 403 \nForbidden"}]
["v.2.1 \n\nERROR 432\n\nConnection timed out",{"$f":"RegExpReplace(A9, $A$2, \"\")","v":"v.2.1 \nERROR 432\nConnection timed out"}]
["v.2.3 ERROR 500 \n\nInternal server error",{"$f":"RegExpReplace(A10, $A$2, \"\")","v":"v.2.3 ERROR 500 \nInternal server error"}]