Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Добавить 8495- к телефонам + убрать лишние пробелы после объединения в таблице
 
#1
26.08.2021 19:39:58
здесь подсказали https://www.planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=142835&a... как объединить столбцы с помощью амперсанта, объединил адрес с номерами дома, корпуса, квартиры и телефон. Но после номера дома и тире идет  лишний пробел, почему? Как исправить и возможно ли не руками и не  макросами?
Телефоны (пусть в отдельном столбце):  7-значные номер, чаще всего один  ячейке вида  ###-##-##,  но есть и по 2 номера в ячейке и есть вида  ####### (т.е без тире).  Необходимо добавить всем 8495-, чтобы  получились все номера вида  8495-###-##-##. Как это сделать? Желательно,  без макросов. Понятно, что  есть вариант через амперсант тупо, но нет  ли иного варианта, особенно  учитывая, что в части таблицы отсутствуют  тире между номерами телефона и есть по 2 в одной строке.
Файл с примером, где указано, как пытался сделать прилагаю.
Надеюсь, на этот раз ничего не нарушил.
Изменено: vikttur - 27.08.2021 12:17:04
 
 
 
#2
26.08.2021 20:04:15
Цитата
Артем Клопов написал:
Но после номера дома и тире идет  лишний пробел, почему?
в ячейке H8 у вас идёт лишний пробел после номера дома и тире, т.к. в ячейке D8 (корпус) перед цифрой 2 стоит пробел ("пробел цифра 2"). Но вы глазами его не видите, а Excel видит этот пробел и добавляет его в вашу формулу.
Удалить пробелы из столбца D можно так
1. Выделяете весь столбец мышкой, для этого мышкой щёлкаете на буковку D (это название столбца)
2. Нажимаете Ctrl+H
3. В поле Найти ставите 1 пробел (один раз нажимаете на клавишу пробел на клавиатуре)
4. Поле "Заменить на"  оставляете пустым и нажимаете кнопку "Заменить всё"

Цитата
Артем Клопов написал:
но есть и по 2 номера в ячейке
какой адрес этой ячейки в вашем файле?
Изменено: New - 26.08.2021 21:09:56
 
 
 
#3
26.08.2021 20:46:10
New, точно, вот я дебил...пробелы. Как убрать я знаю. Спасибо!

Я не весь файл прикладывал, таблица же огромная. Перезалил и выделил для примера 2 ячейки.
 
 
 
#4
26.08.2021 21:05:19
я в формулах не очень, могу лишь предложить вот такую формулу (добавьте пустой столбец G и в ячейку G2 вставьте эту формулу)

Код
=ЕСЛИ(ДЛСТР(F2)=9;"8495-"&F2;ЕСЛИ(ДЛСТР(F2)=7;"8495-"&ЛЕВСИМВ(F2;3)&"-"&ПСТР(F2;4;2)&"-"&ПРАВСИМВ(F2;2);F2))

она из строк:
300-95-04
3074291
сделает
8495-300-95-04
8495-307-42-91
А как обработать ячейки, где в одной ячейке записано несколько телефонов подряд формулами я не знаю
Изменено: New - 26.08.2021 21:08:21
 
 
 
#5
26.08.2021 21:27:35
New,ничего себе не очень...мне даже и близко не разобрать такую крутую формулу. Буду пробовать, спасибо!
Особенно за мысль с пробелами.
 
 
 
#6
26.08.2021 21:48:44
Как вариант для приведенного примера
=IF(ISNUMBER(FIND(",";F2));"8495-"&  TEXT(--SUBSTITUTE(SUBSTITUTE(TRIM(LEFT(SUBSTITUTE(F2;",";"               ");15));"-";"");"495";"");"###-##-##")&"    "  & "8495-"&  TEXT(--SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(F2;",";"               ");15));"-";"");"495";"");"###-##-##") ; "8495-"&TEXT(--SUBSTITUTE(SUBSTITUTE(F2;"-";"");"495";"");"###-##-##"))
 
 
 
#7
26.08.2021 22:30:32
Цитата
Добавить 8495- к телефонам
Для вашего примера UDF
Код
Function Telefon(cell As String)
 Dim re As Object
 Dim temp As String
 Set re = CreateObject("vbscript.regexp")
    re.Pattern = "(-|\s|\+|\(|\))"
    re.Global = True
      temp = re.Replace(cell, "")
    re.Pattern = "(\d{3})?(\d{3})(\d{2})(\d{2})"
    If Len(temp) = 10 And Left(temp, 3) = 495 Then
      Telefon = re.Replace(temp, "8$1-$2-$3-$4")
    Else
      Telefon = re.Replace(temp, "8495-$2-$3-$4")
    End If
End Function
 
 
 
#8
27.08.2021 00:44:31
Kuzmich, спасибо. Но макросы я не знаю от слова совсем. А мне важно понимать, что я делаю,  это и учёба и не хочется пользоваться чужим трудом...
Ещё раз спасибо.
 
 
 
#9
27.08.2021 00:46:37
bigorq, Спасибо, буду пытаться разобраться.
 
 
 
Страницы: 1
Наверх