Текст по столбцам excel: Разделение текста по столбцам с помощью функций

Как в Excel разбить текст по столбцам | Информационные технологии

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

Необходимо разделить ФИО по отдельным столбцам

Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:

  1. Открываем документ с допущенной ранее ошибкой.
  2. Выделяем текст, зажав ЛКМ и растянув выделение до крайней нижней ячейки.

1

  1. В верхней ленте находим «Данные» – переходим.

2

  1. После открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и переходим в следующее диалоговое окно.

3

  1. По умолчанию формат исходных данных будет установлен на «с разделителями». Оставляем и кликаем по кнопке «Далее».
  2. В следующем окне нужно определить, что является разделителем в нашем тексте. У нас это «пробел», а значит устанавливаем галочку напротив этого значения и соглашаемся с проведенными действиями кликнув на кнопку «Далее».

4

  1. Затем нужно определить формат данных столбца. По умолчанию установлено «Общий». Для нашей информации этот формат наиболее уместен.
  2. В таблице выбираем ячейку, куда будет помещаться отформатированный текст. Отступим от исходного текста один столбец и пропишем соответствующий адресат в адресации ячейки. По окончанию нажимаем «Готово».

56

Разделение текста с помощью формулы

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

Этап №1. Переносим фамилии

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

  1. Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.

7

  1. Выберите ячейку, где будет записываться информация о фамилии сотрудника. Активируйте ее нажатием ЛКМ.

8

  1. Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
  2. Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».

9

  1. Далее находим продолжение формулы ЛЕВСИМВ и кликаем по этой строке. Соглашаемся с выполненными действиями нажатием кнопки «ОК».
  2. Появляется новое окно, где нужно указать адресацию ячейки, нуждающейся в корректировке. Для этого нажмите на графу «Текст» и активируйте необходимую ячейку. Адресация вносится автоматически.
  3. Чтобы указать необходимое количество знаков, можно посчитать их вручную и вписать данные в соответствующую графу либо воспользоваться еще одной формулой: ПОИСК().
  4. После этого формула отобразится в тексте ячейки. Кликните по ней, чтобы открыть следующее окно.

10

  1. Находим поле «Искомый текст» и кликаем по разделителю, указанному в тексте. В нашем случае это пробел.
  2. В поле «Текст для поиска» нужно активировать редактируемую ячейку в результате чего произойдет автоматический перенос адресации.
  3. Активируйте первую функцию для возврата к ее редактированию. Это действие автоматически укажет количество символов до пробела.

11

  1. Соглашаемся и кликаем по кнопке «ОК».

В результате можно видеть, что ячейка откорректирована и фамилия внесена корректно. Чтобы изменения вступили в силу на всех строках, потяните маркер выделения вниз.

Этап №2. Переносим имена

Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.

  1. В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.

12

  1. Выбираем ячейку и указываем позицию, где прописан основной текст.
  2. Переходим к графе «Начальная позиция» и вписываем формулу ПОИСК().
  3. Переходим к ней, используя предыдущую инструкцию.

13

  1. В строке «Искомый текст» указываем пробел.
  2. Кликнув по «Текст для поиска», активируем ячейку.

14

  1. Возвращаемся к формуле =ПСТР в верхней части экрана.
  2. В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.

15

  1. Переходим к определению количества знаков – вписываем формулу ПОИСК().
  2. Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
  3. Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
  4. Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
  5. Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.

16

Этап №3. Ставим Отчество

  1. Активировав ячейку и перейдя в аргументы функции, выбираем формулу ПРАВСИМВ. Жмем «ОК».

17

  1. В поле «Текст» вписываем адресацию редактируемой ячейки.
  2. Там, где необходимо указать число знаков, пишем ДЛСТР(A2).

18

  1. Для точного определения количества знаков в конце необходимо написать: -ПОИСК().
  2. Перейдите к редактированию формулы. В «Искомый текст» укажите пробел. В «Текст для поиска» – адресацию ячейки. В «Нач.позиция» вставьте формулу ПОИСК(). Редактируйте формулу, установив те же самые значения.
  3. Перейдите к предыдущему ПОИСК и строке «Нач.позиция» допишите +1.

19

  1. Перейдите к формуле ПРАВСИМВ и убедитесь, что все действия произведены правильно.

20

Заключение

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

Сообщение Как в Excel разбить текст по столбцам появились сначала на Информационные технологии.

Поделиться в социальных сетях

Вам может понравиться

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

Как в excel 2007 разделить текст по столбцам на две, три и более ячеек. Обычно для примера используют разбивку ячеек с ФИО, чтоб данные были раздельно.

Содержание:

  • Как разделить текст по столбцам в экселе
  • Этап первый. Разделить время в ячейках
  • Этап второй. Как в экселе разделить ячейку на время начала и конца
  • Этап третий
  • В этом видео подробно показано, как разделить текст по столбцам:

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

Мне по работе пришлось разделять время в ячейках таблицы excel , пример: 0:00-15:00. А между значениями разделитель точка с запятой 0:00-15:00;16:00-17:00;22:00-24:00. Эти данные в таблице по дням, в ячейках разное количество значений между разделителем. Необходимо было разделить время по каждому дню, в столбик под датой проставить время, разделить время на начало и конец, посчитать разницу и все сложить вместе…

Читайте также: СЦЕПИТЬ в эксель

Этап первый. Разделить время в ячейках

Выделяем диапазон ячеек, на ленте нажимаем Данные-Текст по столбцам, появится Мастер текстов.

Ничего не меняем, если у вас переключатель стоит в положении: с разделителем, жмем далее.

Снимаем галку «знак табуляция», ставим галку «другой»

и в окошко вносим точку с запятой [ ; ]. Жмем либо далее, хотя там особо смотреть не чего, верней нажать готово. Будет задан вопрос: заменить содержимое ячеек? Ответ да!

Мы смогли разбить ячейку на столько значений, сколько их было между [ ; ]. Добавим пустые строки под ячейкой с датой, в количестве, ячеек правей столбца С.

Две ячейки-две строки. Выделяем ячейки, копируем,

и под первой ячейкой жмем правой кнопкой «Специальная вставка»- «Транспонировать».

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

Получилось разделить текст в ячейке, оформить по дням, в столбик.

 

Читайте также: Функция СИМВОЛ в Excel или как верстать HTML в Excel

Этап второй.

Как в экселе разделить ячейку на время начала и конца

Получим разницу во времени, снова прибегнув к мастеру текста. Продолжим разделять текст в ячейках на время начала и конца. Здесь разделителем  является знак  «-» минус.

Вынесем столбец за нашу таблицу, скопировав и вставив ячейки рядом. Работаем с мастером: Данные-Текст по столбцам , шаг первый ничего не меняем, на втором шаге в окошке другой [ — ] ставим минус, нажимаем готово.

Читайте также: Объединить строку и дату в Excel

Этап третий

Ставим формулу: время конца минус время начала,

формат ячеек Все форматы [ч]:мм,

протянем формулу вниз до конца. Мы получили время в часах. В соседней ячейке напишем: =К4*60 Enter. Получим время в минутах.

Правда с секундами… Не нашел способа лучше, чем скопировать столбец, вставить его в блокнот, нажать CTRL+H (Найти и заменить), в найти написал :00

а заменить оставил пустым. …

Нажал заменить все…Текст без :00 скопировал из блокнота и вставил в таблицу.

С количеством часов поступил проще, скопировал в блокнот и вставил в таблицу. Одна проблема: 24 часа стоят как 24:00:00.

Не проблема, раз мы знаем, как в экселе разделить ячейку, то тут можно и Copy&Paste применить, но с хитростью — в ячейке перед 24:00 поставить апостроф ‘ -одиночную кавычку ’24:00

и вставить всюду. Складывать автосуммой можно только минуты, сумму часов получим, разделив сумму минут на 60, а количество дней разделим полученное число часов на 24….

В моем примере нечто из 31 дня проработало 28 дней,из 744 часов -673 часа и из 44640 минут -40411 минут… Не важно что это, главное что на этом примере вы узнали как как в экселе разделить текст по столбцам в ячейке, как можно не стандартно использовать блокнот и менять формат ячеек, добавляя к ним апостроф.

В этом видео подробно показано, как разделить текст по столбцам:

Рекомендуем смотреть видео в полноэкранном режиме, в настойках качества выбирайте 1080 HD, не забывайте подписываться на канал в YouTube, там Вы найдете много интересного видео, которое выходит достаточно часто. Приятного просмотра!

 С уважением, авторы сайта Компьютерапия

Понравилась статья? Поделитесь ею с друзьями и напишите отзыв в комментариях!

Как заставить столбец Excel сохраняться как текст?

спросил

Изменено
2 года, 1 месяц назад

Просмотрено
40 тысяч раз

У меня есть столбец шестнадцатеричных строк:

 506078
363624
505974
505644

Мне нужно, чтобы Excel сохранял их в виде строк, а не целых чисел или чисел с плавающей запятой.

  • Microsoft Excel

1

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

напр.

 '23446

Вы также можете отформатировать весь столбец как текст, который переопределит весь столбец. В Excel 2007 выделите ячейки, щелкните правой кнопкой мыши -> Форматировать ячейки. Нажмите на текст из вариантов слева.

1

Уже упомянутые ответы иногда непрактичны:

  • Вручную добавлять префикс к каждому значению неудобно и работает только для текущих данных.
  • Добавление еще одного столбца может быть нежелательным для конечного получателя «отчета».

Начиная с Excel 2013 (или даже раньше) есть гораздо лучший способ:

  • Выберите весь столбец.
  • Щелкните ДАННЫЕ | Текст в столбцы.
  • В диалоговом окне щелкните Далее | щелкните Далее | выберите Текст | нажмите Готово.

1

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

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

 ="'"&A1

в B1, где A1 — ссылка на вашу ячейку, и перетащите вниз по формуле в B1. На этом этапе вы увидите цитату, но вы можете вставить данные в столбец B как значения (CTRL+C и ALT+E+S и выбрать значения или вставить специальные значения из верхнего меню). Затем найдите + замените ‘(цитата) на ‘(цитата), и у вас будет столбец со значениями, принудительно переведенными в текст.

Зарегистрируйтесь или войдите в систему

Зарегистрируйтесь с помощью Google

Зарегистрироваться через Facebook

Зарегистрируйтесь, используя адрес электронной почты и пароль

Опубликовать как гость

Электронная почта

Требуется, но никогда не отображается

Опубликовать как гость

Электронная почта

Требуется, но не отображается

Нажимая «Опубликовать свой ответ», вы соглашаетесь с нашими условиями обслуживания и подтверждаете, что прочитали и поняли нашу политику конфиденциальности и кодекс поведения.

9Форматирование 0000 — Excel отображает ### для длинного текста — что не так?

спросил

Изменено
1 год, 5 месяцев назад

Просмотрено
292к раз

У нас есть лист excel с довольно длинным текстом в одной ячейке. Вместо разрывов строк (как указано в формате ячейки) мы получаем длинную строку из #### с. Мы можем идти вперед и назад, удаляя и добавляя символы в конце текста.

Мы поигрались с форматированием ячеек и т.д., но безуспешно. Что мы можем сделать?

  • Microsoft-Excel
  • форматирование

3

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

Excel отображает ###, когда содержимое ячейки содержит только текст и превышает 256 символов , а формат ячейки установлен на « Текст ». Обычно установка формата ячейки « Общий » устраняет эту проблему.

Однако! Если вы используете эту ячейку в качестве ввода данных, например, для поля в объединенном документе Word, будут захвачены только первые 256 символов!!!

Я пока не нашел решения этой проблемы, но хотел бы знать решение следующей проблемы.

5

Есть две причины:

A) Текст слишком длинный для текущей ширины ячейки.

Сделайте ячейку шире (двойной щелчок по границе заголовка), чтобы увидеть весь текст. Вот скриншот части электронной таблицы:

Столбцы P и Q содержат одни и те же данные — денежные значения одного порядка величины. P достаточно широк, чтобы отобразить текст Q, которого нет. Как видите, отображается «#####». Если я дважды щелкну заголовок для Q, он снова станет достаточно широким:

B) Другой случай, когда столбец представляет собой дату, а значение выходит за пределы допустимого диапазона. Если вы введете «55555555555555555555555» (скажем) в столбец даты, вы получите серию «#####», а во всплывающей подсказке указано:

Отрицательные или слишком большие даты и время отображаются как ######.

Вполне может быть и другой случай, когда Excel печатает «#####», но это наиболее распространенный случай.

5

Я считаю, что вам просто нужно сделать ячейку немного шире — это (####s) то, что делает Excel, когда он не может отобразить всю вашу запись.

Недавно у меня была аналогичная проблема, когда был включен перенос текста, но как только текст в ячейке достиг определенной длины, ##### отображался независимо. Я обнаружил, что изменение формата ячейки на «Общий» устранило проблему.

1

Какой длины текст ячейки? Какой формат ячейки?

Для редактирования ячеек с длинным текстом разверните строку формул ( Ctrl + Shift + U ).

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

 =ЕСЛИ(ДЛСТР(A1)<=10, A1, СЦЕПИТЬ(ЛЕВО(A1 ,10-3),"..."))

где:

  • A1 — это ячейка с вашим длинным текстом.
  • 10 — это максимальное количество отображаемых символов.

Проблема с отображением должна быть решена путем изменения ячейки на "общую", а не на "текст" - спасибо Microsoft за это интуитивно понятное решение!

Другая проблема, связанная с копированием >256 символов из WORD, решается путем вырезания фактического текста из WORD, а затем в режиме редактирования в соответствующей ячейке вставки текста в строку формул над листом.

Это происходит, если текст превышает 256 символов, поэтому отображается ######.

Некоторые из наших друзей предлагают перейти с текста на общий, но может появиться ошибка Excel:

Итак, решение простое, просто начните свой текст с простого добавления символа ' , чтобы пренебречь настройками столбца, но вам все равно придется использовать общий формат.

Так много странных попыток получить ответы, которые даже не касаются исходного вопроса, опубликованного 4 ноября 2009 года!

  • Дата публикации подразумевает либо Excel 2003, либо 2007.
  • , хотя это и не указано, пользователь может иметь текст в ячейке в виде формулы: ="256+ символов текста здесь". Вот где ограничение в 256 символов - в формулах! Это не имеет ничего общего с высотой строки или шириной столбца. Может быть, люди слишком молоды, чтобы знать о 8-битных архитектурах?
  • Excel ДЕЙСТВИТЕЛЬНО дает решение в окне ошибки, но оно довольно тупое.

Амирулл предоставил правильное исправление 27 ноября 2012 г.:

  1. убедитесь, что ячейка отформатирована как Общий
  2. поместите одинарную кавычку в качестве первого символа текста. Вы можете удалить любой знак равенства и/или двойные кавычки. Одинарная кавычка должна быть своего рода escape-символом для более старых версий Excel.

Надеюсь, что это поможет, так как решение НЕ задокументировано MS.

Excel — это мусор. Это всегда будет мусор. Ни один человек в любом случае реальности никогда не захочет видеть #### вместо содержимого, которое находится в ячейке. Если ячейка не «отображается достаточно широко», она должна показывать начало любой строки, числа, даты, ВСЕГО, что ввел пользователь, чтобы это было полезно, а не просто мусор.
Но это не так. Никогда не будет.
Excel — это липкая пена, которая остается в унитазе после того, как вы смыли рвоту.
Всегда будет хуже.
У него нет настоящей цели.
Все, что вы когда-либо пытались сделать с помощью Excel, заставит вас искать на форумах «почему, черт возьми, Excel делает это и как я могу заставить его делать что-то продуктивное или, по крайней мере, не делать что-то контрпродуктивное?»
К сожалению, ответ всегда будет: «Вы не можете».
Я предполагаю, что люди с деньгами где-то создали и контролируют это приложение, и достаточное количество людей, чтобы обеспечить его повсеместное распространение, и используют его, чтобы гарантировать, что никогда не будет конкуренции в отделе быть богатым.
На всякий случай я не упомянул об этом, EXCEL ЭТО МУСОР.
Вам лучше использовать физическую книгу, сделанную из пчелиных укусов, лезвий бритвы и лимонного сока, и писать в ней своим лицом.
Это должно быть ответом на все вопросы, связанные с Excel, поскольку его целью, по-видимому, является только пытка человечества. Удачи. Постарайтесь не совершить самоубийство, раз за разом ударяясь головой об экран компьютера.
Хотя они, вероятно, читают этот пост и смеются над своим достижением, не дайте террористам победить

2

Перейдите к:

"Инструменты">"Опции">"Переход"

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

1

Проверьте форматирование:

  1. Щелкните правой кнопкой мыши по ячейке
  2. выберите "Форматировать ячейку"
  3. Нажмите на вкладку «Число» в верхней части поля с инструкциями
  4. Выберите «Пользовательский» в поле слева
  5. Затем выберите «Общие» в поле справа.

На этом этапе должен быть виден весь текст..... надеюсь, это поможет.

1

Просто щелкните правой кнопкой мыши по ячейке и выберите « формат ячеек » и измените выбранную « категорию ». Это поможет решить проблему.

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

Формат "Общий" и "Перенос текста" решают проблему.

С точки зрения копирования и вставки, если вы используете функциональную клавишу F2 для входа в режим редактирования и выделяете часть текста (может быть более 256 символов) и копируете, вы можете вставить весь выбор в другие приложения, такие как Excel и Word и т. д.

Мне нужна отметка времени в две строки (дата плюс время), чтобы ширина столбца соответствовала другим данным в моем столбце:

02.03.2020
21:41

Единственный способ отобразить это — использовать текстовую функцию с возвратом каретки ascii.