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

Вход

Регистрация

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

 

= Мир MS Excel/Формула для приведения номеров телефонов к одному формату - Мир MS Excel

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

Excel 2019
Есть список мобильных номеров России и Украины. В коде страны может не хватать символа +, либо +3, либо +38. Задача привести их к одному формату, хотелось бы узнать можно ли решить решить задачу формулами.

Собственно есть закономерность что номера начинаются с: 0; 3; 8; 7; либо +. Возможно ли с помощью компоновки формул ЕСЛИМН ЛЕВСИМВ и каких-то ещё выполнить приведение в единый формат путём добавления недостающих символов? Заранее благодарю за помощь!
К сообщению приложен файл: 2801546.xlsx(9.7 Kb)
 
Ответить
СообщениеЕсть список мобильных номеров России и Украины. В коде страны может не хватать символа +, либо +3, либо +38. Задача привести их к одному формату, хотелось бы узнать можно ли решить решить задачу формулами.

Собственно есть закономерность что номера начинаются с: 0; 3; 8; 7; либо +. Возможно ли с помощью компоновки формул ЕСЛИМН ЛЕВСИМВ и каких-то ещё выполнить приведение в единый формат путём добавления недостающих символов? Заранее благодарю за помощь!

Автор - MS_Iron
Дата добавления - 13.08.2020 в 21:11
gling Дата: Четверг, 13.08.2020, 22:09 | Сообщение № 2
Группа: Друзья
Ранг: Старожил
Сообщений: 2184
Репутация: 544 ±
Замечаний: 0% ±

2010
И вам здравствовать.
Вы показали результат на понятных номерах для них можно так
Код
="+380"&ПРАВСИМВ(A2;9)
а как быть с таким номером, 79045800734 или +79035478594?


ЯД-41001506838083
 
Ответить
СообщениеИ вам здравствовать.
Вы показали результат на понятных номерах для них можно так
Код
="+380"&ПРАВСИМВ(A2;9)
а как быть с таким номером, 79045800734 или +79035478594?

Автор - gling
Дата добавления - 13.08.2020 в 22:09
MS_Iron Дата: Четверг, 13.08.2020, 22:27 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
gling, благодарю за скорый ответ. Забыл уточнить что если + имеется, то номер полный и корректировка не требуется, а в номер типа 79045800734 просто нужно подставить +


Сообщение отредактировал MS_Iron - Четверг, 13.08.2020, 22:27
 
Ответить
Сообщениеgling, благодарю за скорый ответ. Забыл уточнить что если + имеется, то номер полный и корректировка не требуется, а в номер типа 79045800734 просто нужно подставить +

Автор - MS_Iron
Дата добавления - 13.08.2020 в 22:27
bigor Дата: Четверг, 13.08.2020, 22:33 | Сообщение № 4
Группа: Проверенные
Ранг: Обитатель
Сообщений: 270
Репутация: 48 ±
Замечаний: 0% ±

Excel 2016
если + имеется, то номер полный и корректировка не требуется

А как быть с 12 строкой?

Проверяйте формулу
Код
=IF(LEN(A2)>=13;A2;  IF(LEN(A2)=12;IF(LEFT(A2;1)="+";A2;"+"&A2);  IF(LEN(A2)<11;"+38"&A2;IF(LEFT(A2;1)="7";"+"&A2; "+3"&A2)))),
К сообщению приложен файл: 2801546_1.xlsx(7.8 Kb)
 
Ответить
Сообщение
если + имеется, то номер полный и корректировка не требуется

А как быть с 12 строкой?

Проверяйте формулу
Код
=IF(LEN(A2)>=13;A2;  IF(LEN(A2)=12;IF(LEFT(A2;1)="+";A2;"+"&A2);  IF(LEN(A2)<11;"+38"&A2;IF(LEFT(A2;1)="7";"+"&A2; "+3"&A2)))),

Автор - bigor
Дата добавления - 13.08.2020 в 22:33
MS_Iron Дата: Четверг, 13.08.2020, 22:43 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
bigor, Бесконечная благодарность за формулу, всё работает.

12ю строку не разглядел изначально. Желательно бы решить, но вы мне открыли глаза на то, что формулу ЕСЛИ много просто писать много раз (не знал что так можно, крутился вокруг формулы ЕСЛИМН), попробую уже самостоятельно решить добавлением ещё одного ЕСЛИ.
 
Ответить
Сообщениеbigor, Бесконечная благодарность за формулу, всё работает.

12ю строку не разглядел изначально. Желательно бы решить, но вы мне открыли глаза на то, что формулу ЕСЛИ много просто писать много раз (не знал что так можно, крутился вокруг формулы ЕСЛИМН), попробую уже самостоятельно решить добавлением ещё одного ЕСЛИ.

Автор - MS_Iron
Дата добавления - 13.08.2020 в 22:43
bigor Дата: Четверг, 13.08.2020, 22:54 | Сообщение № 6
Группа: Проверенные
Ранг: Обитатель
Сообщений: 270
Репутация: 48 ±
Замечаний: 0% ±

Excel 2016
Советую посмотреть на предложенный gling вариант, и дополнить его условиями. Я слишком "в лоб" решал задачу.
 
Ответить
СообщениеСоветую посмотреть на предложенный gling вариант, и дополнить его условиями. Я слишком "в лоб" решал задачу.

Автор - bigor
Дата добавления - 13.08.2020 в 22:54
gling Дата: Пятница, 14.08.2020, 00:20 | Сообщение № 7
Группа: Друзья
Ранг: Старожил
Сообщений: 2184
Репутация: 544 ±
Замечаний: 0% ±

2010
Еще вариант
Код
=ЕСЛИ(ЛЕВБ(A3)="+";ЛЕВБ(A3;ДЛСТР(A3)-9);ЕСЛИ(ЛЕВБ(A3)="7";"+"&ЛЕВБ(A3;ДЛСТР(A3)-9);"+380"))&ПРАВСИМВ(A3;9)


ЯД-41001506838083
 
Ответить
СообщениеЕще вариант
Код
=ЕСЛИ(ЛЕВБ(A3)="+";ЛЕВБ(A3;ДЛСТР(A3)-9);ЕСЛИ(ЛЕВБ(A3)="7";"+"&ЛЕВБ(A3;ДЛСТР(A3)-9);"+380"))&ПРАВСИМВ(A3;9)

Автор - gling
Дата добавления - 14.08.2020 в 00:20
MS_Iron Дата: Пятница, 14.08.2020, 00:47 | Сообщение № 8
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
gling, спасибо, работает.

А возможно ли в формулу вписать ещё решение для 12й строки? Там стоит номер: +30919876546 и между 3 и 0 пропущено 8. Уже часа 2 бьюсь и не могу скорректировать вариант предоставленный bigor

UPD Удалось вставить костыль, но теперь формула к +79 прибавляет единицу и получается +80, как исправить?
Код
=ЕСЛИ( ЛЕВБ(A19)="+38"&"+7"; ЛЕВБ(A19;ДЛСТР(A19)); ЕСЛИ( ЛЕВБ(A19)="7"; "+"&ЛЕВБ(A19;ДЛСТР(A19)); ЕСЛИ( ЛЕВСИМВ(A19;5)="+3091"; "+3809"&ПРАВСИМВ(A19;8); "+380"&ПРАВСИМВ(A19;9))))


UPD-2 Таки удалось решить проблему, небольшая модернизация кода bigor и костыль не ломает коды оператора
Код
=ЕСЛИ(ДЛСТР(A5)>=13;   A5;   ЕСЛИ(ДЛСТР(A5)=12;    ЕСЛИ(ЛЕВСИМВ(A5;1)="+";    ЕСЛИ(ЛЕВСИМВ(A5;3)="+30"; "+38"&ПРАВСИМВ(A5;10);A5);  "+"&A5);  ЕСЛИ(ДЛСТР(A5)<11; "+38"&A5;  ЕСЛИ(ЛЕВСИМВ(A5;1)="7";  "+"&A5; "+3"&A5))))


Сообщение отредактировал MS_Iron - Пятница, 14.08.2020, 03:22
 
Ответить
Сообщениеgling, спасибо, работает.

А возможно ли в формулу вписать ещё решение для 12й строки? Там стоит номер: +30919876546 и между 3 и 0 пропущено 8. Уже часа 2 бьюсь и не могу скорректировать вариант предоставленный bigor

UPD Удалось вставить костыль, но теперь формула к +79 прибавляет единицу и получается +80, как исправить?
Код
=ЕСЛИ( ЛЕВБ(A19)="+38"&"+7"; ЛЕВБ(A19;ДЛСТР(A19)); ЕСЛИ( ЛЕВБ(A19)="7"; "+"&ЛЕВБ(A19;ДЛСТР(A19)); ЕСЛИ( ЛЕВСИМВ(A19;5)="+3091"; "+3809"&ПРАВСИМВ(A19;8); "+380"&ПРАВСИМВ(A19;9))))


UPD-2 Таки удалось решить проблему, небольшая модернизация кода bigor и костыль не ломает коды оператора
Код
=ЕСЛИ(ДЛСТР(A5)>=13;   A5;   ЕСЛИ(ДЛСТР(A5)=12;    ЕСЛИ(ЛЕВСИМВ(A5;1)="+";    ЕСЛИ(ЛЕВСИМВ(A5;3)="+30"; "+38"&ПРАВСИМВ(A5;10);A5);  "+"&A5);  ЕСЛИ(ДЛСТР(A5)<11; "+38"&A5;  ЕСЛИ(ЛЕВСИМВ(A5;1)="7";  "+"&A5; "+3"&A5))))

Автор - MS_Iron
Дата добавления - 14.08.2020 в 00:47
bigor Дата: Пятница, 14.08.2020, 07:45 | Сообщение № 9
Группа: Проверенные
Ранг: Обитатель
Сообщений: 270
Репутация: 48 ±
Замечаний: 0% ±

Excel 2016
Утро вечера мудренее :)
Код
=IF(OR(LEFT(A2;1)="7";LEFT(A2;2)="+7");"+7"&RIGHT(A2;10);"+380"&RIGHT(A2;9))
 
Ответить
СообщениеУтро вечера мудренее :)
Код
=IF(OR(LEFT(A2;1)="7";LEFT(A2;2)="+7");"+7"&RIGHT(A2;10);"+380"&RIGHT(A2;9))

Автор - bigor
Дата добавления - 14.08.2020 в 07:45
MS_Iron Дата: Суббота, 15.08.2020, 10:04 | Сообщение № 10
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
bigor, огромное спасибо. А ведь сначала думал что лучше VBA использовать, а тут можно всё правильной компоновкой простых формул решить :D
 
Ответить
Сообщениеbigor, огромное спасибо. А ведь сначала думал что лучше VBA использовать, а тут можно всё правильной компоновкой простых формул решить :D

Автор - MS_Iron
Дата добавления - 15.08.2020 в 10:04
Nic70y Дата: Суббота, 15.08.2020, 12:23 | Сообщение № 11
Группа: Друзья
Ранг: Экселист
Сообщений: 6509
Репутация: 1431 ±
Замечаний: 0% ±

ru13;10;03
Код
="+"&(--ЛЕВБ(--A2)<>7)*31+7&ПРАВБ(A2;10)


Яндекс Деньги 41001841029809
 
Ответить
Сообщение
Код
="+"&(--ЛЕВБ(--A2)<>7)*31+7&ПРАВБ(A2;10)

Автор - Nic70y
Дата добавления - 15.08.2020 в 12:23
MS_Iron Дата: Воскресенье, 16.08.2020, 12:59 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 8
Репутация: 0 ±
Замечаний: 0% ±

Excel 2019
Nic70y, мощно, спасибо ))
 
Ответить
СообщениеNic70y, мощно, спасибо ))

Автор - MS_Iron
Дата добавления - 16.08.2020 в 12:59
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула для приведения номеров телефонов к одному формату (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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