как разделить адрес в эксель по ячейкам
Как в Excel разделить текстовые строки с адресами по столбцам формулой?
В одном столбце таблицы Экселя записаны адреса различных организаций через запятую:
Индекс, город, улица, дом
Как составить формулу чтобы в Экселе разделить строки с адресами организаций по столбцам?
Как разделить текстовые строки с адресами организаций на столбцы, чтобы разные части адреса (индекс, улица, номер дома) попали в разные ячейки?
Составим формулу, выделяющую одну часть адреса по номеру элемента и растянем на все столбцы, а потом выделим все ячейки одной строки и растянем на все строки.
строка с адресом находится в столбце B, начиная с ячейки D3, а помещать элементы адреса будем последовательно, начиная со столбца G (первая ячейка G3)
Алгоритм формулы для одной первой ячейки:
Добавляем в строку запятую сначала и в конце, чтобы у каждый элемент имел запятую сначала и в конце, вместо D3 получим выражение: «,»&D3&»,»
определяем номер элемента адреса как смещение от столбца данных от начала размещения:
Находим положение начала и конца элемента адреса, как позиции символа «,» для этого элемента по формуле из ответа на вопрос Как в Эксель найти символ или подстроку по номеру вхождения в строку?
начальная позиция элемента:
позиция окончания элемента
Окончательная формула получится после подстановки этих выражений в функцию выделения подстроки ПСТР:
теперь нужно учесть, что в адресе может отсутствовать элемент, например почтовый индекс, для этого придется сделать обработку ошибки (пустая строка для отсутствующего элемента ), если элемент отсутствует получим окончательную итоговую формулу:
Пример работы формулы
После копирования формулы с этой страницы (сайт «Большой Вопрос») надо будет заменить адреса ячеек на адреса ячеек своего проекта либо руками либо через интернет сервис замены адресов ячеек Эксель
Как разделить адрес в эксель по ячейкам
В этой статье показаны методы разбиения или разделения нескольких адресов на отдельные части в Excel.
Вы можете использовать Текст в столбцы возможность разбивать адрес на отдельные части на основе разделителя в Excel. Пожалуйста, сделайте следующее.
1. Выделите ячейки с адресами, которые нужно разбить на части, и нажмите Данные > Текст в столбцы.
2. В первом Преобразовать текст в столбецволшебник, выберите разграниченный и нажмите Next кнопка. Смотрите скриншот:
3. Во втором мастере отметьте опцию разделителя на основе разделителя вашего адреса (в этом случае я проверяю Запятая коробка). Щелкните значок Next кнопку.
4. В последнем мастере выберите ячейку для размещения разделенных частей адреса в Destination поле и, наконец, щелкните Завершить кнопка. Смотрите скриншот:
Вы можете видеть, что выбранные адреса разделены на части, как показано на скриншоте ниже.
Была основана Разделить клетки полезности Kutools for Excel можно легко разбить или разделить адрес в выбранных ячейках на отдельные части в Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейки с адресом, который нужно разбить, затем нажмите Kutools > Слияние и разделение > Разделить клетки.
2. в Разделить клетки диалоговое окно, настройте следующим образом.
Внимание: В данном случае я выбираю Другое вариант и введите в поле символ запятой.
3. В секунду Разделить клетки диалоговом окне выберите ячейку для размещения разделенных частей, а затем щелкните значок OK кнопку.
Затем все выбранные адреса сразу разделяются на отдельные части.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Как в Excel разбить ячейку на две или несколько? Как разделить слова по ячейкам?
Электронные таблицы – незаменимый инструментарий для четкого преподнесения информации. При работе с.
Электронные таблицы – незаменимый инструментарий для четкого преподнесения информации. При работе с MS Excel пользователь встречается с необходимостью объединять или разделять ячейки. Как в Excel разбить ячейку на две, должен знать каждый.
Как разъединить ячейки в Excel
Минимальной единицей электронных таблиц является ячейка. Осуществить разбиение клеток возможно, если пользователь произвел слияние этих самых клеток. Опытный пользователь знает, как разделить ячейку в Экселе при составлении «заголовка» объекта со сложной структурой.
Существует 3 варианта для разбиения ячеек в табличном процессоре. Важно помнить, что разделить можно только те ячейки, которые прошли операцию слияния.
Слияние ячеек
Если в процесс работы встал вопрос, как объединить ячейки, то закономерным будет, как в Excel разделить объединенные ячейки. Структура объекта табличного процессора должна быть тщательно продумана.
Для слияния ячеек пользователь:
Замечание: в результате слияния информация останется в верхней левой клетке. Для сохранения текста из второй единицы пользователь копирует ее в другое место.
Кнопка «Объединение» неактивна, если объекты находятся на стадии редактирования.
Разбиение объединенных ячеек
Если структура объекта табличного процессора подразумевает разбиение ячейки в середине таблицы нужно знать, как разъединить ячейки в Excel. Для этого пользователь работает исключительно с кнопкой «Объединить и поместить в центре»:
Замечание: Эксель при таком варианте рассматривает результат как единое целое.
Разбиение ячеек по диагонали посредством изменения формата
Нужно знать, как разбить ячейки в Excel, как провести процесс разбиения по диагонали посредством форматирования. Пользователь:
Замечание: при таком способе разбиения ячейки табличный процессор воспринимает объект как одну клетку.
Разбиение ячеек по диагонали посредством вставки фигуры
Подобный вариант подходит, если секция большая или создана посредством объединения нескольких единиц. Для осуществления способа пользователь:
Зная, как в Эксель разделить ячейку на две части, пользователь построит таблицу по намеченному шаблону.
Как разделить слова по ячейкам
Если дана таблица, в клетках которой информация не вмещается в указанных пределах, пользователь упорядочивает информацию и приводит таблицу к читабельному виду. Для этого пользователь:
Другая ситуация настигает обывателя, когда не знает, как в Экселе разделить слова в ячейке по столбцам. Если в клетке указано несколько параметров, то привести текст к читабельному виду в таблице можно посредством:
Пользователям полезно знать, как разделить слова в Экселе по ячейкам. Если диапазон для разбиения текста по секциям не будет пустым, информация перезаписывается. Чтобы сделать свободное пространство, пользователь вставляет новые столбцы (строки) или вырезает и вставляет информацию на другом листе.
При слиянии и разбиении секций пользователь должен помнить, что применить сортировку или цикличность невозможно. Если в клетку не будут вносить информацию, а ее разделение – визуальное, то проводить операции по объединению и разделению нежелательно.
Делим слипшийся текст на части
Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:
Способ 1. Текст по столбцам
На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов (с разделителями) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).
Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.
Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании «Иванов, Манн и Фарбер») не делился по запятой
внутри названия.
И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:
Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.
Способ 2. Как выдернуть отдельные слова из текста
Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:
Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:
=SUBSTRING(Txt; Delimeter; n)
Способ 3. Разделение слипшегося текста без пробелов
Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО «ИвановИванИванович»), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:
Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:
Как разделить текст в excel с помощью формулы
Раннее мы рассматривали возможность разделить текст по столбцам на примере деления ФИО на составные части.
Для этого мы использовали инструмент в Excel «Текст по столбцам».
Видео: Разделить текст по столбцам в Excel / Text to Columns (Урок 6) [Eugene Avdukhov, Excel Для Всех]
Несомненно, это очень важный и полезный и инструмент в Excel, который значительно может упростить множество задач. Но у данного способа есть небольшой недостаток.
Если вам, например, постоянно присылают данные в определенном виде, а вам постоянно необходимо их делить, то это занимает определенное время, кроме того, если данные вам прислали заново, то вам снова нужно будет проделать все операции.
Содержание
Пример 1. Делим текст с ФИО по столбцам с помощью формул Если рассматривать на примере деления ФИО, то разделить текст можно будет с помощью текстовых формул Excel, используя функцию ПСТР и НАЙТИ, которую мы рассматривали в прошлых статьях.
В этом случае вам достаточно вставить данные в определенный столбец, а формулы автоматически разделят текст так как вам необходимо. Давайте приступит к рассмотрению данного примера. У нас есть столбец со списком ФИО, наша задача разместить фамилию, имя отчество по отдельным столбцам.
Попробуем очень подробно описать план действия и разобьем решение задачи на несколько этапов. Первым делом добавим вспомогательные столбцы, для промежуточных вычислений, чтобы вам было понятнее, а в конце все формулы объединим в одну.
Итак, добавим столбцы позиция 1-го и 2-го пробелам. С помощью функции НАЙТИ, как мы уже рассматривали в предыдущей статье найдем позицию первого пробелам. Для этого в ячейке «H2» пропишем формулу
и протянем вниз. Теперь нам необходимо найти порядковый номер второго пробела. Формула будет такая же, но с небольшим отличием. Если прописать такую же формулу, то функция найдет нам первый пробел, а нам нужен второй пробел.
Значит на необходимо поменять третий аргумент в функции НАЙТИ — начальная позиция — то есть позиция с которой функция будет искать искомый текст.
Мы видим, что второй пробел находится в любом случае после первого пробела, а позицию первого пробела мы уже нашли, значит прибавив 1 к позиции первого пробелам мы укажем функции НАЙТИ искать пробел начиная с первой буквы после первого пробела.
Функция будет выглядеть следующим образом:
Далее протягиваем формулу и получаем позиции 1-го и 2-го пробела.
Приступаем к делению первой части текста — Фамилии
Для этого мы воспользуемся функцией ПСТР, напомню синтаксис данной функции:
=ПСТР(текст- начальная_позиция- число_знаков), где
Формула будет выглядеть следующим образом:
Приступаем к делению второй части текста — Имя
Снова используем функцию =ПСТР(текст- начальная_позиция- число_знаков), где
Получаем итоговую формулу:
Приступаем к делению третьей части текста — Отчество
И снова функция =ПСТР(текст- начальная_позиция- число_знаков), где
Видео: Формулы в Эксель
Далее выделяем все три ячейки и протягиваем формулы вниз и получаем нужный нам результат. На этом можно закончить, а можно промежуточные расчеты позиции пробелов прописать в сами формулы деления текста.
Видим, что в формуле ячейки I2 встречается H2 меняем ее на саму формулу и получаем в ячейке I2 вложенную формулу НАЙТИ(» «-A2-НАЙТИ(» «-A2-1)+1)
Смотрим первую формулу выделения Фамилии и смотрим где здесь встречается H2 или I2 и меняем их на формулы в этих ячейках, аналогично с Именем и Фамилией
Теперь промежуточные вычисления позиции пробелом можно смело удалить. Это один из приемов, когда для простоты сначала ищутся промежуточные данные, а потом функцию вкладывают одну в другую. Согласитесь, если писать такую большую формулу сразу, то легко запутаться и ошибиться.
Надеемся, что данный пример наглядно показал вам, как полезны текстовые функции Excel для работы с текстом и как они позволяют делить текст автоматически с помощью формул однотипные данные.
Пример 2. Как разделить текст по столбцам в Excel с помощью формулы
Рассмотрим второй пример, который так же очень часто встречался на практике. Пример похож предыдущий, но данных которые нужно разделить значительно больше. В этом примере я покажу прием, который позволит достаточно быстро решить вопрос и не запутаться.
Допустим у нас есть список чисел, перечисленных через запятую, нам необходимо разбить текст таким образом, чтобы каждое число было в отдельной ячейке (вместо запятых это могут быть любые другие знаки, в том числе и пробелы). То есть нам необходимо разбить текст по словам.
Напомним, что вручную (без формул) это задача очень просто решается с помощью инструмента текст по столбцам, который мы уже рассматривали. В нашем же случае требуется это сделать с помощью формул. Для начала необходимо найти общий разделить, по которому мы будет разбивать текст.
В нашем случае это запятая, но например в первой задаче мы делили ФИО и разделитель был пробел. Наш второй пример более универсальный (более удобный при большом количестве данных), так например мы удобно могли бы делить не только ФИО по отдельным ячейкам, а целое предложение — каждое слово в отдельную ячейку.
Собственно такой вопрос поступил в комментариях, поэтому было решено дополнить эту статью. Для удобства в соседнем столбце укажем этот разделитель, чтобы не прописывать его в формуле а просто ссылаться на ячейку. Это так же позволит нам использовать файл для решения других задач, просто поменяв разделитель в ячейках.
Теперь основная суть приема.
Шаг 1. В вспомогательном столбце находим позицию первого разделителя с помощью функции НАЙТИ. Описывать подробно функцию не буду, так как мы уже рассматривали ее раннее. Пропишем формулу в D1 и протянем ее вниз на все строки
То есть ищем запятую, в тексте, начиная с позиции 1
Шаг 2. Далее в ячейке E1 прописываем формулу для нахождения второго знака (в нашем случае запятой). Формула аналогичная, но с небольшими изменениями.
Пропишем формулу и протянем ее вниз.
Шаг 3. Находимо позиции всех остальных разделителей. Для этого формулу нахождения второго разделителя (шаг 2) протянем вправо на то количество ячеек, сколько всего может быть отдельно разбитых значений с небольшим запасом. Получим все позиции разделителей. Там где ошибка #Знач означает что значения закончились и формула больше не находит разделителей. Получаем следующее
Шаг 4. Отделяем первое число от текст с помощью функции ПСТР.
Начальная позиция у нас 1, количество знаков мы рассчитываем как позиция первого разделителя минус 1: D1-1 протягиваем формулу вниз
Шаг 5. Находимо второе слово так же с помощью функции ПСТР в ячейке P1
Начальная позиция второго числа у нас начинается после первой запятой. Позиция первой запятой у нас есть в ячейке D1, прибавим единицу и получим начальную позицию нашего второго числа.
Количество знаков это есть разница между позицией третьего разделителя и второго и минус один знак, то есть E1-D1-1 Закрепим столбец A исходного текста, чтобы он не сдвигался при протягивании формулы право.
Шаг 6. Протянем формулу полученную на шаге 5 вправо и вниз и получим текст в отдельных ячейках.
Шаг 7. В принципе задача наша уже решена, но для красоты все в той же ячейке P1 пропишем формула отлавливающую ошибку заменяя ее пустым значением.
Так же можно сгруппировать и свернуть вспомогательные столбцы, чтобы они не мешали. Получим итоговое решение задачи
Примечание. Первую позицию разделителя и первое деление слова мы делали отлично от других и из-за этого могли протянуть формулу только со вторых значений.
Во время написания задачи я заметил, что можно было бы упростить задачу. Для этого в столбце С нужно было прописать 0 значения первого разделителя. После этого находим значение первого разделителя
а первого текста как
После этого можно сразу протягивать формулу на остальные значения. Именно этот вариант оставляю как пример для скачивания. В принципе файлом можно пользоваться как шаблоном.
В столбец «A» вставляете данные, в столбце «B» указываете разделитель, протягиваете формулы на нужное количество ячеек и получаете результат.
Внимание! В комментариях заметили, что так как в конце текста у нас нет разделителя, то у нас не считается количество символов от последнего разделителя до конца строки, поэтому последний разделенный текст отсутствует.
Чтобы решить вопрос можно либо на первом шаге добавить вспомогательный столбец радом с исходным текстом, где сцепить этот текст с разделителем. Таким образом у нас получится что на конце текста будет разделитель, значит наши формулы посчитают его позицию и все будет работать.
Либо второе решение — это на шаге 3, когда мы составляем формулу вычисления позиций разделителей дополнить ее. Сделать проверку, если ошибка, то указываем заведомо большое число, например 1000.
Таким образом последний текст будет рассчитываться начиная от последней запятой до чуть меньше 1000 знаков, то есть до конца строки, что нам и требуется.
Оба варианта выложу для скачивания.