修剪功能的Excel IF语句 [英] Excel IF statement for trim function

问题描述

下午好，

在可能的情况下，我希望有一个固定的修整地址.

I would like to have a fixed trim address, when it's possible.

我的地址是:

**阿伯丁伯恩茅斯路15-21号，AB11 6YA **

** 15-21 Bournemouth rd, Aberdeen, AB11 6YA**

我想摆脱邮政编码，并保留这样的地址形式:

I want to get rid of the postcode and keep the address form like this:

阿伯丁伯恩茅斯路15-21号

在这种情况下，我必须使用左侧的TRIM函数，就像我所做的一样.

In this event I have to use the TRIM function from the left, what I did.

我尝试了以下两个公式:

I tried these 2 formulas:

 =LEFT(TRIM(D18),FIND("^",SUBSTITUTE(TRIM(D18)&" "," ","^",4))-2)

和

 =TRIM(LEFT(D18, FIND("~",SUBSTITUTE(D18, " ", "~",5)&"~")))

这给了我结果:

阿伯丁伯恩茅斯路15-21号

阿伯丁伯恩茅斯路15-21号

那很好.我很高兴.

尽管有时我的地址带有前提的自己的名字，例如:

Although sometimes my address comes with the own name of the premise, like this:

21-23 Regis House 15-23 Bournemouth rd，Aberdeen，AB11 6YA

然后问题变得更加复杂，因为我无法应用这些公式(我必须手动更改它们，这是我不想要的).

and then the issue becomes more complicated, as I cannot apply these formulas (I have to change them manually, which I don't want).

由于相同的公式参数，我得到了相应的结果:

In the result of the same formula parameters I am getting accordingly:

21-23 Regis House 15-2

瑞吉斯大厦23号(15-21)

这不好，因为我只需要一个完整的地址，而没有邮政编码.

which is not good, as I need a full address without the postcode only.

我无法手动更改这些公式.最重要的是，当我将它们交换到右侧时，将保留最右边的内容.

I cannot change these formulas manually. On top of that, when I swap them to the right side, then the rightmost stuff is being kept.

我在这里找到了一些东西

I found something here:

组合修剪和if公式

TRIM函数的IF语句，而net试图将其绘制到我的示例中:

the IF statement for TRIM function, and net tried to plot it into my example:

   =IF(NOT(SUM(COUNTIF(A18, "*"&{",",","}&"*"))), TRIM(LEFT(SUBSTITUTE(MID(A18,FIND("|",SUBSTITUTE(A18,"-","|",2))+1,LEN(A18)),"-",REPT(" ",LEN(A18))),LEN(A18))), "")

返回 #VALUE .

我该如何解决?

推荐答案

如果，我们可以假设您的邮政编码始终跟随字符串中的最后一个逗号，您可以使用以下代码提取最后一个逗号之前的所有内容:

IF we can assume your postcode always follows the last comma in your string you can use the below to extract everything before the last comma:

=MID(A1,1,FIND("@",SUBSTITUTE(A1,",","@",LEN(A1)-LEN(SUBSTITUTE(A1,",","")))))

请注意，这还假设 @ 不会出现在您的字符串中.我们本质上是想用一个符号替换目标逗号，该符号对于任何字符串中的所有其他字符都是唯一的

Note this also assumes that @ will not be in your strings anywhere. We essentially want to replace your target comma with a symbol that will be unique to all other characters in any string

如果您愿意使用VBA解决方案，则可以使用下面的UDF获得相同的结果

If you are open to VBA solution, you can use the below UDF to get the same results

Public Function LASTCOMMA(Target As String)

Dim i As Long, Arr
Arr = Split(Target, ",")

For i = LBound(Arr) To (UBound(Arr) - 1)
    LASTCOMMA = LASTCOMMA & Arr(i)
Next i

End Function

这篇关于修剪功能的Excel IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！