Cari Blog Ini

01 Maret 2013

How To Remove Invalid Character Like Single Quotes


Apply To:
Microsoft Excel 2010

Problem: 
Want to remove Invalid Character ' ( single quotes ) in All Rows. 



Example,
filename : "Replace Invalid Character in Format Comma Delimited - Source.xls"
To download the file, please click this link: 
https://docs.google.com/file/d/0B6a5vK3IVP_0c0VHMERyMDBpb1E/edit?usp=sharing



Solution :

00. Open filename "Replace Invalid Character in Format Comma Delimited - Source.xls"

01. Block all rows,
Example from range "B2" to "B21"

02. Press CTR + H to open Window "Find and Replace"

03. In Window "Find and Replace", for "Find what", please fill with = ' (single quotes)

04. In Window "Find and Replace", 
for "Replace with", please press in keyboard for = ALT + 39



05. Choose "Replace All" then choose "OK"



06. Choose "Close"




07. Check the results

08. Then for make sure, 
save the file,
from filename "Replace Invalid Character in Format Comma Delimited - Source.xls" 
to new file,
example name 
"Replace Invalid Character in Format Comma Delimited - Results CSV.csv"

To download the file, please click this link: 




09. Close the filename :  
"Replace Invalid Character in Format Comma Delimited - Results CSV.csv"

10. Next, Open again the filename : 
"Replace Invalid Character in Format Comma Delimited - Results CSV.csv", 
using Wordpad



11. The column "Original" is valid. There is no again for invalid charater like ' (single quotes)



Source: 
* http://www.mrexcel.com/forum/excel-questions/539437-number-stored-text-converted-number-when-save-csv.html
* http://www.theasciicode.com.ar/extended-ascii-code/non-breaking-space-no-break-space-ascii-code-255.html