Составим формулу, выделяющую одну часть адреса по номеру элемента и растянем на все столбцы, а потом выделим все ячейки одной строки и растянем на все строки.
Исходные данные:
строка с адресом находится в столбце B, начиная с ячейки D3, а помещать элементы адреса будем последовательно, начиная со столбца G (первая ячейка G3)
Алгоритм формулы для одной первой ячейки:
Добавляем в строку запятую сначала и в конце, чтобы у каждый элемент имел запятую сначала и в конце, вместо D3 получим выражение: ","&D3&","
определяем номер элемента адреса как смещение от столбца данных от начала размещения:
=Столбец() - Столбец(G3) + 1
Находим положение начала и конца элемента адреса, как позиции символа "," для этого элемента по формуле из ответа на вопрос Как в Эксель найти символ или подстроку по номеру вхождения в строку?
начальная позиция элемента:
=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"<wbr />," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G3) + 1))
или так
=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"<wbr />," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G:$G) + 1))
позиция окончания элемента
=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"<wbr />," ; "," ; СИМВОЛ(3) ; Столбец() - Столбец($G3) + 2))
или так
=НАЙТИ( СИМВОЛ(3) ; ПОДСТАВИТЬ(","&$D3&"<wbr />," ; "," ; СИМВОЛ(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) ; "")
Пример работы формулы
После копирования формулы с этой страницы (сайт "Большой Вопрос") надо будет заменить адреса ячеек на адреса ячеек своего проекта либо руками либо через интернет сервис замены адресов ячеек Эксель
