Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Преобразование тел номеров в один формат
 
#1
05.07.2017 19:41:39
Все привет!

подскажите пожалуйста как с помощью формул ( скриптом или как то по-другому (кроме варианта с поиск/замена)) преобразовать номера из различных форматов в один, например имеется список номеров
         
7 (923)   485-23-28
89237273418
     7 (929) 973-74-24
     +7(913)   759-44-42
     89513663757
     89050878259
     89857226223
     7 (951) 366-37-57
его необходимо преобразовать в
               
89234852328
89237273418
     89299737424
     89137594442
     89513663757
     89050878259
     89857226223
     89513663757
в приложении пример файла - номера будут постоянно добавляться


спасибо
 
 
 
#2
05.07.2017 19:49:34
sergexcel, привет. Можно так:
=ЕСЛИОШИБКА(СЦЕПИТЬ("8";ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(C2;НАЙТИ("(";C2)+1;99);")";"");"-";"");" ";""));F2)
Или так (на один ПОДСТАВИТЬ короче)
=ЕСЛИОШИБКА("8"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(C2;НАЙТИ("(";C2)+1;99);") ";"");"-";"");F2)
Изменено: Bema - 05.07.2017 20:00:39
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#3
05.07.2017 20:21:33
UDF
Код
Public Function iPhone(cell As Range) As String
 Dim re As Object
 Dim tempString
    Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(-|\s|\+|\(|\))"
    re.Global = True
    re.IgnoreCase = True
      tempString = re.Replace(cell, "")
   If Len(tempString) = 11 Then
     If (Left(tempString, 1) = "8") Then
       iPhone = tempString
     Else
       iPhone = "8" + Mid(tempString, 1)
     End If
   Else
     iPhone = "не та разрядность номера"
   End If
End Function
 
 
 
#4
05.07.2017 20:29:07
Мой примитивнейший вариант - "до кучи"!
 
 
 
#5
06.07.2017 10:12:08
Bema,
=ЕСЛИОШИБКА("8"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(C2;НАЙТИ("(";C2)+1;99);") ";"");"-";"");F2)
почему вы ссылаетесь в "еслиошибка" на F2? Значение в F2 получить надо - в файле я указал это как пример - вид к которому надо привести

решения с макросами не работают на mac версии (
 
 
 
#6
06.07.2017 10:15:22
Ошибся. Сошлитесь на ячейку C2.
PS. На логику формулы это не влияет.
Изменено: Bema - 06.07.2017 10:34:55
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#7
06.07.2017 11:51:32
Bema,
все работает, супер!
спасибо большое всем за ответы!!!
 
 
 
#8
10.07.2017 10:27:48
Bema, появились номера которые заносятся в таком формате "79134481484", формула такой номер преобразует в "79134481484" - то есть не в "89134481484", подскажите пожалуйта, что надо поменять в формуле
=ЕСЛИОШИБКА("8"&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(C2;НАЙТИ("(";C2)+1;99);") ";"");"-";"");C2)

чтобы номер всегда начинался с "8"

спасибо
 
 
 
#9
10.07.2017 10:39:13
sergexcel, сейчас не за компьютером. Позже отвечу.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#10
10.07.2017 11:00:26
А UDF, которую я вам написал, вы принципиально игнорируете?
 
 
 
#11
10.07.2017 11:02:43
=8&ЕСЛИОШИБКА(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(C2;НАЙТИ("(";C2)+1;99);") ";);"-";);ПСТР(C2;2;10))
 
 
 
#12
10.07.2017 12:22:13
to Kuzmich
"А UDF, которую я вам написал, вы принципиально игнорируете?"
У меня не получилось реализовать по макросу, который вы выслали, может я что то сделал не так (
Добавил функцию в модуль, потом пишу =iphone (C2)  - выдается "#value!"  (C2 - ячейка с номером преобразования)
(excel на MAC)

дополнение к ранее высланному ответу
реализовал на экcеле из винодовс, функция работает, но не всегда корректно
пример ошибки
     

+7(913) 749-44-42        --->   879137494442

сделал на маке, не работает, может что не так делаю

http://joxi.ru/82QLlpji1jZDkm  - то что сделал, функция находится, но не работает

Изменено: sergexcel - 10.07.2017 17:24:44
 
 
 
#13
10.07.2017 12:25:56
Цитата
Добавил функцию в модуль, потом пишу =iphone (C2)
Код в стандартный модуль, вызов =iPhone(C2)
Уловили разницу
 
 
 
Страницы: 1
Наверх