Вход
Быстрая регистрация
Если вы у нас впервые: О проекте FAQ
0

Как в Excel разделить текстовые строки с адресами по столбцам формулой?

vdtest [15.3K] 1 неделю назад

В одном столбце таблицы Экселя записаны адреса различных организаций через запятую:

Индекс, город, улица, дом

Как составить формулу чтобы в Экселе разделить строки с адресами организаций по столбцам?

Как разделить текстовые строки с адресами организаций на столбцы, чтобы разные части адреса (индекс, улица, номер дома) попали в разные ячейки?

1

Составим формулу, выделяющую одну часть адреса по номеру элемента и растянем на все столбцы, а потом выделим все ячейки одной строки и растянем на все строки.

Исходные данные:

строка с адресом находится в столбце B, начиная с ячейки D3, а помещать элементы адреса будем последовательно, начиная со столбца G (первая ячейка G3)

Алгоритм формулы для одной первой ячейки:

Добавляем в строку запятую сначала и в конце, чтобы у каждый элемент имел запятую сначала и в конце, вместо D3 получим выражение: ","&D3&","

определяем номер элемента адреса как смещение от столбца данных от начала размещения:

=Столбец() - Столбец(G3) + 1

Находим положение начала и конца элемента адреса, как позиции символа "," для этого элемента по формуле из ответа на вопрос Как в Эксель найти символ или подстроку по номеру вхождения в строку?

начальная позиция элемента:

=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"­," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G3) + 1))

или так

=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"­," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G:$G) + 1))

позиция окончания элемента

=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"­," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G3) + 2))

или так

=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"­," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G:$G) + 2))

Окончательная формула получится после подстановки этих выражений в функцию выделения подстроки ПСТР:

=ПСТР( ","&$D3&"," ; НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3) + 1) ; 1)+1 ; НАЙТИ(СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3)+2) ;1) - НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3) +1) ; 1) -1)

или так:

=ПСТР( ","&$D3&"," ; НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G) + 1) ; 1)+1 ; НАЙТИ(СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G)+2) ;1) - НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G) +1) ; 1) -1)

теперь нужно учесть, что в адресе может отсутствовать элемент, например почтовый индекс, для этого придется сделать обработку ошибки (пустая строка для отсутствующего элемента ), если элемент отсутствует получим окончательную итоговую формулу:

=ЕСЛИОШИБКА(ПСТР( ","&$D3&"," ; НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3) + 1) ; 1)+1 ; НАЙТИ(СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3)+2) ;1) - НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G3) +1) ; 1) -1) ; "")

или так:

=ЕСЛИОШИБКА(ПСТР( ","&$D3&"," ; НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G) + 1) ; 1)+1 ; НАЙТИ(СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G)+2) ;1) - НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ( ","&$D3&"," ; "," ; СИМВОЛ(3) ; СТОЛБЕЦ() - СТОЛБЕЦ($G:$G) +1) ; 1) -1) ; "")

Пример работы формулы

После копирования формулы с этой страницы (сайт "Большой Вопрос") надо будет заменить адреса ячеек на адреса ячеек своего проекта либо руками либо через интернет сервис замены адресов ячеек Эксель

Знаете ответ?
Есть интересный вопрос? Задайте его нашему сообществу, у нас наверняка найдется ответ!
Делитесь опытом и знаниями, зарабатывайте награды и репутацию, заводите новых интересных друзей!
Задавайте интересные вопросы, давайте качественные ответы и зарабатывайте деньги. Подробнее..
регистрация
OpenID