在公式中添加分隔符 [英] Appending separators in the formula

问题描述

有人可以帮忙吗?我的A栏包含电话号码，B栏包含国家/地区代码.如果要避免重复，我想删除A列中的国家/地区代码.有人可以帮我吗?

Can someone help on this? I have column A with telephone number and Column B with country code. I want to remove the country code in column A if it is there to avoid duplication. Can someone help me?

A列1234567 B列1 C列1234567

Column A 1234567 Column B 1 Column C 1234567

C列应具有公式来检查单元格值A的开头是否已存在1，如果是，它将删除它，但如果没有，则将B列值添加到其中.下面是另一种情况:A列234567列B列1 C列1234567

Column C should have the formula to check if 1 is already in the beginning of the cell value A, if yes, it'll remove it, but if not, column B value will be added to it. Another scenario is below: Column A 234567 Column B 1 Column C 1234567

此外，我想在此公式中在国家/地区代码之后以及国家/地区代码的三位数之后(在第1列的基础上加上已删除的国家/地区代码)添加分隔符.

Also, I would like to add separators in this formula after the country code, and after three digits of the country code based on column 1 with removed country code in it.

如何在此公式中附加它?

How to append it in this formula?

= IF(B1 = --LEFT(A1，LEN(B1))，A1，-(B1& A1))

=IF(B1 = --LEFT(A1, LEN(B1)),A1,--(B1&A1))

感谢您的回答！

推荐答案

据我了解，您描述的内容可能是这样的:

As far as I understand what you describe, you may be after something like this:

="+"&TEXT(B1,"0")&"-"&
IF(LEFT(TEXT(A1,"0"),LEN(TEXT(B1,"0")))=TEXT(B1,"0"),
   MID(TEXT(A1,"0"),LEN(TEXT(B1,"0"))+1,99),
   A1)

关于基于第1列的国家/地区代码的三位数后"的声明.尚不清楚.该公式为您提供了在国家/地区代码之前和之后添加字符的工具，同时可以消除国家/地区代码重复.

The statement about "after three digits of the country code based on column 1 " is unclear. This formula gives you tools to add characters before and after the country code, while it removes country code duplication.

在评论后进行编辑:您实际上并没有对此进行描述，至少不清楚，但是您似乎希望在国家/地区代码之后以及电话号码的前三位数字之后输入竖线.实现这一目标的公式是

edit after comments You don't really describe it, at least not clearly, but it looks like you want a vertical bar after the country code and again after the first three digits of the phone number. The formula to achieve that is

=TEXT(B1,"0")&"|"&
REPLACE(IF(LEFT(TEXT(A1,"0"),LEN(TEXT(B1,"0")))=TEXT(B1,"0"),
   MID(TEXT(A1,"0"),LEN(TEXT(B1,"0"))+1,99),
   A1),4,1,"|")

将您可能需要的其他内容拼接在一起后的另一种编辑方式， :如果B列可能包含1或63之类的数字，也可能包含以a开头的文本国家/地区代码，然后加上空格和其他内容(例如63 2)，则可以使用此公式

another edit after stitching together what else you might want but don't clearly describe: If column B can possibly contain either numbers like 1 or 63 but also text that starts with a country code, followed by a space and some other stuff, like 63 2, then you can use this formula

=LEFT(TEXT(B2,"0"),FIND(" ",TEXT(B2,"0")&" ")-1)&"|"&
REPLACE(IF(LEFT(TEXT(A2,"0"),LEN(TEXT(B2,"0")))=TEXT(B2,"0"),
   MID(TEXT(A2,"0"),LEN(TEXT(B2,"0"))+1,99),
   A2),4,1,"|")

这篇关于在公式中添加分隔符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！