从文本中删除不必要的字符 [英] Remove unnecessary characters from a text
问题描述
我有一长串手机号码,如(0912)-6862723。我想上传一个这个手机号码列表的列表,该列表应该没有额外的字符(或 - 。手机号码应该是这样的09126862723.我尝试过CLEAN()功能,特别粘贴
和其他一些方法但我没有得到我的结果。如何将此文本(0912)-6862723转换为09126862723。
I have a long list of mobile numbers that are like (0912)-6862723. I want to upload a list of this mobile number list that should have no extra character like ( or -. The mobile number should be like this 09126862723. I tried CLEAN() function, special paste and some other ways but I haven't get my result. How can I convert this text (0912)-6862723 to 09126862723.
问候,
Karim Vaziri问候,
Karim Vaziri Regards,
推荐答案
使用单元格A2中的电话号码,您可以使用以下公式:
With a phone number in cell A2, you can use this formula:
= SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(","")," )","")," - ","")
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"(",""),")",""),"-","")
此公式可以填写。
或者,选择整个范围并运行以下宏:
Alternatively, select the entire range and run the following macro:
Sub CleanPhoneNumbers()
Dim cel As Range
Application.ScreenUpdating = False
Selection.NumberFormat =" @"
&nbs磷;  对于每个cel选择
cel.Value =替换(替换(替换(cel.Value,"(",""),")","")," - ","")
下一个bac
Application.ScreenUpdating = True
End Sub
Sub CleanPhoneNumbers()
Dim cel As Range
Application.ScreenUpdating = False
Selection.NumberFormat = "@"
For Each cel In Selection
cel.Value = Replace(Replace(Replace(cel.Value, "(", ""), ")", ""), "-", "")
Next cel
Application.ScreenUpdating = True
End Sub
这篇关于从文本中删除不必要的字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!