ГлавнаяРазноеКак разбить в экселе текст по строкам

Разделить текст в ячейке Excel по столбцам. - EXCEL-OFFICE.RU. Как разбить в экселе текст по строкам


Разбить по строкам/столбцам

MulTEx » 1 Май 2011       Дмитрий       50184 просмотров
Данная функция является частью надстройки MulTEx

Эта команда разбивает данные во всех выделенных ячейках на части, используя в качестве разделителя указанные символ/символы. Главная особенность - в отличие от стандартной команды Excel "Разбить по столбцам", данная команда заносит результат разбиения данных в строки либо столбцы в одну строку/столбец, а не каждую часть в свой столбец. К тому же в стандартной команде Excel нет возможности указать несколько символов - только один.

Диапазон значений - указывается диапазон ячеек, данные которых необходимо разбить. Может быть как одним столбцом/строкой, так и диапазоном, состоящим из нескольких строк и нескольких столбцов.Разделитель - указывается символ (или группа символов), который будет применен в качестве разделителя. Если разделитель не указан, то появится сообщение, информирующее об этом. В сообщении будет предложено использовать разделитель по умолчанию (Пробел). В случае отказа Вы сможете указать необходимый разделитель. Данные, внесенные ранее не будут утеряны.

    Специальный - можно добавить в поле Разделитель один из специальных символов: неразрывный пробел или перенос на строки. Стандартно такие символы невозможно ввести с клавиатуры, но они могут часто встречаться в ячейках. Например, эти символы часто применяется в выгрузках из различных программ(вроде 1С): неразрывный пробел для разделения цифр или слов, которые не надо переносить на строки, а перенос на строки - для разделения основания платежей и иной информации.Если в поле Разделитель уже указан какой-либо символ, то при выборе дополнительно специального символа будет показано сообщение:
  • ДА(YES) - в поле Разделитель будет добавлен указанный специальный символ. Указанные ранее символы не будут удалены.
  • НЕТ(NO) - поле Разделитель будет очищено и в него будет помещен только выбранный специальный символ.

Поместить результат:

  • Начиная с ячейки - одна ячейка, начиная с которой будет выводиться результат. Не может находиться в Диапазоне значений. В случае, если Диапазон значений состоит из нескольких строк и столбцов, данные будут разбиты в соответствии с расположением данных:
  • В одну ячейку - результирующие значения будут помещены в одну ячейку, разделенные между собой указанным разделителем:

Разделитель результата:Доступно только если в качестве вывода результата указана одна ячейка. По умолчанию разделителем является перенос на строки. В качестве разделителя можно указать любой символ или группу символов.

  • Перенос на строки - каждое новое результирующее значение в ячейке будет записано с новой строки:
  • Произвольный - любой символ, буква, цифра или группа символов(словосочетания, цифры и т.п.).

Транспонировать - если Диапазон значений представляет собой столбец, то данные после обработки будут помещены в одну строку. Если Диапазон значений - строка, то данные будут помещены в столбец. Недоступно при выводе результата в одну ячейку.

www.excel-vba.ru

Делим слипшийся текст на части

Итак, имеем столбец с данными, которые надо разделить на несколько отдельных столбцов. Самые распространенные жизненные примеры:

  • ФИО в одном столбце (а надо - в трех отдельных, чтобы удобнее было сортировать и фильтровать) 
  • полное описание товара в одном столбце (а надо - отдельный столбец под фирму-изготовителя, отдельный - под модель для построения, например, сводной таблицы)
  • весь адрес в одном столбце (а надо - отдельно индекс, отдельно - город, отдельно - улица и дом)
  • и т.д.

Поехали..

Способ 1. Текст по столбцам

Выделите ячейки, которые будем делить и выберите в меню Данные - Текст по столбцам (Data - Text to columns). Появится окно Мастера разбора текстов:

На первом шаге Мастера выбираем формат нашего текста. Или это текст, в котором какой-либо символ отделяет друг от друга содержимое наших будущих отдельных столбцов (с разделителями) или в тексте с помощью пробелов имитируются столбцы одинаковой ширины (фиксированная ширина).

На втором шаге Мастера, если мы выбрали формат с разделителями (как в нашем примере) - необходимо указать какой именно символ является разделителем:

Если в тексте есть строки, где зачем-то подряд идут несколько разделителей (несколько пробелов, например), то флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) заставит Excel воспринимать их как один.

Выпадающий список Ограничитель строк (Text Qualifier) нужен, чтобы текст заключенный в кавычки (например, название компании "Иванов, Манн и Фарбер") не делился по запятой внутри названия.

И, наконец, на третьем шаге для каждого из получившихся столбцов, выделяя их предварительно в окне Мастера, необходимо выбрать формат:

  • общий - оставит данные как есть - подходит в большинстве случаев
  • дата - необходимо выбирать для столбцов с датами, причем формат даты (день-месяц-год, месяц-день-год и т.д.) уточняется в выпадающем списке
  • текстовый - этот формат нужен, по большому счету, не для столбцов с ФИО, названием города или компании, а для столбцов с числовыми данными, которые Excel обязательно должен воспринять как текст. Например, для столбца с номерами банковских счетов клиентов, где в противном случае произойдет округление до 15 знаков, т.к. Excel будет обрабатывать номер счета как число:

Кнопка Подробнее (Advanced) позволяет помочь Excel правильно распознать символы-разделители в тексте, если они отличаются от стандартных, заданных в региональных настройках.

Способ 2. Как выдернуть отдельные слова из текста

Если хочется, чтобы такое деление производилось автоматически без участия пользователя, то придется использовать небольшую функцию на VBA, вставленную в книгу. Для этого открываем редактор Visual Basic:

  • в Excel 2003 и старше - меню Сервис - Макрос - Редактор Visual Basic (Tools - Macro - Visual Basic Editor)
  • в Excel 2007 и новее - вкладка Разработчик - Редактор Visual Basic (Developer - Visual Basic Editor) или сочетание клавиш Alt+F11

Вставляем новый модуль (меню Insert - Module) и копируем туда текст вот этой пользовательской функции:

Function Substring(Txt, Delimiter, n) As String Dim x As Variant x = Split(Txt, Delimiter) If n > 0 And n - 1 <= UBound(x) Then Substring = x(n - 1) Else Substring = "" End If End Function

Теперь можно найти ее в списке функций в категории Определенные пользователем (User Defined) и использовать со следующим синтаксисом:

=SUBSTRING(Txt; Delimeter; n)

где

  • Txt - адрес ячейки с текстом, который делим
  • Delimeter - символ-разделитель (пробел, запятая и т.д.)
  • n - порядковый номер извлекаемого фрагмента

Например:

Способ 3. Разделение слипшегося текста без пробелов

Тяжелый случай, но тоже бывает. Имеем текст совсем без пробелов, слипшийся в одну длинную фразу (например ФИО "ИвановИванИванович"), который надо разделить пробелами на отдельные слова. Здесь может помочь небольшая макрофункция, которая будет автоматически добавлять пробел перед заглавными буквами. Откройте редактор Visual Basic как в предыдущем способе, вставьте туда новый модуль и скопируйте в него код этой функции:

Function CutWords(Txt As Range) As String Dim Out$ If Len(Txt) = 0 Then Exit Function Out = Mid(Txt, 1, 1) For i = 2 To Len(Txt) If Mid(Txt, i, 1) Like "[a-zа-я]" And Mid(Txt, i + 1, 1) Like "[A-ZА-Я]" Then Out = Out & Mid(Txt, i, 1) & " " Else Out = Out & Mid(Txt, i, 1) End If Next i CutWords = Out End Function

Теперь можно использовать эту функцию на листе и привести слипшийся текст в нормальный вид:

 Ссылки по теме

 

www.planetaexcel.ru

Как разделить текст в MS Excel

Многие знают, что для того, чтобы объединить текст в двух ячейках достаточно воспользоваться функцией СЦЕПИТЬ (CONCATENATE), однако, как быть, если необходимо не объединить, а наоборот разделить текст в ячейке? Если количество символов, которое необходимо отделить известно (не важно справа или слева), тогда можно воспользоваться функциями ЛЕВСИМВ (LEFT) или ПРАВСИМВ (RIGHT), в зависимости с какой стороны необходимо выделить определенное количество символов.

Однако, как быть, если необходимо разделить ячейку в которой заведомо не известно количество символов, которые нужно отделить, а известно лишь сколько частей необходимо получить в результате операции. Самым простым примером такой ситуации может быть необходимость выделить из ячейки в которой занесено ФИО человека, отдельно фамилию, имя и отчество. Фамилии у всех разные, поэтому заранее узнать количество символов, которые необходимо отделить, не получится.

На Ваше обозрение представим два способа разделения текста. Один очень быстрый – для тех кому надо просто разделить текст заменив имеющийся, а второй с использованием формул.

Первый способ – супер быстрый.

На самом деле в MS Excel существует встроенная возможность быстрого разделения текста в ячейке, если там присутствует или присутствуют разделитель/разделители (например, простой пробел или запятая). Причем таких разделителей может быть несколько, т.е. текст будет разделятся если в строке присутствует или пробел, или точка с запятой или запятая и т.д.

Для этого необходимо выделить ячейки с текстом, который необходимо разделить и воспользоваться командой "Текст по столбцам".

В англоязычной версии MS Excel данная команда звучит как "Text to Columns" вкладки "DATA".

После несложных подсказок мастера (на самом деле, в нашем примере после выбора разделителя – пробела можно смело жать "Готово")

А вот и, собственно, результат.

Второй способ – с использованием формул.

В такой ситуации понадобится сочетание функций: ПОИСК (SEARCH) и ПСТР (MID). Для начала, с помощью первой находим пробел между словами (между фамилией и именем и именем и отчеством), а потом подключаем вторую для того, чтобы выделить необходимое количество символов. Грубо говоря, первой функцией определяем количество символов, а второй – уже разделяем.

Кроме того, поскольку разделительного знака в конце строки нет, то количество символов в последнем слове (нашем случае – отчестве) вычислить не удастся, но это не проблема, достаточно указать заведомо бОльшее количество символов в качестве аргумента «число_знаков» функции ПСТР, например, 100.

Рассмотрим вышесказанное на примере. Сначала, для лучшего понимания, разнесем формулы и, таким образом, разделим весь процесс на два этапа.

Для того, чтобы определить количество символов, которые необходимо выделить в строке, необходимо определить позиции разделителей (в нашем случае пробела) их будет на один меньше нежели слов в ячейке.

Поскольку информация о количестве необходимых символах получена, следующим этапом будет использование функции ПСТР (MID).

Здесь, в качестве аргументов, используются промежуточные значения, полученные с помощью функции ПОИСК. Для последней колонки количество символов неизвестно, поэтому было взято заведомо бОльшее количество символов (в нашем случае 100).

Теперь попробуем соединить промежуточные расчеты в одну формулу.

Если текст в ячейке необходимо разделить лишь на две части, то необходимо произвести поиск лишь одного пробела (либо другого разделителя, который находится между словами), а для разделения на 4 и больше частей формулу придется усложнить поиском 3го, 4го и т.д. разделителей.

Ниже представлено видео проделанных изменений.

msoffice-prowork.com

Как в Excel разделить ячейку

Microsoft Office Excel — программа для редактирования данных. Информация в файле представлена в виде таблицы, которая состоит из строк и столбцов. Наименьшая единица измерения в документе Excel — ячейка. Эти элементы имеют условный номер, который определяется их отношением к столбцу и строке, например, A1 или D13. Вы можете изменять ширину и высоту ячеек, придавая им желаемый вид, чтобы форма, размер и соотношение сторон соответствовали требованиям. Кроме того, можно объединять соседние элементы с любой из сторон или отменять разделение, чтобы подстраивать структуры таблицы. К сожалению, так как ячейка является минимальной единицей файла в Excel, разделить её нельзя.

Электронные таблицы Excel очень популярны и часто эксплуатируются для работы с данными. Иногда у юзеров возникает необходимость разделить ячейку на две или несколько частей, но такой возможности в программе нет. Тем не менее есть способы обойти это ограничение и придать таблице желаемый вид.

Потребность разделить ячейку может появляться в том случае, если в одном из столбцов должно содержаться два и более элемента данных. Например, два или несколько названий определённого предмета, тогда как другие в столбце «наименование» имеют лишь одно. Кроме того, некоторым пользователям требуется разбить данные, которые содержатся в одной ячейке, на несколько. Самый распространённый пример — разделение ФИО человека отдельно на фамилию, имя и отчество, чтобы каждое значение занимало отдельную клетку. В обоих случаях вы можете выполнить разделение с помощью таких функций, как объединение ячейки, «Текст по столбцам», «Мгновенное заполнение» и множества формул, которые настраиваются индивидуально для каждого случая.

Как разделить ячейку в таблице Excel с помощью планирования структуры

Excel имеет определённую структуру таблицы — это нужно для того, чтобы было проще ориентироваться в данных, а также не было ошибок в формулах и расчётах, проблем с комбинацией и считыванием. Каждая ячейка имеет свой индивидуальный номер, который обусловлен её положением на осях цифр и латинских букв. Например, буквенно-числовой адрес первого элемента в таблице — A1. Одной строке соответствует одна ячейка таблицы, и наоборот. Это значит, что она является минимальным элементом и не может быть разделена на два или более самостоятельных.

В некоторых случаях юзеру Excel нужно сделать так, чтобы в одном столбце было два или более значения для пересечения с одной из строк. Например, в том случае, когда та или иная вещь имеет несколько названий или номеров, а данные остальных умещаются в одну  ячейку. Аналогично со строками, если один столбец включает в себя первичные категории (например, «программисты», «дизайнеры» и так далее), а другой — вторичные («программисты» — Иван, Пётр). Несмотря на то что в редакторе нельзя сделать это напрямую, ограничение можно обойти. Для этого нужно:

  1. Заранее запланировать, какое максимальное количество значений будет включать в себя строка или столбец;
  2. На этапе подготовки листа Excel к работе объединить те ячейки столбца или строки, которые будут использоваться как одинарные;
  3. Таким образом, «разделённые» ячейки будут представлять собой самостоятельные элементы, а «целые» — соединённые, то есть результат получится визуальным (но он всё ещё будет соответствовать требованиям Excel).

Пример: в столбце A и строках 1–5 у вас расположено 5 фамилий, а соседний столбец B содержит должности этих людей в компании. Если один или несколько человек занимают 2 должности, впишите вторую в столбец C, а для остальных просто объедините B1 и C1, B2 и C2 и так далее. Аналогично в тех случаях, когда одному значению в первом столбце соответствует более 2 в последующих. Каждая ячейка будет иметь свой адрес в Excel и останется полностью функционирующей.

Как разбить ячейки, объединённые при планировании структуры

Если после действий, подобных описанным в предыдущем пункте, вы решили, что всей странице нужно вернуть прежнее состояние и разбить ячейки:

  1. Откройте нужный лист, выделите все ячейки (или определённую часть) и перейдите во вкладку «Главная» на верхней панели Excel;
  2. В области «Выравнивание» нажмите на стрелку и откройте выпадающий список с функциями «Объединить и поместить в центре», затем выберите «Отменить объединение ячеек»;
  3. Элементы будут разбиты на одинарные, но все данные сместятся в левый верхний — распределить их по столбцам вы сможете с помощью функции «Текст по столбцам», которую мы рассмотрим дальше.

Как визуально разбить ячейку в таблице Excel, как разделить элемент по диагонали

Если вам достаточно разбить ячейку визуально, не изменяя её свойств и адреса в Excel, нужно:

  1. Установить курсор на необходимый элемент либо выделить несколько (или весь лист).
  2. Открыть вкладку «Главная», в области «Ячейки» кликнуть «Формат».
  3. Откроется выпадающее меню, где вам нужно выбрать «Формат ячеек».
  4. В новом окне необходимо перейти во вкладку «Граница» — здесь вы сможете самостоятельно нарисовать нужные рамки ячейки (вертикальные, горизонтальные и диагональные линии, несколько вариантов линий и множество цветов).
  5. Есть и другой вариант — нужно кликнуть по выделенным ячейкам правой клавишей мыши, чтобы вызвать контекстное меню, затем выбрать «Формат ячеек», перейти во вкладку «Граница» и точно так же сделать линии.
  6. Одна или несколько выбранных ячеек получит разметку, которую вы указали.

Для того чтобы сделать ячейку, которая будет включать в себя название строк и столбцов одновременно, нужно сделать:

  1. В окне «Формат ячеек», во вкладке «Граница» нарисовать любую диагональную линию, которая идёт из левого верхнего в правый нижний угол.
  2. Применить форматирование.
  3. Ввести текст в «верхнюю часть» ячейки (она разделена только визуально), которая будет соответствовать строке, например, «название»).
  4. Выровнять его по левому или правому краю, расположить точнее с помощью пробелов.
  5. Во время редактирования элемента нажать Alt + Enter, чтобы перейти на новую строку, затем ввести текст для столбца, например, «количество»;
  6. Если текст расположен или выглядит не так, как нужно, необходимо поменять его местоположение с помощью пробела либо изменить размер и соотношение сторон ячеек.

Как разбить данные ячейки по столбцам таблицы Excel с помощью разделителя

Если у вас есть ячейки, которые заполнены некоторыми данными, и их нужно распределить по столбцам по категориям, используйте функцию разделения. Она отлично подойдёт в том случае, когда в элементах указана информация, например, о приглашённых людях — имя, фамилия, номер билета, их город или страна, дата прибытия. Если всё это было перенесено из текстового документа, оно не будет иметь форматирования. Для того чтобы было удобнее работать в Excel, данные нужно разбить по соответствующим столбцам — «имя», «фамилия» и так далее.

Делается это так:

  1. Создайте новые пустые столбцы, если справа от того, который содержит информацию, их недостаточно (их должно быть не меньше, чем количество категорий данных), иначе сведения будут записаны на другие, уже заполненные. Поставьте курсор мыши после искомого столбца на строке с латинскими буквами и нажмите по рамке таблицы правой клавишей мыши, в контекстном меню выберите «Вставить». Если нужно добавить несколько пустых столбцов, перед этим выберите аналогичное количество справа от искомого (нажмите на ячейку с латинской буквой и перетащите выделение).
  2. Выделите тот столбец, который нужно разбить. Откройте «Данные» — «Работа с данными» — «Текст по столбцам».
  3. В новом окне (Мастер распределения текста по столбцам) выберите формат данных. Если в искомом столбце информация разных категорий разделена пробелами или запятыми, выберите «С разделителями», если она имеет фиксированное количество данных — «Фиксированной ширины» (например, цифровой идентификатор — рассмотрим этот вариант позже), нажмите «Далее».
  4. Затем укажите разделители, которые используются в массиве текста в столбце. Укажите их в «Символом-разделителем является» (если их несколько, перечислите все в поле «Другой»). Также укажите «Считать последовательные разделители одним», если в данных встречаются несколько типов подряд (например, два пробела подряд либо точка, которая говорит о сокращении слова, а не о конце предложения, и после неё идёт запятая).
  5. Настройте ограничитель строк, если в тексте встречаются предложения, которые выделены кавычками, а в них встречаются разделители из прочего пункта, но разбивать их нельзя. К таким относятся предложения вроде «Россия, Москва» — адрес в таком случае должен оставаться целостным. Если вы не установите ограничитель, «Россия» и «Москва» окажутся в разных столбцах.
  6. Выберите формат данных. По умолчанию он «Общий». Если в вашей информации есть даты или суммы денежных средств, укажите соответствующие для того столбцы, в которых они будут размещены. Здесь вы также можете указать, куда будут помещены те или иные данные. Кликните по иконке выбора диапазона справа от «Поместить в» и в качестве первого столбца укажите крайний левый из свободных, которые должны быть заполнены. К сожалению, данные нельзя переместить в другую книгу Excel и даже на другой лист, но вы можете разбить их на текущем, после чего просто скопировать в нужное место.
  7. Нажмите «Готово» — все настройки будут применены. Сохраните документ, чтобы не утерять их.

Как распределить данные ячейки по столбцам таблицы Excel с помощью мгновенного заполнения

Начиная с версии 2013 года, Microsoft Office Excel предлагает возможность утилизировать «Мгновенное заполнение». С помощью данной функции вы можете заставить редактор автоматически распределить данные в ячейки столбца, как только он заметит закономерность ввода.

Работает опция так: Excel начинает анализировать данные, которые вы вписываете в ячейки рабочего листа, и пытается выяснить, откуда они берутся, чему соответствуют и есть ли в них закономерность. Так, если в столбце A у вас указаны фамилии и имена людей, а в B вы вводите фамилии, с помощью «Мгновенного заполнения» утилита посчитает этот принцип и предложит автоматически распределить все значения в столбец B.

С помощью указанного инструмента вам достаточно ввести в новый столбец лишь часть данных — учитывая, что функция работает в пассивном режиме, она очень удобная. Чтобы её активировать и использовать:

  1. Убедитесь, что у вас активировано «Мгновенное заполнение» — оно находится во вкладке «Файл» — пункт «Параметры» — «Дополнительно» — «Автоматически выполнять мгновенное заполнение» (установите галочку, если её там нет).
  2. Начните вводить в один из столбцов данные из другого, и редактор сам предложит распределить информацию массово. Если вам подходит то, что предлагает система Excel, нажмите Enter.
  3. Если функция активирована, но не срабатывает в рамках определённого шаблона, запустите инструмент вручную в «Данные» — «Мгновенное заполнение» или нажмите Ctrl + «E».

Как распределить данные ячейки по столбцам таблицы Excel с помощью формул

В Excel есть формулы, которые позволяют упростить разбитие данных и сделать это более функционально. Как правило, обычно достаточно команд «ЛЕВСИМВ», «ПСТР», «ПРАВСИМВ», «НАЙТИ», «ПОИСК» И «ДЛСТР». Давайте рассмотрим, когда они нужны и как их использовать.

Как разделить имя и фамилию в 2 столбца

Один из самых частых случаев — потребность разделить имена и фамилии из столбца A в B и C соответственно. Для этого нужно сделать так, чтобы редактор сам нашёл пробел между значениями и разбил всё автоматически. Используйте формулу «=ЛЕВСИМВ(A2;ПОИСК(» «; A2;1)-1)». Она ищет пробелы в поисках, после чего принимает их за разделитель и отправляет, например, фамилии в левый из двух столбцов, а имена — в правый. Аналогично с другими значениями, которые разбиты пробелами. Указанная формула не подходит для более сложных ячеек, в том числе имён с фамилиями и отчествами, суффиксами и прочими данными.

Как разделить имя, фамилию и отчество в 3 столбца

Если вам нужно разбить на столбцы полное ФИО из трёх значений (при этом любое из них может быть только в виде буквенного инициала):

  1. Используйте формулу «=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-1)», чтобы отделить имя;
  2. Используйте «=ПСТР(A2;НАЙТИ(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))», чтобы найти отчество (в записи вроде «Иванов Иван Иванович»)
  3. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))», чтобы извлечь фамилию.

Эти же формулы можно эксплуатировать для записей вроде «Иванов Иван младший» (на западный манер) или других, содержащих суффикс.

Как распределить данные, если они разделены запятой

Если данные в ячейках записаны в виде «Black, Bob Mark» (полное имя с фамилией спереди — в английском языке в таком случае требуется постановка запятой), разделить их в привычные нам «Bob Mark White» можно так:

  1. Используйте «=ПСТР(A2;ПОИСК(» «;A2;1)+1;НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1)-(НАЙТИ(» «;A2;1)+1))», чтобы выделить имя;
  2. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ(» «;A2;НАЙТИ(» «;A2;1)+1))», чтобы извлечь отчество;
  3. Используйте «=ЛЕВСИМВ(A2;НАЙТИ(» «;A2;1)-2)», чтобы извлечь фамилию».

Другие формулы

Excel позволяет работать не только с именами и фамилиями людей, но и с другими типами данных. Другой часто встречающийся пример — адреса. Если в ячейке записана информация вида «Россия, Москва, улица Арбат», вы можете распределить значения по другим элементам, в качестве разделителя указав запятую, точку или другой произвольный символ. Чтобы разбить такой адрес из 3 частей (страна, город, улица):

  1. Используйте «=ЛЕВСИМВ(A2;ПОИСК(«,»;A2)-1)», чтобы отделить страну;
  2. Используйте «=ПСТР(A2;ПОИСК(«,»;A2)+2;ПОИСК(«,»;A2;ПОИСК(«,»;A2)+2)-ПОИСК(«,»;A2)-2)», чтобы выделить город;
  3. Используйте «=ПРАВСИМВ(A2;ДЛСТР(A2)-(ПОИСК(«,»;A2;ПОИСК(«,»;A2)+1)+1))», чтобы отделить улицу.

Таким образом, назначение указанной формулы — разделять данные в том месте, где встречается заданный значок (в данном случае — запятая). Просто укажите его в кавычки.

Итог

Microsoft Office Excel предлагает широкие возможности для работы как с сеткой таблицы, так и с её содержимым. Несмотря на то что нет функции для дробления ячейки на несколько частей, вы можете добиться результата, планируя структуру и группируя элементы. Если форматирование вас не устроит, его можно отменить на всём листе. С помощью границ можно разделить элемент по диагонали, чтобы добиться расположения названия столбцов справа сверху, а строк — слева снизу. Если вы хотите распределить массив информации по ячейкам других столбцов, используйте формулы, функции «Мгновенное заполнение» или «Текст по столбцам».

nastroyvse.ru

Разделить текст в ячейке Excel по столбцам.

Как сделать в Excel. Разделить текст в ячейке Excel по столбцам.       Рассмотрим, как разделить текст в ячейках Excel по столбцам формулой. В Excel есть специальная функция – «Текст по столбцам». Где она находится и как с ней работать, смотрите в статье «Разделить текст Excel на несколько ячеек» тут.      Здесь рассмотрим, как разделить в Excel формулой текст числа, коды на составляющие.У нас такая таблица с данными.В столбце А написан код, составленный через слеш из кода и года. Нам нужно их разделить.      В ячейке В27 пишем такую формулу.   =ЗНАЧЕН(ЛЕВСИМВ(A27;НАЙТИ("/";A27;1)-1))      Пояснение к формуле.В конце формулы мы написали «-1». Это для того, чтобы знак слеша не писался с кодом в столбце В.      Копируем формулу по столбцу. Получится так.В столбце С устанавливаем формат «Дата». Какими способами установить формат, смотрите в статье «Число Excel. Формат» здесь.      В ячейке С27 пишем такую формулу.   =ДАТАЗНАЧ(ПСТР(A27;НАЙТИ("/";A27;1)+1;255))      Пояснение в формуле.В формуле написали «+1» - это значит, чтобы в ячейке с датой не был написан слеш (косая черточка).«255» - это количество символов, которые могут отобразиться в ячейке. По факту, отображается столько символов, сколько есть в дате (после слеша).      Копируем формулу по столбцу. Получится так.      Внимание!Если числа разделены не слешем, а другим знаком, то в формуле указываем это знак.       Примеры других формул выбора данных с разными условиями смотрите в статье "Как сделать выбор в ячейке Excel" тут.       Как выделить в ячейке из большого текста число, чтобы оно считалось, смотрите в статье «Не работают формулы в Excel».       В Excel есть несколько разных способов сгрупприровать данные в таблице по нашим условиям, например, по датам, по определенному периоду, шагу цен, сумм, т. д. Как это сделать, смотрите в статье "Как группировать в Excel".

www.excel-office.ru

Как в Excel разделить текст в одной ячейке на две строки, если по ширине он помещается весь на одной строке?

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

Правой кнопкой по ячейке-формат ячейки-выравнивание-переносить по словам.

в агент выйди - там скажу проще будет

Вы можете сделать ширину и длину ячейки какую вам надо. Вырежте часть текста и вставьте в другую ячейку и обозначте границы какие вам надо. Немного не понятна проблемма.

Если тебе надо данные из одной колонки разбить на две автоматически, то выдели нужную колонку. Затем в меню: Данные/Текст по столбцам/ там выбери "фиксированной ширины. Откроется Мастер текстов. Там на появившейся шкале курсором выбери место, в котором надо разбить колонку и жми "далее". Но предварительно создай справа от твоей колонки одну пустую колонку, чтобы не произошла замена текста.

Можно это сделать гораздо проще.. . Напечатать первую строчку, одновременно нажать Alt+Enter, продолжать печатать на второй строке и т. д.

В месте переноса нажмите Alt+Enter.

touch.otvet.mail.ru

Как разделить текст по столбцам?

В более ранних публикациях рассматривался вопрос объединения значений ячеек, а также объединения самих ячеек без потери значений объединяемых ячеек. Еще одной задачей из этого цикла является разделение значений по ячейкам разных столбцов.

На вкладке «Данные» ленты Excel есть группа кнопок под названием «Работа с данными», а в этой группе есть кнопка «Текст по столбцам». Нажатие этой кнопки вызывает диалоговое окно «Мастер текстов», при помощи которого можно быстро разнести текст из ячеек одного столбца по ячейкам других столбцов, например имена, отчества и фамилии, написанные в одной ячейке по ячейкам различных столбцов.

Для того чтобы разобрать текст по столбцам, необходимо выделить диапазон ячеек, причем диапазон этот может содержать много строк, но должен содержать только один столбец, то есть все ячейки выделенного диапазона должны быть в одном столбце. Если столбец содержит объединенные ячейки, то их предварительно необходимо разъединить.

После того как диапазон ячеек с разделяемыми значениями выделен, вызывается мастер текстов (вкладка «Данные»/группа кнопок «Работа с данными»/кнопка «Текст по столбцам»). При этом значения выделенного диапазона отображаются в поле предварительного просмотра мастера текстов. Остается выбрать одну из двух опций, в нашем случае это опция «с разделителями» и нажать кнопку «Далее».

Поскольку слова в нашем примере отделены друг от друга пробелами, то в качестве символа-разделителя указывается пробел, здесь же можно увидеть образец результата разбора данных. Также можно использовать опцию "фиксированной величины", в тех случаях, когда поля имеют заданную ширину.

Нажатие на кнопку далее приведет к отображению следующего экрана мастера текстов.

На этом этапе для каждого столбца можно установить формат данных, а также указать ячейку/диапазон ячеек для вставки разделенных значений. После этого нажимается кнопка «Готово».

Другие материалы по теме:

macros-vba.ru