vba - Excel file contains invalid hidden characters that can't be removed -


i have peculiar problem hidden characters in excel spreadsheet uses vba create text file. i've attached link test version of file, , i'll explain best can issue.

the file creates plain txt file can used feed data system use. works normally, we've been supplied approximately 15,000 rows of data, , @ random points throughout there hidden characters.

in test file, there's 1 row , it's cell b11 has hidden characters @ beginning , end of value. if put cursor @ end of it, , press backspace key, if nothing has happened, you've deleted 1 of characters.

as far excel concerned, hidden characters question marks, they're not, text stream parse those, doesn't, , instead throws invalid procedure call error.

i've tried using excel's clean formula, i've tried vba equivalent, tried using 'replace', nothing seems recognise characters. excel convinced they're question marks, ascii character call gives me same answer (63), replace doesn't replace them question marks, omits them!

any on this, if it's formula apply appreciated. in interests of data protection data in file fake way, it's nobody's real ni number.

the excel file vba code here

this vba macro run on own or in conjunction clearformatting macro. did strip out rogue unichars sample.

sub strip_rogue_unichars()     dim uc long     cells(11, 1).currentregion         uc = 8000 8390             .replace what:=chrw(uc), replacement:=vbnullstring, lookat:=xlpart             doevents         next uc     end end sub

there's better way , being able restrict scope of unicode characters search , replace speed things up. turning off .enableevents, .screenupdating, etc likewise help. believe calculation @ manual. intentionally left doevents in loop first run several thousand different unichars.


Comments

Post a Comment

Popular posts from this blog

javascript - AngularJS custom datepicker directive -

i make custom datepicker directive custom template. but have no idea how start building it... how include date data directive? i appreciate guide or give me advice work on more precisely. it might you! follow below steps html code sample: <label>birth date</label> <input type="text" ng-model="birthdate" date-options="dateoptions" custom-datepicker/> <hr/> <pre>birthdate = {{birthdate}}</pre> <script type="text/ng-template" id="custom-datepicker.html"> <div class="enhanced-datepicker"> <div class="proxied-field-wrap"> <input type="text" ui-date-format="yy-mm-dd" ng-model="ngmodel" ui-date="dateoptions"/> </div> <label> <button class="btn" type="button"><i class="icon-calendar"></i>
Read more

javascript - jQuery date picker - Disable dates after the selection from the first date picker -

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <title>jquery ui datepicker - default functionality</title> <link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css"> <script src="//code.jquery.com/jquery-1.10.2.js"></script> <script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script> <link rel="stylesheet" href="/resources/demos/style.css"> <script> $(function () { $("#datepicker1").datepicker(); }); </script> <script> $(function () { $("#datepicker2").datepicker(); }); </script> </head> <body> <p>date 1: <input type="text" id="datepicker1"></p> <p>date 2: <input type="t
Read more