检测并替换LSEP字符(& #xB;) [英] Detecting and replacing LSEP character ()
问题描述
我很难接受一个特殊字符(Unicode U + 2028，在XML中显示为& #xB，在Chrome和Excel单元格文本中，显示为L SEP的虚线正方形).这是这一个:
I'm having a hard time with a special character (Unicode U+2028, in XML it shows as , and in Chrome and Excel cell text, it shows as a L SEP in a dashed square). It's this one:
我制作了一个宏，该宏可以打开许多网页，并将其部分内容复制到Excel工作表中的单独单元格中.在大多数情况下，它的性能都不错，但是最近我需要研究其网页的内容发行商之一开始使用LSEP字符(我不知道为什么，也许他正在使用新的文本或HTML编辑器；我与任何人都没有联系其中的).
I've made a macro which opens many webpages, copying part of their contents into separate cells in an excel sheet. Most of the time it performs well, but recently one of the content distributors whose webpage I need to research started using the LSEP character (I don't know why, maybe he's using a new text or HTML editor; I have no contact with any of them).
结果，Excel无法打开xlsx，从而在/xl/sharedStrings.xml文件中返回XML错误.当我将MySheet.xlsx重命名为MySheet.xlsx.zip并打开问题文件时，我发现错误是由那个可怕的字符引起的.手动地，我删除了LSEP字符，替换了sharedStrings.xml，将MySheet.xlsx.zip重命名为MySheet.xlsx并打开了所有内容.
As a result, Excel can't open the xlsx, returning an XML error in /xl/sharedStrings.xml file. When I renamed MySheet.xlsx to MySheet.xlsx.zip and opened the troubling file, I've discovered the error was caused by that dreadful character. Manually, I removed the LSEP characters, replaced sharedStrings.xml, renamed MySheet.xlsx.zip to MySheet.xlsx and it opened all right.
解决了我的问题后，下一步是将一些代码编写到我的宏中，以自动将LSEP字符替换为空.作为一个特殊角色，我的方法是尝试通过 ASC 函数引用它:
Having figured out my problem, the next step was to write some piece of code into my macro, to automatically replace the LSEP character with nothing. Being a special character, my approach was to try referring it by ASC function:
Asc(ActiveCell.Formula)
这是我得到的第一个怪异的东西:上面的代码用于返回 63 ，但是有一次它返回了 93 相同的单元格内容！尝试 ActiveCell.Text 可以得到相同的结果.
Here was the first spooky thing I got: the code above use to return 63, but one time it returned 93 to the same cell content! Trying ActiveCell.Text got me the same result.
无论如何，我已经尝试了正常的 Replace :
Anyway, I've tried a normal Replace:
Replace(ActiveCell.Formula, Chr(63), "")
Replace 返回完全相同的字符串，并且使用臭名昭著的LSEP(尝试 ActiveCell.Formula 和 ActiveCell.Text 都返回了相同；因此我尝试使用 Chr(93)而不是 Chr(63)进行相同操作，但没有成功).
The Replace returned exactly the same string, with the infamous LSEP (tried both ActiveCell.Formula and ActiveCell.Text, both returned the same; so I've tried the same with Chr(93) instead of Chr(63), with no sucess).
然后，我试图找到臭名昭著的鬼sneak忍者海盗LSEP角色，所以我尝试:
Then, I've tried to locate the infamous sneaky ninja pirate LSEP character, so I tried:
InStr(1, ActiveCell.Formula, Asc(63)) 'Or Asc(93), again the same results
返回值为 0 .因此，我震惊但没有被打败，我擦了一下魔幻灯，然后问了VBA天才:
The return was 0. So, astounded but not defeated, I rubbed my magical lamp and asked this to VBA genius:
MsgBox "Where's Chr(63)? " & InStr(1, ActiveCell.Formula, Chr(63)) & vbCrLf & _
"Where's Chr(93)? " & InStr(1, ActiveCell.Formula, Chr(93)) & vbCrLf & _
"Asc(Right(ActiveCell.Formula, 1)): " & Asc(Right(ActiveCell.Formula, 1))
作为回应，VBA告诉我第一行和第二行 0 (例如没有 Chr(63)或 Chr(93))，同时告诉我同一字符串的最右边字符是Ascii 63！
In response, VBA tells me 0 to first and second lines (like there isn't Chr(63) nor Chr(93)), at the same time telling me the rightmost character of the same string is the Ascii 63!
这怎么可能?有人有主意吗?我完全迷路了.如何在我的代码中提前发现并替换这个隐身的忍者海盗LSEP角色?
How is this possible? Does anyone have an idea? I'm totally lost. How can I detect and replace this stealthy ninja pirate LSEP character in advance, in my code?
感谢您的时间！
推荐答案
Asc()& Chr()仅适用于ANSI字符.
Asc() & Chr() only work for ANSI characters.
请改为使用Unicode感知版本: Chrw $(& h2028)是 LSep 字符，其对应字符是 AscW().
Instead use the Unicode aware versions: Chrw$(&h2028) is the LSep character, its counterpart is AscW().
这篇关于检测并替换LSEP字符(& #xB;)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
