数据库trim函数_使用TRIM和SUBSTITUTE清除Excel数据

数据库trim函数

You have two Excel lists, and you’re trying to find the items that are in both lists. You know there are matching items, but if your VLOOKUP formulas can't find any matches, you might need to clean Excel data with TRIM and SUBSTITUTE.

您有两个Excel列表，并且您正在尝试查找两个列表中的项目。 您知道有匹配项，但是如果您的VLOOKUP公式找不到任何匹配项，则可能需要使用TRIM和SUBSTITUTE清除Excel数据。

VLOOKUP找不到匹配项 (VLOOKUP Cannot Find Matches)

Here's an example of this problem. In this screenshot, the tiptech.html page is in both lists, but the VLOOKUP formula in cell C2 can’t find it. It returns and #N/A error.

这是此问题的示例。 在此屏幕快照中，tiptech.html页面在两个列表中，但是单元格C2中的VLOOKUP公式找不到它。 返回并显示＃N / A错误。

找出差异 (Spot the Differences)

Working with Excel data can be like one of those “Spot the Difference” puzzles. What’s different between list A and list B?

使用Excel数据就像是“ 发现差异 ”难题之一。 列表A和列表B有什么区别？

If you’re lucky, the differences are obvious, like the forward slash in column B, and no leading slash in column E. Other times, it’s tougher to find the differences.

如果幸运的话，区别是显而易见的，例如B列中的正斜杠，而E列中没有前斜杠。有时，查找差异更困难。

A common problem, when trying to match data, is items with leading or trailing spaces. You can’t see them on the screen, but after you’ve encountered them a few times, you learn to check for them.

尝试匹配数据时，常见的问题是带有前导或尾随空格的项目。 您无法在屏幕上看到它们，但是几次遇到它们之后，您就会学会检查它们。

检查长度 (Check the Length)

The LEN function is a great help if you suspect there are hidden space characters in a cell.

如果您怀疑单元格中有隐藏的空格字符，则LEN功能会很有用。

If you use the LEN function to compare the length of the text in cell B2 and E4, you’d see that there are 2 additional characters in cell B2. One character is the forward slash, and the other character is a trailing space.

如果使用LEN函数比较单元格B2和E4中文本的长度，您会看到单元格B2中还有2个附加字符。 一个字符为正斜杠，另一个字符为结尾空格。

删除前导和尾随空格 (Remove Leading and Trailing Spaces)

If you want to use a VLOOKUP or MATCH to find column B items, in column E, you’ll have to get rid of any extra characters.

如果要使用VLOOKUP或MATCH查找B列中的项目，则必须删除E列中的所有多余字符。

First, you can deal with the spaces, by using the TRIM function.

首先，可以使用TRIM函数处理空格。

To return the text from cell B2, without any leading or trailing characters, you’d use this formula:

要从单元格B2返回文本，且不包含任何前导或尾随字符，请使用以下公式：

=TRIM(B2)

= TRIM(B2)

If you use a formula in cell B12 to check the length of the trimmed text, it’s now 13 characters, instead of 14. The trailing space has been removed.

如果您在单元格B12中使用公式来检查修剪的文本的长度，则它现在是13个字符，而不是14个字符。尾部空格已被删除。

删除特定字符 (Remove a Specific Character)

Next, you can use the SUBSTITUTE function to remove the forward slash from the text in cell B2.

接下来，您可以使用SUBSTITUTE函数从单元格B2中的文本中删除正斜杠。

=SUBSTITUTE(B2,”/”,””)

= SUBSTITUTE(B2，“ /”，“”)

• The first argument, B2, is the cell that contains the text value.

  第一个参数B2是包含文本值的单元格。

• The second argument, “/”, is the old text, that you want to replace.

  第二个参数“ /”是您要替换的旧文本。

• The third argument, “”, is the new text, that replaces the old text. If you want to remove the old text, without inserting new text, use “” as an empty string, as we did here.

  第三个参数“”是新文本，它将替换旧文本。 如果要删除旧文本而不插入新文本，请像在此一样使用“”作为空字符串。

结合功能 (Combine the Functions)

The TRIM and SUBSTITUTE functions work well separately, and you can combine them, to remove the extra spaces and the forward slash. The order doesn’t matter, so you can use either:

TRIM和SUBSTITUTE函数可以分别很好地工作，并且可以将它们组合使用，以消除多余的空格和正斜杠。 顺序无关紧要，因此您可以使用以下任一方法：

=TRIM(SUBSTITUTE(B2,"/",""))

= TRIM(SUBSTITUTE(B2，“ /”，“”))

or:

要么：

=SUBSTITUTE(TRIM(B2),"/","")

= SUBSTITUTE(TRIM(B2)，“ /”，“”)

添加到VLOOKUP (Add to the VLOOKUP)

Now that you know the TRIM and SUBSTITUTE functions will clean up the text in column B, you can add those functions to the VLOOKUP formula.

现在您知道TRIM和SUBSTITUTE函数将清除B列中的文本，您可以将这些函数添加到VLOOKUP公式中。

Instead of using B2 in the VLOOKUP:

而不是在VLOOKUP中使用B2：

=VLOOKUP(B2,$E$2:$F$8,2,FALSE)

= VLOOKUP( B2 ，$ E $ 2：$ F $ 8,2，FALSE)

use the TRIM and SUBSTITUTE functions:

使用TRIM和SUBSTITUTE函数：

=VLOOKUP(TRIM(SUBSTITUTE(B2,"/","")),$E$2:$F$8,2,FALSE)

= VLOOKUP( TRIM(SUBSTITUTE(B2，“ /”，“”)) ，$ E $ 2：$ F $ 8,2，FALSE)

找到一个匹配项 (A Match is Found)

After you change the VLOOKUP formula, to include TRIM and SUBSTITUTE, it works correctly.

更改VLOOKUP公式以包括TRIM和SUBSTITUTE后，它可以正常工作。

A match for the cleaned up text is found in column E, and in the Update column, cell C2 is filled in with the correct date.

在E列中找到了与清除的文本匹配的内容，在Update列中，用正确的日期填充了单元格C2。

对VLOOKUP公式进行故障排除 (Troubleshoot a VLOOKUP Formula)

If TRIM and SUBSTITUTE don’t solve your VLOOKUP problems, there are a few more suggestions on the Contextures website: Troubleshoot the VLOOKUP formula.

如果TRIM和SUBSTITUTE不能解决您的VLOOKUP问题，则在Contextures网站上还有其他一些建议： 对VLOOKUP公式进行故障排除 。

On that page, you'll also find examples of using the IF function or IFERROR function to deal with VLOOKUP function errors.

在该页面上，您还将找到使用IF函数或IFERROR函数处理VLOOKUP函数错误的示例。

翻译自: https://contexturesblog.com/archives/2010/02/17/clean-excel-data-with-trim-and-substitute/

数据库trim函数