Формула для приведения номеров телефонов к одному формату

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир 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)

Ответить

gling

Дата: Четверг, 13.08.2020, 22:09 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2519

Репутация: 674 ±

Замечаний: 0% ±

2010

И вам здравствовать.
Вы показали результат на понятных номерах для них можно так

Код

="+380"&ПРАВСИМВ(A2;9)

а как быть с таким номером, 79045800734 или +79035478594?

ЯД-41001506838083

Ответить

MS_Iron

Дата: Четверг, 13.08.2020, 22:27 | Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

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

Сообщение отредактировал MS_Iron - Четверг, 13.08.2020, 22:27

Ответить

bigor

Дата: Четверг, 13.08.2020, 22:33 | Сообщение № 4

Группа: Проверенные

Ранг: Старожил

Сообщений: 1099

Репутация: 209 ±

Замечаний: 0% ±

нет

Цитата gling, 13.08.2020 в 22:09, в сообщении № 2 ()

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

А как быть с 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)

Ответить

MS_Iron

Дата: Четверг, 13.08.2020, 22:43 | Сообщение № 5

Группа: Пользователи

Ранг: Прохожий

Сообщений: 8

Репутация: 0 ±

Замечаний: 0% ±

Excel 2019

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

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

Ответить

bigor

Дата: Четверг, 13.08.2020, 22:54 | Сообщение № 6

Группа: Проверенные

Ранг: Старожил

Сообщений: 1099

Репутация: 209 ±

Замечаний: 0% ±

нет

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

Ответить

gling

Дата: Пятница, 14.08.2020, 00:20 | Сообщение № 7

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2519

Репутация: 674 ±

Замечаний: 0% ±

2010

Еще вариант

Код

=ЕСЛИ(ЛЕВБ(A3)="+";ЛЕВБ(A3;ДЛСТР(A3)-9);ЕСЛИ(ЛЕВБ(A3)="7";"+"&ЛЕВБ(A3;ДЛСТР(A3)-9);"+380"))&ПРАВСИМВ(A3;9)

ЯД-41001506838083

Ответить

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))))