Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Приведение телефонных номеров к единому формату - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Приведение телефонных номеров к единому формату (Формулы/Formulas)
Приведение телефонных номеров к единому формату
Garik007 Дата: Среда, 04.05.2016, 11:25 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день. Необходимо привести большое количество (более 30 тыс.) телефонных номеров к единому 10-значному формату. Пример в прилагаемом файле.
К сообщению приложен файл: 8891864.xlsx(8Kb)
 
Ответить
СообщениеДобрый день. Необходимо привести большое количество (более 30 тыс.) телефонных номеров к единому 10-значному формату. Пример в прилагаемом файле.

Автор - Garik007
Дата добавления - 04.05.2016 в 11:25
buchlotnik Дата: Среда, 04.05.2016, 11:36 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
можно UDF-кой [vba]
Код
Function tel$(t$)
    Dim txt$
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        txt = .Replace(t, "")
    End With
    If Len(txt) < 7 Then
        tel = "неправильный номер"
    Else
        tel = "8495" & Right(txt, 7)
    End If
End Function
[/vba] ну или её в макрос засунуть
К сообщению приложен файл: 8891864.xls(33Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщениеможно UDF-кой [vba]
Код
Function tel$(t$)
    Dim txt$
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        txt = .Replace(t, "")
    End With
    If Len(txt) < 7 Then
        tel = "неправильный номер"
    Else
        tel = "8495" & Right(txt, 7)
    End If
End Function
[/vba] ну или её в макрос засунуть

Автор - buchlotnik
Дата добавления - 04.05.2016 в 11:36
Garik007 Дата: Среда, 04.05.2016, 11:51 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Работает, спасибо, но сейчас вспомнил про номера мобильных телефонов. Если номер, например, 9031234567 то его нужно приводить к формату 89031234567, т.е. если в номере 9 или 10 цифр, то их нужно оставлять как есть.
 
Ответить
СообщениеРаботает, спасибо, но сейчас вспомнил про номера мобильных телефонов. Если номер, например, 9031234567 то его нужно приводить к формату 89031234567, т.е. если в номере 9 или 10 цифр, то их нужно оставлять как есть.

Автор - Garik007
Дата добавления - 04.05.2016 в 11:51
buchlotnik Дата: Среда, 04.05.2016, 12:03 | Сообщение № 4
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
тогда так [vba]
Код
Function tel$(t$)
    Dim txt$
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        txt = .Replace(t, "")
    End With
    Select Case Len(txt)
          Case Is =  7: tel = "8495" & txt
          Case Is = 10: tel = 8 & txt
          Case Is = 11: tel = txt
          Case Else: tel = "неправильный номер"
    End Select
End Function
[/vba]
и макрос типа: [vba]
Код
Sub re_tel()
    Dim i%
    For i = 1 To [a1].End(xlDown).Row
        Cells(i, 2) = tel(Cells(i, 1))
    Next
End Sub
[/vba]
К сообщению приложен файл: 8318914.xlsm(15Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Среда, 04.05.2016, 12:29
 
Ответить
Сообщениетогда так [vba]
Код
Function tel$(t$)
    Dim txt$
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\D"
        txt = .Replace(t, "")
    End With
    Select Case Len(txt)
          Case Is =  7: tel = "8495" & txt
          Case Is = 10: tel = 8 & txt
          Case Is = 11: tel = txt
          Case Else: tel = "неправильный номер"
    End Select
End Function
[/vba]
и макрос типа: [vba]
Код
Sub re_tel()
    Dim i%
    For i = 1 To [a1].End(xlDown).Row
        Cells(i, 2) = tel(Cells(i, 1))
    Next
End Sub
[/vba]

Автор - buchlotnik
Дата добавления - 04.05.2016 в 12:03
_Boroda_ Дата: Среда, 04.05.2016, 12:08 | Сообщение № 5
Группа: Модераторы
Ранг: Экселист
Сообщений: 9354
Репутация: 3924 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Не столь универсальный вариант формулой
Код
=--(8&ПРАВБ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;"-";);")";);"моб.";);10))
и форматом
[<=79999999999]"неправильный номер";#(###) ###-##-##
К сообщению приложен файл: 8891864_1.xlsx(9Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеНе столь универсальный вариант формулой
Код
=--(8&ПРАВБ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A3;"-";);")";);"моб.";);10))
и форматом
[<=79999999999]"неправильный номер";#(###) ###-##-##

Автор - _Boroda_
Дата добавления - 04.05.2016 в 12:08
Garik007 Дата: Среда, 04.05.2016, 12:18 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, спасибо большое, все работает как нужно, хотя мне больше по вкусу формулами.
_Boroda_, спасибо большое за формулу, жалко что не совсем универсальная, т.к. в исходном номере может быть любое количество букв, русских или английских, а не только "моб.".


Сообщение отредактировал Garik007 - Среда, 04.05.2016, 12:29
 
Ответить
Сообщениеbuchlotnik, спасибо большое, все работает как нужно, хотя мне больше по вкусу формулами.
_Boroda_, спасибо большое за формулу, жалко что не совсем универсальная, т.к. в исходном номере может быть любое количество букв, русских или английских, а не только "моб.".

Автор - Garik007
Дата добавления - 04.05.2016 в 12:18
buchlotnik Дата: Среда, 04.05.2016, 12:58 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
мне больше по вкусу формулами
ну можно тогда вот так извратиться B)
Код
=ТЕКСТ(--8&ПРАВСИМВ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВСИМВ(A3;ПРОСМОТР("a";ПСТР(A3;СТРОКА($A$1:$A$25);1);СТРОКА($A$1:$A$25))-1);")";);"(";);"-";);" ";);10);"[<=79999999999]неправильный\ но\мер;0")
К сообщению приложен файл: 8891864-1-.xlsx(9Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru


Сообщение отредактировал buchlotnik - Среда, 04.05.2016, 13:25
 
Ответить
Сообщение
Цитата
мне больше по вкусу формулами
ну можно тогда вот так извратиться B)
Код
=ТЕКСТ(--8&ПРАВСИМВ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВСИМВ(A3;ПРОСМОТР("a";ПСТР(A3;СТРОКА($A$1:$A$25);1);СТРОКА($A$1:$A$25))-1);")";);"(";);"-";);" ";);10);"[<=79999999999]неправильный\ но\мер;0")

Автор - buchlotnik
Дата добавления - 04.05.2016 в 12:58
AlexM Дата: Среда, 04.05.2016, 13:45 | Сообщение № 8
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3053
Репутация: 740 ±
Замечаний: 0% ±

формула массива
Код
=--(8495&ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВБ(A3;ПОИСКПОЗ(1=1;ПСТР(A3&"ё";СТРОКА($1:$20);1)>"z";)-1);"-";);")";);"(";);7))
плюс формат ячейки [<80000000000]"неправильный номер";0
К сообщению приложен файл: 7630698.xls(30Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщениеформула массива
Код
=--(8495&ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВБ(A3;ПОИСКПОЗ(1=1;ПСТР(A3&"ё";СТРОКА($1:$20);1)>"z";)-1);"-";);")";);"(";);7))
плюс формат ячейки [<80000000000]"неправильный номер";0

Автор - AlexM
Дата добавления - 04.05.2016 в 13:45
Garik007 Дата: Среда, 04.05.2016, 14:06 | Сообщение № 9
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, почему то когда меняешь количество СТРОКА($A$1:$A$25);1);СТРОКА($A$1:$A$25), например на СТРОКА($A$1:$A$2500);1);СТРОКА($A$1:$A$2500), формула не убирает буквы из номеров.
[moder]
когда меняешь количество

А количество ЧЕГО Вы меняете? И зачем?[/moder]
AlexM, к сожалению не учитываются мобильные телефоны, везде единый формат 8495.

Пока что макросом получилось лучше формул, всем огромное спасибо.

P.S. При обработке файлов выяснилось что в некоторых ячейках указано больше одного номера, причем номера идут как через пробел, так и через другие символы, но это уже отдельный разговор с теми кто делал этот телефонный список.
 
Ответить
Сообщениеbuchlotnik, почему то когда меняешь количество СТРОКА($A$1:$A$25);1);СТРОКА($A$1:$A$25), например на СТРОКА($A$1:$A$2500);1);СТРОКА($A$1:$A$2500), формула не убирает буквы из номеров.
[moder]
когда меняешь количество

А количество ЧЕГО Вы меняете? И зачем?[/moder]
AlexM, к сожалению не учитываются мобильные телефоны, везде единый формат 8495.

Пока что макросом получилось лучше формул, всем огромное спасибо.

P.S. При обработке файлов выяснилось что в некоторых ячейках указано больше одного номера, причем номера идут как через пробел, так и через другие символы, но это уже отдельный разговор с теми кто делал этот телефонный список.

Автор - Garik007
Дата добавления - 04.05.2016 в 14:06
buchlotnik Дата: Среда, 04.05.2016, 14:41 | Сообщение № 10
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
Цитата
некоторых ячейках указано больше одного номера

Garik007, а можно пример данных?


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщение
Цитата
некоторых ячейках указано больше одного номера

Garik007, а можно пример данных?

Автор - buchlotnik
Дата добавления - 04.05.2016 в 14:41
Garik007 Дата: Среда, 04.05.2016, 14:55 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
А количество ЧЕГО Вы меняете? И зачем?

Не разобрался, думал что должен быть выделен весь диапазон в котором имеются номера.
buchlotnik, пример некорректных результатов выделен цветом.
К сообщению приложен файл: _8891864-1-1.xlsx(10Kb)
 
Ответить
Сообщение
А количество ЧЕГО Вы меняете? И зачем?

Не разобрался, думал что должен быть выделен весь диапазон в котором имеются номера.
buchlotnik, пример некорректных результатов выделен цветом.

Автор - Garik007
Дата добавления - 04.05.2016 в 14:55
Garik007 Дата: Среда, 04.05.2016, 15:04 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, пример, когда в ячейке указано несколько номеров. В ячейке А4, когда указывали второй номер, видимо указали его через пробел и он преобразовался в числовой формат со всеми вытекающими.
К сообщению приложен файл: 3283593.xlsx(11Kb)
 
Ответить
Сообщениеbuchlotnik, пример, когда в ячейке указано несколько номеров. В ячейке А4, когда указывали второй номер, видимо указали его через пробел и он преобразовался в числовой формат со всеми вытекающими.

Автор - Garik007
Дата добавления - 04.05.2016 в 15:04
buchlotnik Дата: Среда, 04.05.2016, 15:25 | Сообщение № 13
Группа: Друзья
Ранг: Старожил
Сообщений: 2049
Репутация: 613 ±
Замечаний: 0% ±

2010, 2013, 2016 RUS / ENG
формулу подкорректировал
Код
=ТЕКСТ(--8&ПРАВСИМВ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВСИМВ(A14;ПРОСМОТР(9^9;ПСТР(A14;СТРОКА($A$1:$A$25);1)+1;СТРОКА($A$1:$A$25)));")";"");"(";"");"-";"");" ";"");10);"[<=79999999999]неправильный\ но\мер;0")
К сообщению приложен файл: uted_f.xlsx(10Kb)


платная помощь:
ЯД: 410012595572239; WM: 311017577133
buchlotnik@mail.ru
 
Ответить
Сообщениеформулу подкорректировал
Код
=ТЕКСТ(--8&ПРАВСИМВ(495&ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВСИМВ(A14;ПРОСМОТР(9^9;ПСТР(A14;СТРОКА($A$1:$A$25);1)+1;СТРОКА($A$1:$A$25)));")";"");"(";"");"-";"");" ";"");10);"[<=79999999999]неправильный\ но\мер;0")

Автор - buchlotnik
Дата добавления - 04.05.2016 в 15:25
Garik007 Дата: Среда, 04.05.2016, 15:53 | Сообщение № 14
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
buchlotnik, вроде работает, спасибо.
 
Ответить
Сообщениеbuchlotnik, вроде работает, спасибо.

Автор - Garik007
Дата добавления - 04.05.2016 в 15:53
AlexM Дата: Среда, 04.05.2016, 16:03 | Сообщение № 15
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3053
Репутация: 740 ±
Замечаний: 0% ±

Еще вариант
[moder]А вот не полезу в файле формулу смотреть! Лениво качать.
К сообщению приложен файл: 8891864_1.xls(30Kb)


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал _Boroda_ - Среда, 04.05.2016, 16:09
 
Ответить
СообщениеЕще вариант
[moder]А вот не полезу в файле формулу смотреть! Лениво качать.

Автор - AlexM
Дата добавления - 04.05.2016 в 16:03
Garik007 Дата: Среда, 04.05.2016, 16:43 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Почему-то отказывается работать эта формула.
К сообщению приложен файл: 8690422.xls(30Kb)
 
Ответить
СообщениеПочему-то отказывается работать эта формула.

Автор - Garik007
Дата добавления - 04.05.2016 в 16:43
DrMini Дата: Среда, 04.05.2016, 20:35 | Сообщение № 17
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013
Garik007, Всё работает. В C14 Вы не поставили квадратные скобки массива в конце и в начале., а в C17:C23 Вы не сменили формат ячейки. Протащите C13 вниз и будет Вам счастье.
[moder]Фигурные?


Сообщение отредактировал _Boroda_ - Среда, 04.05.2016, 20:42
 
Ответить
СообщениеGarik007, Всё работает. В C14 Вы не поставили квадратные скобки массива в конце и в начале., а в C17:C23 Вы не сменили формат ячейки. Протащите C13 вниз и будет Вам счастье.
[moder]Фигурные?

Автор - DrMini
Дата добавления - 04.05.2016 в 20:35
DrMini Дата: Среда, 04.05.2016, 20:48 | Сообщение № 18
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 151
Репутация: 1 ±
Замечаний: 20% ±

Excel 2010, 2013
Модератор:
Фигурные?

Ага.
 
Ответить
Сообщение
Модератор:
Фигурные?

Ага.

Автор - DrMini
Дата добавления - 04.05.2016 в 20:48
AlexM Дата: Среда, 04.05.2016, 22:44 | Сообщение № 19
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3053
Репутация: 740 ±
Замечаний: 0% ±

А вот не полезу в файле формулу смотреть! Лениво качать.
Формула массива
Код
=--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВБ(A3;ПОИСКПОЗ(1=1;ПСТР(A4&"ё";СТРОКА($1:$20);1)>"z";)-1);"-";);")";);"(";);10)
Формат ячейки [<1000000]"неправильный номер";[>9999999]"8"0;"8495"0


Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
Сообщение
А вот не полезу в файле формулу смотреть! Лениво качать.
Формула массива
Код
=--ПРАВБ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ЛЕВБ(A3;ПОИСКПОЗ(1=1;ПСТР(A4&"ё";СТРОКА($1:$20);1)>"z";)-1);"-";);")";);"(";);10)
Формат ячейки [<1000000]"неправильный номер";[>9999999]"8"0;"8495"0

Автор - AlexM
Дата добавления - 04.05.2016 в 22:44
Garik007 Дата: Четверг, 05.05.2016, 09:10 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 45
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Всем спасибо за решение данной задачки, теперь все работает как нужно.
 
Ответить
СообщениеВсем спасибо за решение данной задачки, теперь все работает как нужно.

Автор - Garik007
Дата добавления - 05.05.2016 в 09:10
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Приведение телефонных номеров к единому формату (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!