Как в Excel разделить текстовые строки с адресами по столбцам формулой?
В одном столбце таблицы Экселя записаны адреса различных организаций через запятую:
Индекс, город, улица, дом
Как составить формулу чтобы в Экселе разделить строки с адресами организаций по столбцам?
Как разделить текстовые строки с адресами организаций на столбцы, чтобы разные части адреса (индекс, улица, номер дома) попали в разные ячейки?
комментировать
в избранное бонус
vdtes t [37.3K]
4 года назад
Составим формулу, выделяющую одну часть адреса по номеру элемента и растянем на все столбцы, а потом выделим все ячейки одной строки и растянем на все строки.
строка с адресом находится в столбце 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) ; «»)
Пример работы формулы
После копирования формулы с этой страницы (сайт «Большой Вопрос») надо будет заменить адреса ячеек на адреса ячеек своего проекта либо руками либо через интернет сервис замены адресов ячеек Эксель
Как разделить ФИО в Excel на ячейки? Как выделить инициалы из ФИО в Excel?
В столбце Эксель (Excel) записано ФИО человека в формате «Фамилия Имя Отчество».
Например, Иванов Андрей Иванович.
Требуется с помощью формул сделать, чтобы:
1) Фамилия, имя и отчество располагались в отдельных ячейках.
2) Выделить инициалы — преобразовать «Иванов Андрей Иванович» в «Иванов А. И.».
Как в (Эксель) Excel выполнить подобную задачу?
Как в (Эксель) Excel разделить ФИО на отдельные ячейки и на инициалы?
комментировать
в избранное
10 ответов:
Anast aziol og [18K]
6 лет назад
Пусть в ячейке A1 содержится ФИО в виде «Фамилия Имя Отчество».
Тогда, для получения результата :
- В ячейку B1 пишем формулу : =ПСТР(A1;1;НАЙТИ(» «;A1;1))
- В ячейку С1 пишем формулу : =ПСТР(A1;НАЙТИ(» «;A1;1)+1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)-НАЙТИ(» «;A1;1))
- В ячейку D1 пишем формулу : =ПСТР(A1;НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1;ДЛСТР(A1)-НАЙТИ(» «;A1;НАЙТИ(» «;A1;1)+1)+1)
- В ячейку E1 пишем формулу : =B1&» «&ЛЕВСИМВ(C1;1)&». «&ЛЕВСИМВ(D1;1)&».»
автор вопроса выбрал этот ответ лучшим
комментировать
в избранное ссылка отблагодарить
Vodil a [17.2K]
10 месяцев назад
Не зная, что у многих программ есть очень интересные и полезные функции часто приходится выполнять лишнюю работу, которую, зная как сделать проще можно было сделать за секунды. Так часто бывает нужно разбить фамилию имя отчество, написанные в одной ячейке Экселя на три столбца разных, где будет отдельно фамилия, отдельно имя, отдельно отчество. Чтобы это сделать нужно выделить ячейки, в которых нужно разделить данные, потом выбрать вверху программы слово Данные, потом выбрать кнопку текст по столбцам и в диалоговом окошке выполнить необходимые настройки. Иногда бывает несколько пробелов между словами, чтобы не появлялось лишних столбцов нужно поставить флажок считать последовательные ограничители одним. потом выбрать далее и готово.
Microsoft Excel. Как в Экселе разделить ячейку по горизонтали?
Функции разделения ячеек в Microsoft Excel нет. Но можно сделать так, чтобы ячейка выглядела разделенной. Для этого необходимо смежные с ней объединить. При этом нужно следить за содержимым объединяемых ячеек, чтобы не удалить лишнего.
система выбрала этот ответ лучшим
комментировать
в избранное ссылка отблагодарить
владс андро вич [833K]
7 лет назад
Такая функция как единое одноразовое действие отсутствует, однако такое действие, можно воспроизвести, за счет совокупности отлаженных и выверенных действий.
Необходимо объединить две ячейки,за счет их выделения а затем в панели настроек, вам нужно будет включить закладку Главная, в которой присутствует графа под наименованием Шрифт, в которой присутствует кнопка регулирующая рисование границ, вам необходимо задействовать, графу Все границы.
комментировать
в избранное ссылка отблагодарить
Искат ель прикл ючени й [103K]
7 лет назад
В программе Excel возможно разделить ячейку по горизонтали двумя способами:
1) Выделить две ячейки, а потом на верхней панели настроек, вам нужна заклада Главная, там есть блок с названием Шрифт, там есть кнопка отвечающая за рисование границ, вам нужно выбрать кнопку Все границы.
2) Нарисовать линиями такую границу, тогда снова следует переместиться на панель настроек, вам нужна закладка Вставка, смотрите блок под названием Иллюстрация, выбираете кнопку Фигуры, а там находите линии, после чего можно выбрать ячейку и там нарисовать уже горизонтальную линию.
Какой способ вам удобен, тем и пользуйтесь.
Разбивка данных в столбце на основании вводимого текста
Введите имя или другое значение в столбце рядом с данными и нажмите ввод.
Начните вводить следующее имя. При этом будет отсказок на список предлагаемых имен.
Если все выглядит неплохо, просто нажмите ввод, чтобы принять список.
Совет: Чтобы продолжить ввод без использования предлагаемых имен, нажмите escape.
Введите фамилию в следующем столбце и нажмите ввод.
Начните вводить следующее имя,нажмите ввод, и все готово.
Переход на прописные буквы.
«»Вспышка» счувствительна к делу. Например, если ввести более нижнюю фамилию, все фамилии будут следовать в соответствии с именем.
Если результат вам не нравится, просто нажмите escape и продолжайте вводить текст без использования предложений.
Вы также можете сделать так, чтобы чувствительность дела совместилась с вами, и изменить дело текста с помощью этой переменной.
Работа с отными именами
Если данные имеют одинаковые инициалы или имена (т. е. каждое имя их имеет), она работает одинаково, но выполните шаги три раза вместо двух.
Дополнительные информация о flash Fill
Разумеется, данные не должны быть именами. Она работает с любыми данными, которые нужно разделить на несколько столбцов, или просто использовать их для заполнения данных на основе примера. Она обычно начинает работать, когда распознает в данных закономерность.
Однако эта информация может не всегда начинаться с заполнения данных. Она лучше всего работает, если в данных есть согласованность. Например, все имена имеют средние инициалы или все адреса имеют один и тот же тип почтовых индексов. Если данные отличаются различными данными, вы всегда можете разделить текст по столбцам на разные ячейки или разделить текст с помощью функций.