Измененить формат номера телефона

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

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

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Измененить формат номера телефона (Формулы/Formulas)

dashenka2237

Дата: Среда, 29.03.2023, 07:21 | Сообщение № 1

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

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

Сообщений: 3

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

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

Добрый день, помогите, пожалуйста, решить проблему, есть таблица (куча строк), в ней забиты номера в формате 79995556622, в некоторых ячейках по несколько номеров через запятую, где-то есть эл.почта, нужно привести номер телефона к формату 8-999-555-66-22, это реально сделать?

К сообщению приложен файл: kniga1.xlsx (9.3 Kb)

Ответить

elovkov

Дата: Среда, 29.03.2023, 08:16 | Сообщение № 2

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

Ранг: Обитатель

Сообщений: 368

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

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

Excel 2013

Пока придумал как вытащить номера раздельно

Код

=ПСТР($A1;СТОЛБЕЦ($A$1)+13*(СТОЛБЕЦ(A1)-1);11)

Это для ячейки В1 например, и тащим вправо на 5-6 столбцов, сколько там максимум номеров может быть и вниз
А вот как их до 7-999-999-99-99 переделать еще не придумал)))

К сообщению приложен файл: 0361967.xlsx (11.2 Kb)

Умное лицо это еще не признак ума. Все глупости на земле делаются именно с этим выражением лица

Ответить

_Boroda_

Дата: Среда, 29.03.2023, 09:15 | Сообщение № 3

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Цитата elovkov, 29.03.2023 в 08:16, в сообщении № 2 (

)

А вот как их до 7-999-999-99-99 переделать еще не придумал

Это как раз несложно )))

Код

=ТЕКСТ(ПСТР($A1;СТОЛБЕЦ($A$1)+13*(СТОЛБЕЦ(A1)-1);11);"0-000-000-00-00")

dashenka2237, а в каком вообще виде нужно результат получить? В смысле - как оно все должно на листе располагаться? И что с почтой делать? И может ли там еще какая-то информация быть?

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

dashenka2237

Дата: Среда, 29.03.2023, 09:44 | Сообщение № 4

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

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

Сообщений: 3

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

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

_Boroda_, прикрпила файл, столбец А-исходные данный, столбец В-необходимый результат. И начало телефона не с 7, а с 8

К сообщению приложен файл: 5393883.xlsx (10.0 Kb)

Сообщение отредактировал dashenka2237 - Среда, 29.03.2023, 09:54

Ответить

msi2102

Дата: Среда, 29.03.2023, 10:33 | Сообщение № 5

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

Ранг: Обитатель

Сообщений: 413

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

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

Excel 2007

Наверное придется делать макросом
[vba]

Код

Sub Макрос1()
    Dim arr1, arr2, n As Long, m As Integer
    arr1 = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
    For n = 1 To UBound(arr1)
        arr2 = Split(arr1(n, 1), ",")
        For m = 0 To UBound(arr2)
            If IsNumeric(Trim(arr2(m))) And Len(Trim(arr2(m))) = 11 Then
                If Left(Trim(arr2(m)), 1) = 7 Or Left(Trim(arr2(m)), 1) = 8 Then
                    arr2(m) = Format(Mid(Trim(arr2(m)), 2), "8-@@@-@@@-@@-@@")
                End If
            End If
            arr1(n, 1) = Join(arr2, ", ")
        Next
    Next
    [c1].Resize(UBound(arr1), 1) = arr1
End Sub

[/vba]

[p.s.]Исправил код и файл

К сообщению приложен файл: kniga1.xlsm (20.4 Kb)

Сообщение отредактировал msi2102 - Среда, 29.03.2023, 10:57

Ответить

dashenka2237

Дата: Среда, 29.03.2023, 11:48 | Сообщение № 6

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

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

Сообщений: 3

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

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

msi2102, СПАСИБО!!!

Сообщение отредактировал dashenka2237 - Среда, 29.03.2023, 11:48

Ответить

Gustav

Дата: Среда, 29.03.2023, 12:47 | Сообщение № 7

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

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

Сообщений: 2731

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

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

начинал с Excel 4.0, видел 2.1

Для свежих версий Excel (2021+, 365, web):

Код

=ОБЪЕДИНИТЬ(", ";; LET(a; ТЕКСТ(СЖПРОБЕЛЫ(ТЕКСТРАЗД(A1;",")); "0-000-000-00-00"); ЕСЛИ(ЛЕВСИМВ(a)="7"; "8" & ПСТР(a;2;99); a)))

P.S. Причем, в процессе работы у меня также было промежуточное достижение в виде мега-формулы для одной ячейки, которая разбирает весь массив на отдельные ячейки по одному номеру (почтовому адресу), с указанием в соседней ячейке номера исходной строки (т.е. такая формула для "нормализации" справочника телефонов):

Код

=LET(arr; REDUCE(ГСТОЛБИК(0; ""); A1:A13; LAMBDA(a;b; ВСТОЛБИК(a; ТЕКСТРАЗД(ОБЪЕДИНИТЬ("~";; СТРОКА(b) &"|"& LET(c; ТЕКСТ(СЖПРОБЕЛЫ(ТЕКСТРАЗД(b; ",")); "0-000-000-00-00"); ЕСЛИ(ЛЕВСИМВ(c)="7"; "8" & ПСТР(c;2;99); c) )); "|"; "~")))); ФИЛЬТР(arr; ИНДЕКС(arr;;2)<>""))

Вводим эту формулу в одну ячейку, например, D1. Результат выглядит так (встречаем его в диапазоне D1:E27):

Для свежих версий Excel (2021+, 365, web):

Код

=ОБЪЕДИНИТЬ(", ";; LET(a; ТЕКСТ(СЖПРОБЕЛЫ(ТЕКСТРАЗД(A1;",")); "0-000-000-00-00"); ЕСЛИ(ЛЕВСИМВ(a)="7"; "8" & ПСТР(a;2;99); a)))

Код

Вводим эту формулу в одну ячейку, например, D1. Результат выглядит так (встречаем его в диапазоне D1:E27):

Gustav

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 29.03.2023, 14:00

Ответить

СообщениеДля свежих версий Excel (2021+, 365, web):

Код

=ОБЪЕДИНИТЬ(", ";; LET(a; ТЕКСТ(СЖПРОБЕЛЫ(ТЕКСТРАЗД(A1;",")); "0-000-000-00-00"); ЕСЛИ(ЛЕВСИМВ(a)="7"; "8" & ПСТР(a;2;99); a)))

Код

Вводим эту формулу в одну ячейку, например, D1. Результат выглядит так (встречаем его в диапазоне D1:E27):

Автор - Gustav
Дата добавления - 29.03.2023 в 12:47

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Измененить формат номера телефона (Формулы/Formulas)

Страница 1 из 1
1