具有某些不可见字符的Excel单元格 [英] Excel cell having some invisible character

问题描述

我正要销毁计算机.自2个小时以来，我预期完成的任务将需要几分钟.我正在尝试在两个不同的excel工作表之间进行vlookup，但是它总是返回#N/A.因此，我拿了一个单元格并开始对其进行测试，我发现问题出在查找工作簿(第二个工作簿)上.单元格中的值是一串长度为9的字母.但是，当我使用= len(A1)公式时，它将显示10个字符.所以我用了trim，它仍然显示10，然后我用了这篇文章:

I am in the verge of destroying my computer. A task that I anticipated will take a few minutes I am sitting on it since 2 hours. I was trying vlookup between two different excel sheets but it always returned #N/A. So I took one cell and started testing it, I found out the problem is on the lookup workbook (second workbook). The value in the cell is a string of alphabets of length 9. But when I use the =len(A1) formula it shows 10 characters. So I used trim, still it shows 10, then I used this post:

问题通常是一个不间断的空格-CHAR(160)-特别是来自Web文本源的-CLEAN无法删除该空格，因此我将进一步尝试并尝试使用类似这样的公式来替换任何内容带有标准空格的不间断空格

Quite often the issue is a non-breaking space - CHAR(160) - especially from Web text sources -that CLEAN can't remove, so I would go a step further than this and try a formula like this which replaces any non-breaking spaces with a standard one

= TRIM(CLEAN(SUBSTITUTE(A1，CHAR(160)，")))

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

罗恩·德·布鲁因(Ron de Bruin)在此处提供了有关清洁数据提示的精彩文章

Ron de Bruin has an excellent post on tips for cleaning data here

您还可以通过以下方式直接删除CHAR(160)，而无需解决方案公式:

You can also remove the CHAR(160) directly without a workaround formula by

编辑....替换您选择的数据， 在查找保持ALT的内容"中，使用数字键盘输入0160 将替换为"保留为空白，然后选择全部替换"

Edit .... Replace your selected data, in Find What hold ALT and type 0160 using the numeric keypad Leave Replace With as blank and select Replace All

它仍然显示10个字符，而不是9个字符.请帮助

Still it shows 10 characters, instead of 9. Please HELP

推荐答案

这是一种简单的方法来找出您的角色.

Here is an easy way to find out what your characters are.

将包含10个字符的单元格复制到单元格A1上的新工作表中.

Copy your cell with the 10 characters to a new sheet on cell A1.

选择单元格B1:B10，然后单击工作表顶部的公式栏，然后粘贴以下公式:

Select cells B1:B10 and click on the Formula Bar at the top of the worksheet and paste this formula:

=MID(A1,ROW(OFFSET($A$1,,,LEN(A1))),1)

这是一个数组公式，必须通过 Ctrl + Shift + Enter 进行确认.

This is an array formula and must be confirmed with Ctrl+Shift+Enter.

现在...在选定的单元格中，每个单元格应该显示一个字符.其中之一很可能看起来像空白单元格，但事实并非如此.

Now... in the selected cells you should see one character per cell. One of these will most likely LOOK like a blank cell, but it is not.

现在，选择单元格C1:C10，然后再次单击编辑栏.这次，粘贴此公式:

Now, select cells C1:C10 and click on the Formula Bar again. This time, paste this formula:

=CODE(B1)

您将以不同的方式确认此公式.同时按下Control键和Enter键.

You will confirm this formula differently. Press Control and Enter at the same time.

在C列中，您现在将在B列中看到每个字符的字符代码.看起来空白的单元格的代码是什么?

In column C, you will now see the character codes for each of the characters in column B. What is the code for the cell that looks blank?

这篇关于具有某些不可见字符的Excel单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！