как разбить адрес по столбцам в excel
Как разбить адрес по столбцам в excel
В этой статье показаны методы разбиения или разделения нескольких адресов на отдельные части в Excel.
Вы можете использовать Текст в столбцы возможность разбивать адрес на отдельные части на основе разделителя в Excel. Пожалуйста, сделайте следующее.
1. Выделите ячейки с адресами, которые нужно разбить на части, и нажмите Данные > Текст в столбцы.
2. В первом Преобразовать текст в столбецволшебник, выберите разграниченный и нажмите Next кнопка. Смотрите скриншот:
3. Во втором мастере отметьте опцию разделителя на основе разделителя вашего адреса (в этом случае я проверяю Запятая коробка). Щелкните значок Next кнопку.
4. В последнем мастере выберите ячейку для размещения разделенных частей адреса в Destination поле и, наконец, щелкните Завершить кнопка. Смотрите скриншот:
Вы можете видеть, что выбранные адреса разделены на части, как показано на скриншоте ниже.
Была основана Разделить клетки полезности Kutools for Excel можно легко разбить или разделить адрес в выбранных ячейках на отдельные части в Excel. Пожалуйста, сделайте следующее.
Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.
1. Выберите ячейки с адресом, который нужно разбить, затем нажмите Kutools > Слияние и разделение > Разделить клетки.
2. в Разделить клетки диалоговое окно, настройте следующим образом.
Внимание: В данном случае я выбираю Другое вариант и введите в поле символ запятой.
3. В секунду Разделить клетки диалоговом окне выберите ячейку для размещения разделенных частей, а затем щелкните значок OK кнопку.
Затем все выбранные адреса сразу разделяются на отдельные части.
Если вы хотите получить 30-дневную бесплатную пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.
Как разбить текст по столбцам в 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. Для разбивки текста по столбцам используются различные методы, которые определяются исходя из предложенной информации, необходимости получения конечного результата и степени профессионализма пользователя.
Необходимо разделить ФИО по отдельным столбцам
Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:
От эксперта! Для разделения текста могут быть использованы запятые, точки, двоеточия, точки с запятой, пробелы и другие знаки.
Замечание эксперта! Размещенный отформатированный текст из-за разного количества символов в ФИО может не вмещаться в выбранные ячейки, поэтому полученная таблица нуждается в корректировке. Для этого используется расширение размеров ячейки.
Разделение текста с помощью формулы
Для самостоятельного разделения текста могут быть использованы сложные формулы. Они необходимы для точного расчета позиции слов в ячейке, обнаружения пробелов и деления каждого слова на отдельные столбцы. Для примера будем также использовать таблицу с ФИО. Чтобы произвести разделение, потребуется выполнить три этапа действий.
Этап №1. Переносим фамилии
Чтобы отделить первое слово, потребуется меньше всего времени, потому что для определения правильной позиции необходимо оттолкнуться только от одного пробела. Далее разберем пошаговую инструкцию, чтобы понять для чего нужны вычисления в конкретном случае.
В результате можно видеть, что ячейка откорректирована и фамилия внесена корректно. Чтобы изменения вступили в силу на всех строках, потяните маркер выделения вниз.
Этап №2. Переносим имена
Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.
Этап №3. Ставим Отчество
Примечание эксперта! Формула определит автоматически количество символов.
Заключение
В статье прошло ознакомление с двумя распространенными способами разделения информации в ячейках по столбцам. Следуя нехитрым инструкциям, можно с легкостью освоить владение данными способами и использовать их на практике. Сложность разделения по столбцам, используя формулы, может оттолкнуть с первого раза неопытных пользователей Excel, но практическое применение метода, поможет привыкнуть к нему и применять его в дальнейшем без каких-либо проблем.
Разделение текста по столбцам с помощью функций
Для управления строками текста в данных можно использовать текстовые функции LEFT, MID, RIGHT, SEARCH и LEN. Например, можно распределить имя, середину и фамилию из одной ячейки в три отдельных столбца.
Для распределения компонентов имен с текстовыми функциями важно положение каждого символа в текстовой строке. Позиции пробелов в текстовой строке также важны, поскольку они указывают на начало или конец компонентов имени в строке.
Например, в ячейке, содержавшей только имя и фамилию, фамилия начинается после первого висячего пробела. Некоторые имена в списке могут содержать от среднее имя, в этом случае фамилия начинается после второго пробела.
В этой статье показано, как извлекать компоненты из различных форматов имен с помощью этих удобных функций. Текст также можно разделить на разные столбцы с помощью мастера преобразования текста в столбцы.
Один средний начальный
Два средних инициала
Имя, в которое входит две части
Фамилия из трех части
Фамилия с двумя частьми
Фамилия и суффикс с запятой
Фамилия с дефисами
Примечание: На рисунках в следующих примерах выделение в полном имени показывает символ, который ищется в формуле ПОИСК.
В этом примере разделяется два компонента: имя и фамилия. Два имени отделены одним пробелом.
Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.
Имя начинается с первого знака в строке (Ю) и заканчивается пятым знаком (пробелом). Формула возвращает пять знаков в ячейке A2, начиная слева.
Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.
Найдите позицию пробела в A2, начиная слева.
Фамилия отделена от имени пробелом, начинается с пятого знака справа и заканчивается первым знаком справа (а). Формула извлекает в A2 пять знаков справа.
Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК и ДЛСТР.
Найдите позицию пробела в A2, начиная слева. (5)
Подсчитайте общую длину текстовой строки, а затем вычтите количество знаков слева до первого пробела, найденное в пункте 1.
В этом примере используются имя, отс. инициал и фамилия. Каждый компонент имен разделяется пробелом.
Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.
Один средний начальный
Результат (средний начальный)
‘=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-SEARCH(» «;A2;1))
=MID(A2;ПОИСК(» «;A2;1)+1;ПОИСК(» «;A2;ПОИСК(» «;A2;1)+1)-ПОИСК(» «;A2;1))
Live Result (фамилия)
‘=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))
=ПРАВАЯ(A2;LEN(A2)-SEARCH(» «;A2;ПОИСК(» «;A2;1)+1))
Имя начинается с первого знака слева (E) и заканчивается пятым (первым пробелом). Формула извлекает первые пять знаков в A2, начиная слева.
Для поиска значения «число_знаков» следует воспользоваться функцией ПОИСК.
Найдите позицию пробела в A2, начиная слева. (5)
От второе имя начинается с шестой позиции (S) и заканчивается в 1-й позиции (второй пробел). Эта формула включает вложенные функции ПОИСК для поиска второго экземпляра пробела.
Формула извлекает три знака, начиная с шестой позиции.
Для поиска значения «начальная_позиция» следует воспользоваться функцией ПОИСК:
Поищите позицию первого пробела в A2, начиная с первого знака слева. (5).
Чтобы получить позицию знака после первого пробела (S), добавьте 1. Эта позиция является начальной позицией от имени. (5 + 1 = 6)
Для поиска значения «число_знаков» следует воспользоваться вложенной функцией ПОИСК.
Поищите позицию первого пробела в A2, начиная с первого знака слева. (5)
Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 4. Этот номер знака является конечной позицией от имени. (8)
Поищите позицию пробела в A2, начиная с первого знака слева. (5)
Возьмите номер знака второго пробела, найденного на шаге 5, и вычитайте номер первого пробела, найденного на шаге 6. Результатом является количество символов, извлекаемых СТП из текстовой строки, начиная с шестой позиции, найденной на шаге 2. (8 –5 = 3)
Фамилия начинается с шести знаков справа (K) и заканчивается первым знаком справа (n). Эта формула включает вложенные функции ПОИСК для поиска второго и третьего экземпляров пробела (которые находятся на пятой и пятой позициях слева).
Формула извлекает шесть знаков в A2, начиная с правого.
Используйте функции LEN и вложенные функции ПОИСК, чтобы найти значение для num_chars:
Поищите позицию пробела в A2, начиная с первого знака слева. (5)
Чтобы получить позицию знака после первого пробела (S), добавьте 1. Результат — это номер символа, с которого нужно начать поиск второго пробела. (5 + 1 = 6)
Поиск второго пробела в A2, начиная с шестой позиции (S), найденной в шаге 2. Этот номер знака является конечной позицией от имени. (8)
Подсчитайте общую длину текстовой строки в A2 и вычитайте количество знаков слева до второго пробела, найденного на шаге 3. Результат — количество символов, извлекаемого справа от полного имени. (14 – 8 = 6).
Вот пример того, как извлечь два средних инициала. Компоненты имен отделяют первый и третий пробелы.
Скопируйте ячейки таблицы и в таблицу Excel ячейку A1. Формула слева будет отображаться для справки, а Excel автоматически преобразует формулу справа в соответствующий результат.
Совет. Перед тем как врезать данные в таблицу, установите для столбцов A и B ширину 250.