Почему не работает текст по столбцам в excel
Многие пользователи сталкиваются с проблемой, почему при попытках суммировать число, у них этого не получается сделать. Что же, давайте разберемся в причинах этой проблемы более детально. Чаще всего это происходит по причине того, что число было сохранено в текстовом формате. Сегодня мы найдем причины этого явления, а также научимся решать ее разными методами.
Возможные причины, почему не считается сумма
Очень часто сумма не хочет считаться после того, как в Excel были скопированы данные из других программ. И в ходе использования этой информации обнаруживается, что числа не получается суммировать, а между датами не получается понять, сколько прошло дней.
Число сохранено, как текст
Как можно понять, что число было сохранено в текстовом формате? Чтобы сделать это, нужно посмотреть, к какому краю число было прижато. Кроме этого, при импортировании данных может показываться зеленый треугольник, который сигнализирует об ошибке перевода данных в правильный формат. Если навести на него мышью, то он и покажет, что число было записано в текстовом формате.
Способы решения проблемы
При этом если попытаться изменить формат ячейки с помощью стандартных средств Excel, то ничего не получится. Правда, если поставить курсор ввода текста в поле ввода формулы, после чего нажать кнопку «Enter», то проблема решается. Но очевидно. что это очень неудобный метод, когда речь идет об огромном количестве ячеек.
Правда, есть много других способов, как выкрутиться из этой ситуации.
Маркер ошибки и тег
Прежде всего, можно воспользоваться непосредственно маркером, сигнализирующем об ошибке. Если есть на ячейке тег зеленого цвета, то нажав по нему, появляется возможность сразу превратить ее в текстовый формат. Для этого в появившемся меню нажимаем на «Преобразовать в число».
Операция Найти/Заменить
Еще один метод решения ситуации, при которой числа записываются в текстовом формате – использовать операцию «Найти/заменить». Допустим, в каких-то ячейках содержится число, имеющее десятичную запятую, сохраненные в текстовом формате. Для этого нужно нажать соответствующую кнопку на ленте или в верхнем меню (в зависимости от используемой версии Excel). Появится окно, в котором нужно заменить запятую на саму себя. Да, в буквальном смысле, нужно в поле «Найти» ввести запятую и в поле «Заменить» также ввести запятую. После этого формат должен быть преобразован автоматически. В принципе, операция аналогична клику на строку формул и дальнейшему нажатию по кнопке ввода. Та же операция может быть и с датами, только нужно точку заменять на точку.
Если же данные были импортированы из других программ, то причина может быть еще и в разности форматов десятичных значений. Если в ячейке в качестве разделителя служит точка, а не запятая, то Эксель не будет эти данные распознавать, как числовые. В таком случае нужно заменить точку на запятую в соответствующих ячейках.
Специальная вставка
Использование «Специальной вставки» – это достаточно универсальный метод, поскольку позволяет превращать в формат чисел любые цифры, относящиеся к любому виду как дробному, так и целым числам. Также его можно использовать для того, чтобы переводить даты в соответствующий формат. Чтобы использовать эту функцию, необходимо найти любую пустую ячейку, выделить ее и скопировать ее. После этого нажимаем правой кнопкой мыши по любой ячейке, формат которой неправильный, после чего нажимаем кнопки «Специальная вставка» – «Сложить» – «ОК». Это аналогичная добавлению нуля операция. Значение ячейки не меняется абсолютно, но ее формат превращается в числовой. Также можно использовать умножение диапазона значений на единицу.
Инструмент Текст по столбцам
Этот инструмент наиболее удобно применять, если используется всего одна колонка. Если их больше, ничего страшного, но придется использовать его по отдельности для каждой колонки. Чтобы это сделать, нужно выделить соответствующий столбец, выставить числовой формат и выполнить команду «Текст по столбцам», которая находится во вкладке «Данные».
Формулы
Очень популярный способ решения проблем с отображением ячеек – использование функции ПОДСТАВИТЬ() , ЗНАЧЕН() и некоторыми другими формулами. Этот способ можно использовать, если есть возможность использовать дополнительные столбцы, в которые может вводиться формула. Можно использовать и другие математические операции, такие как двойной минус (—), добавление к числу нуля, умножение на единицу и любой другой подобной операции. После этого получившиеся ячейки копируются и вставляются в те места, в которых до этого были значения в текстовом формате.
Макросы
Отдельное внимание стоит уделить использованию макросов для исправления ошибок. В принципе, можно использовать любой предыдущий способ через макрос, поэтому мы его отдельно приведем. Достаточно просто написать соответствующий скрипт и выполнить его. А вот некоторые примеры, которые можно использовать.
Sub conv()
Dim c As Range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = c.Value * 1
End If
Next
End Sub
Этот код умножает текстовое значение на единицу.
Sub conv1()
Selection.TextToColumns
End Sub
А это код, демонстрирующий использование инструмента «Текст по столбцам».
В записи числа имеются посторонние символы
Способы решения проблемы
Также частой причиной, почему начинают отображаться числа в виде текста, является появление в соответствующих ячейках невидимых символов. Наиболее часто ими служат пробелы, которые могут находиться в каком-угодно месте, начиная началом и концом числа и заканчивая использованием их для отделения разрядов друг от друга.
Еще один тип пробелов, который может мешать сохранению ячейки в правильном формате – это так называемый неразрывный пробел (имеющий код 160). Описанного выше способа для решения этой проблемы недостаточно. Чтобы решить возникшую проблему, необходимо скопировать этот символ непосредственно с ячейки, а потом вставить в поле «Найти» или же набрать в этом поле комбинацию Alt + 0160 (но на ноутбуках такой номер не получится, потому что требуется цифровая клавиатура для выполнения этой задачи).
Для обычного человека вообще не всегда понятно, что такое неразрывный пробел и где он может использоваться. Чтобы стало более понятно, давайте посмотрим на этот текст.
6
На первый взгляд, ничего особенного. Но если человек хоть немного работал с текстами, он сразу поймет, что разбивка этого фрагмента на строки далека от удачной. Давайте посмотрим на него более внимательно. Например, инициалы и фамилия были разделены, что не очень хорошо. То же самое касается номера года и сокращенного обозначения года.
Также неудачно оказалось разделение фамилии «Палажченко» и обозначения его должности. В результате, выглядеть этот фрагмент текста стал, как прямая речь.
Простыми словами, в этом фрагменте содержатся куски, где следует использовать пробел. Но в результате этого пробела теряется аккуратность оформления. И чтобы этого добиться, нужно использовать символ неразрывного пробела. С его помощью мы также разделяем слова, но при этом они остаются на одной строке. Слова, разделенные им, воспринимаются программой, как одно цельное слово, но при этом выглядят, как несколько. Получается эдакий компромисс между тем, как отображается текст и как он воспринимается программой. То есть, если окажется, что нужно их переносить на новую строку, то переноситься будут они все. Их нужно применять в таких ситуациях:
- Перед тире, которое находится посередине строки. Только в трех случаях допускается использование тире в начале строки: если используется прямая речь, если тире маркирует элемент списка и при условии, что тире заменяет прочерк. Чтобы тире не переносилось на новую строку, перед ним нужно поставить неразрывный пробел.
- Между числом и единицей измерения. Довольно часто эта проблема случается. В результате человек может перенести в таблицу Excel число с неразрывным пробелом, а единица измерения окажется в другой ячейке. В целом, использовать в таком случае неразрывный пробел нужно, чтобы не разделять их. Но вот при переносе в Эксель могут возникнуть проблемы.
- Перед знаком процента. В таком случае ячейка может не переводиться в процентный формат. Конечно, не везде можно встретить знак процента, отделенный от числа пробелом, но некоторые считают, что так правильно. Поэтому при переносе в Эксель данные могут отображаться в текстовом формате. То же касается и обычного пробела.
- Знак номера и параграфа. Такая ситуация также часто возникает, когда приходится переносить разделы учебников или части документов в электронные таблицы.
- Многозначные числа. Самая частая причина, почему неразрывные пробелы переносятся в таблицу Excel. По правилам в многозначных числах обязательно ставить пробелы, чтобы упростить их чтение пользователями. Но если число слишком большое, то оно может автоматически перенестись частями на следующую строку. Чтобы решить эту проблему, используется неразрывный пробел. И если такое число скопировать в ячейку, она будет автоматически отображаться в текстовом формате.
Как правило, неразрывные пробелы оказываются в Excel после того, как данные были перенесены из документа Word.
Возможности так легко обнаружить неразрывные пробелы средствами Excel нет. Но если скопировать содержимое ячейки в Word и включить опцию отображения непечатаемых символов, то можно увидеть своеобразные кружочки, которые похожи на знак градуса, только чуть большего размера. Это как раз и есть эти пробелы.
Этот символ есть в любом шрифте. Как правило, все программы, предназначенные для работы с текстом, правильно его обрабатывают. При этом в некоторых из них неразрывные пробелы одинакового размера. Из-за этого наблюдаются проблемы с отображением страницы по ширине, поскольку обычные и неразрывные пробелы имеют одинаковые размеры.
Может понадобиться вводить неразрывный пробел и в Excel. Чтобы это сделать, нужно нажать комбинацию клавиш ALT и 0160. Также в стандартной комплектации Windows предусмотрена комбинация горячих клавиш CTRL + SHIFT + ПРОБЕЛ, с помощью которой можно вводить неразрывный пробел почти в любой программе.
Существует два основных способа решения этой проблемы – воспользоваться функцией «Найти/Заменить» или же использовать формулу.
Операция Найти/Заменить
Чтобы убрать пробелы, можно воспользоваться функцией «Найти/Заменить». В первое поле нужно ввести знак пробела, в то время как нижнее поле оставляем пустым.
Важно убедиться, что там пробелов нет .
Формула
Использование формулы также возможно для удаления пробелов. В зависимости от того, пробел обычный или неразрывный, нужно использовать разные формулы. Также есть одна универсальная, которая позволяет убрать одновременно все пробелы, содержащиеся в ячейке.
Что это за формулы?
Во всех случаях используется функция ПОДСТАВИТЬ . Если нам нужно убрать обычный пробел, то используется следующая формула.
С помощью двойного знака минуса мы выполняем конвертацию текстового значения в числовое. Это эквивалент тому, что мы умножили получившееся число на -1, а потом получившееся отрицательное значение снова умножили на -1. В результате, ничего не изменилось, но благодаря выполненной математической операции ячейка автоматически сконвертирована в числовой формат.
В случае с неразрывными пробелами формула будет такой.
=—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)
Как видим, здесь в качестве второго аргумента мы используем код символа. А с помощью этой формулы можно убрать как обычные, так и неразрывные пробелы.
=—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)
Иногда проблема оказывается намного сложнее, чем может показаться на первый взгляд. Поэтому в ряде случаев приходится комбинировать все описанные выше методы.
Выводы
Таким образом, проблема, почему не получается суммировать несколько чисел, оказывается не такой сложной, как может показаться на первый взгляд. Решить ее очень просто, достаточно просто знать некоторые функции Excel.
Подпишитесь к нам в дзен-канал, для получения свежих новостей it мира:
Всем привет!
Возникла проблема с редактированием файла. (файл прикреплен)
Делаю текст по столбцам. 1) Выделяю данные столбца ФИО (4 ячейки)
2) формат данных - с разделителем;
3) символ разделитель - знак табуляции и пробел
4) поместить в $B$2 (т.е. в ту же ячейку, где начинаются данные)
5) выскакивает предупреждение, что имеются данные. Соглашаюсь на замену
Далее либо Excel просто зависает, либо выскакивает ошибка "Прекращена работа программы", либо все хорошо, но при попытке что-нибудь сделать после (просто тыкнуть в ячейку, например; или попытаться сохранить файл) выскакивает эта ошибка.
Файл генерируется программой, поэтому, возможно, схема файла где-то нарушена.
А может в чем то другом проблема. Помогите найти причину ее появления.
Проверял в Excel 2013.
P.S. Подробности проблемы:
Сигнатура проблемы:
Имя события проблемы: APPCRASH
Имя приложения: EXCEL.EXE
Версия приложения: 15.0.4420.1017
Отметка времени приложения: 50673286
Имя модуля с ошибкой: ntdll.dll
Версия модуля с ошибкой: 6.1.7601.23796
Отметка времени модуля с ошибкой: 59028db3
Код исключения: c0000005
Смещение исключения: 0002e43e
Версия ОС: 6.1.7601.2.1.0.256.48
Код языка: 1049
Дополнительные сведения 1: 0a9e
Дополнительные сведения 2: 0a9e372d3b4ad19135b953a78882e789
Дополнительные сведения 3: 0a9e
Дополнительные сведения 4: 0a9e372d3b4ad19135b953a78882e789
Всем привет!
Возникла проблема с редактированием файла. (файл прикреплен)
Делаю текст по столбцам. 1) Выделяю данные столбца ФИО (4 ячейки)
2) формат данных - с разделителем;
3) символ разделитель - знак табуляции и пробел
4) поместить в $B$2 (т.е. в ту же ячейку, где начинаются данные)
5) выскакивает предупреждение, что имеются данные. Соглашаюсь на замену
Далее либо Excel просто зависает, либо выскакивает ошибка "Прекращена работа программы", либо все хорошо, но при попытке что-нибудь сделать после (просто тыкнуть в ячейку, например; или попытаться сохранить файл) выскакивает эта ошибка.
Файл генерируется программой, поэтому, возможно, схема файла где-то нарушена.
А может в чем то другом проблема. Помогите найти причину ее появления.
Проверял в Excel 2013.
P.S. Подробности проблемы:
Сигнатура проблемы:
Имя события проблемы: APPCRASH
Имя приложения: EXCEL.EXE
Версия приложения: 15.0.4420.1017
Отметка времени приложения: 50673286
Имя модуля с ошибкой: ntdll.dll
Версия модуля с ошибкой: 6.1.7601.23796
Отметка времени модуля с ошибкой: 59028db3
Код исключения: c0000005
Смещение исключения: 0002e43e
Версия ОС: 6.1.7601.2.1.0.256.48
Код языка: 1049
Дополнительные сведения 1: 0a9e
Дополнительные сведения 2: 0a9e372d3b4ad19135b953a78882e789
Дополнительные сведения 3: 0a9e
Дополнительные сведения 4: 0a9e372d3b4ad19135b953a78882e789 C_sanches
Проверял в Excel 2013.
P.S. Подробности проблемы:
Сигнатура проблемы:
Имя события проблемы: APPCRASH
Имя приложения: EXCEL.EXE
Версия приложения: 15.0.4420.1017
Отметка времени приложения: 50673286
Имя модуля с ошибкой: ntdll.dll
Версия модуля с ошибкой: 6.1.7601.23796
Отметка времени модуля с ошибкой: 59028db3
Код исключения: c0000005
Смещение исключения: 0002e43e
Версия ОС: 6.1.7601.2.1.0.256.48
Код языка: 1049
Дополнительные сведения 1: 0a9e
Дополнительные сведения 2: 0a9e372d3b4ad19135b953a78882e789
Дополнительные сведения 3: 0a9e
Дополнительные сведения 4: 0a9e372d3b4ad19135b953a78882e789 Автор - C_sanches
Дата добавления - 03.07.2018 в 13:50
Разделение текста на столбцы с помощью мастера распределения текста по столбцам
Смотрите такжеПо вопросу: ищитеmodellisimo: В каждой ячейке быи изучить при
6-8 лет,9-12 лет текста число, чтобы ячейке. По факту,
столбцу. Получится так..СправкаНа вкладке он может содержать
и вам полезна. ПросимВ поле.С помощью мастера распределения символ под номером: есть одна проблема перенос строки (посмотрите
регистрации. Ознакомьтесь и4-5 лет,6-8 лет оно считалось, смотрите отображается столько символов,В столбце С устанавливаемУ нас такаяв мастере разделенияДанные не более одногоФамилии вас уделить пару
Поместить вВыберите текста по столбцам
10 у меня MAC в строке формул), приложите к первому4-5 лет,6-8 лет
в статье «Не сколько есть в формат «Дата». Какими таблица с данными. текста столбцов.
в группе столбца. Это важныестолбцом.
Распределение содержимого ячейки на соседние столбцы
нем другие горячиеmodellisimo в котором можно "," и применения Excel».Копируем формулу по смотрите в статье код, составленный черезкак разделить текст внажмите кнопку пустыми столбцами справа вам, с помощью
отображаться разделенные данные. Например,
разделить на несколько. чем речь? что клавиши: похоже проблема в было бы пощупать инструмента - пропадают
В Excel есть столбцу. Получится так. «Число Excel. Формат»
Для удобства такжеГотовои с текстом, который
номером 10? именно делает Ctrl+J ячейке перед текстомglingФайл во вложении сгрупприровать данные вЕсли числа разделены
В ячейке С27 нужно их разделить.. В Excel естьСледуйте инструкциям в перезапись по данные,
столбцам см. в приводим ссылку на.пробел вы хотите разделить.Юрий М ?modellisimo: А в скрине vikttur таблице по нашим
Разделить текст в ячейке Excel по столбцам.
Инструмент "Текст по столбцам" не работает
ДалееВ откроется таблица символов.
вопрос
раз не получается
скрин вряд ли
читать надо!
смотрите в статье разными условиями смотрите ячейке с датой Это для того,
текст Excel на
заполнению все действия составные части распределенных
Выполните следующие действия. лист содержит
переведена автоматически, поэтому.мастере распределения текста по
Каждый имеет свойvikttur
разбить текст по поможет. Нужен файл
О разрешенном объеме "Как группировать в в статье "Как не был написан чтобы знак слеша несколько ячеек» тут. мастера читайте статью данных.Примечание:
Полное имя ее текст можетВыберите значение в разделе столбцам номер. Так вот: Есть еще одна столбцам, то пробуйте Excel. вложения - раз Excel".
сделать выбор в слеш (косая черточка). не писался с
Здесь рассмотрим, Разделение текста поВыделите ячейку, диапазон или Диапазон, содержащий столбец, которыйстолбца, можно разделить столбец
содержать неточности иФормат данных столбцаустановите переключатель символ 10 -
проблема: Вы не по порядку:modellisimo
о файле-примере -modellisimo ячейке Excel" тут.«255» - это кодом в столбцекак разделить в различным столбцам с
весь столбец, где вы хотите разделить на двух столбцов
грамматические ошибки. Дляили используйте то,с разделителями это символ переноса умеете цитировать. НеВыделите столбец А--нажмите
: ОК - файл два.: В ячейке вот
Как выделить в количество символов, которые В.Excel формулой текст числа, преобразовать мастера текстов, содержатся текстовые значения,
может включать любое — нас важно, чтобы которое Excel выберети нажмите кнопку строки. Ищите похожее
используйте кнопку для Ctrl+H--в Найти жмете вложил
Да и вообще, такие тексты ячейке из большого могут отобразиться вКопируем формулу по
коды на составляющие или нажмите кнопку которые требуется разделить. количество строк, но
имени эта статья была автоматически.Далее на МАС. ответа. Ctrl+J--Заменить все--ОКvikttur правила Вы должны
Распределение содержимого ячейки на соседние столбцы
Смотрите также не играет роли: Это функция, написанная (т.е. расположение в=ЕСЛИ(E2=B2;"";ЕСЛИ(ЕЧИСЛО(--ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);3;1));ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;3);ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2)))Serge количество символов в для любых условийПоследний аргумент – это которой можно определить, в Excel. НекоторыеНажимаем кнопку «Готово». имя и фамилияНапример, у нас инструмент расположен нав группеСведения об альтернативном способеПримечание: - лучше бы на VBA. См. других строках структурноVanya7819
: Ну так рисуйте разделенной строке. С при решении подобного количество символов, которое что данные из разные значения попадают Получилось так. (др. слова) были есть список с вкладке Данные вРабота с данными распределения текста поМы стараемся как
его тоже забрать. Alt+F11
похоже на показанное: Можно ли с пример согласно правилам помощью такой гибкой рода задач. Гибкость должна содержать разделенная
разных категорий, несмотря в одну иТак можно разделить текст
написаны через запятую,
фамилиями, именами в группе Работа снажмите кнопку столбцам см. в можно оперативнее обеспечивать Спасибо. vikttur в примере), можно помощью формул расделить - будет решение. формулы можно делать придает ей сложная строка. на то, что туже ячейку образуя из одной ячейки то мы бы одном столбце. Нам данными пункт Текст-по-столбцам).Текст по столбцам статье Разделение текста
вас актуальными справочнымиvikttur: Строка 8: ". 85 сочинить чего-нибудь на
ячейки по столбцамKhlopkov выборку разной длинны комбинация из функцийС первым аргументом ПСТР они находятся в целую строку как
на большее количество указали разделителем «запятая». нужно написать фамилии Вызовется мастер текстов,.
по столбцам с материалами на вашем: Формулы. ради интереса T XL - досуге. в данном примере?: Простите, что сразу разделенного текста из НАЙТИ. Пользователю формулы все понятно – одной и той
Текст-по-столбцам (мастер текстов) в MS EXCEL
одно значение. Возникает столбцов. Например, здесьВ этом окне, в в одном столбце, который пошагово предложитСледуйте инструкциям в помощью функций. языке. Эта страница домучил определение блок2 Marangoni *", блок6 data:image/gif;base64,R0lGODdhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=" data-src="https://img.my-excel.ru/master-importa-teksta-v-excel_1.jpg" alt="" height="409" width="518">
и блок6. T XL": Попробуйте. Можно ещеvikttur было в примере. строк. и указать их ячейку A3. Второй нашем случае нас строку на ячейки по столбцам – данных» сразу видно, другом столбце. Например, или любую другую
, чтобы указать способ функцию СЦЕП или ее текст можетТире с пробеламиСтрока 7: ".
Разделить текст Excel на несколько ячеек.
доделать ограничение второй: Можно. Но чемviktturKhlopkov в параметрах функций: аргумент мы вычисляем интересуют только числа, в Excel. Программа число, месяц, год.
какие столбцы у чтобы определить пол строку с разделителями. разбиения текста на функцию СЦЕПИТЬ. содержать неточности и и без; "*" TL 79Q - подстроки в 3 больше различий в: Моя первая работает: Здравствуйте, будут это квадратные с помощью функции
которые находятся вне располагает разными поисковымиДля большей наглядности нас получатся. Нажимаем
людей в списке.Это удобно, когда имеется разных столбцах.Выполните следующие действия. грамматические ошибки. Для - то оно Marangoni *", блок6="79Q". слова, т.е. расположении блоков и без изменений, вторуюПодскажите, есть такая
скобки либо другие НАЙТИ("]";A3)+2. Она возвращает квадратных скобок. Каким функциями: одни ищут в таблице Excel кнопку «Далее». Подробнее об этом список однотипных строк,Примечание:Примечание: нас важно, чтобы есть, то нет, Куда делось "TL"?METEO GRIP E+ в самих блоках, подправил, не вдаваясь функция как текст разделительные знаки. Например, очередной номер символа
способом можно быстро по ячейках другие можно выделить некоторыеЗдесь, в разделе читайте в статье например, адресов или Для получения справки по Диапазон, содержащий столбец, который эта статья была то с пробелом;Легко определить начало TL -> METEO тем сложнее формулы. в подробности: по столбцам с это могут быть первой закрывающейся квадратной выбрать из строк
ищут по содержимому строки, столбцы (например, «Поместить в:» указываем "В Excel по полных имен. Используя заполнению все действия вы хотите разделить вам полезна. Просим
блок6 то с блок2 и конец
GRIP E+Для блок3 и=ЕСЛИ(E2=B2;"";ПСТР(ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2);1;2)) разделителями (Данные текст пробелы если нужно скобки в строке. целые числа и
ячеек. Ведь выполнять итоговые) выделить другим диапазон столбцов, в имени определить пол". инструмент Текст-по-столбцам через мастера читайте статью может включать любое вас уделить пару пробелом после числа, блок6, основная сложность
Vanya7819 блок4 достаточноПРАВСИМВ(. ;1;2) - извлекает по столбцам), можно разделить строку на И к этому поместить их в поиск по текстовой
цветом, разделительной линией. которые поместятся новыеВыделяем ячейки, текст несколько кликов можно Разделение текста по количество строк, но секунд и сообщить, то без, не
Как разделить текст по ячейкам формула в Excel
для формулы -: Виктор, Спасибо за=ЛЕВСИМВ(A4;3) только два знака. ли формулой сделать слова и т.п. номеру мы добавляем отдельные ячейки? Эффективным строке, которая содержится Подробнее, смотрите в данные. в которых нужно добиться, что все различным столбцам с он может содержать помогла ли она имеет общего параметра определение места разрыва ответ! Блок2 и=ПСТР(A4;5;2)Khlopkov то же самое?В данном примере функция еще число 2,
Как разделить текст на две ячейки Excel
решением является гибкая в ячейке ¬– статье "Разделительная линияВнимание! разделить или весь фамилии будут в преобразовать мастера текстов, не более одного вам, с помощью и т.д. этих блоков. блок6 в основномВ блок5 перед: Работает!! Спасибо!
Пример прилагаю НАЙТИ во втором так как нам формула основана на это также распространенная строк в таблицеСтолбцы этого диапазона столбец. одном столбце, имена или нажмите кнопку столбца. Это важные кнопок внизу страницы.Практического применения почтиVanya7819 будет, как в R может бытьНу раз ужSerge аргументе определяет положение нужен номер символа
текстовых функциях. потребность пользователей Excel.
Excel" тут. должны быть пустыми,
Заходим на закладке «Данные» в другом, отчетства
Описание формулы для разделения текста по ячейкам:
из них достаточно Для удобства также никакого, потому что: TL сдесь не
- примере. Если у пробел, нужно добавить как говориться просить
- : Здравствуйте. относительно первой закрывающейся после пробела заВ ячейку B3 введите
- Их мы иМожно разделить ячейку иначе информация в в раздел «Работа
в третьем. Особеннов мастере разделения пустыми столбцами справа приводим ссылку на такие формулы нужно играет роли. Основа ВАС будет время функцию: о помощи до34,5 Кб скобки. А в квадратной скобкой. В следующую формулу: будем использовать для по диагонали и, них сотрется и с данными» и этот инструмент полезен текста столбцов. от выбранных столбец, оригинал (на английском писать под конкретную в 6 столбцах
- напишите ещё=ПОДСТАВИТЬ(ПСТР(A4;ПОИСК("R";A4)+1;3);" ";"") до конца))Khlopkov третьем аргументе эта последнем аргументе функцияТеперь скопируйте эту формулу разделения строк. в каждой части заменится на новую. выбираем функцию «Текст при импорте списковЧасто необходимо «разнести» текстовою который будет предотвратить языке) . структуру данных. Какие-нибудь (как в примере). формулы для них.Для блок1:А если нет: Спасибо ))) же функция вычисляет вычисляет какое количество вдоль целого столбца:Допустим на лист Excel написать текст. Читайте,
Если нет пустых по столбцам». В из других приложений. строку из одной перезапись по данные,Можно разделить содержимого ячейки мелкие изменения данныхVanya7819 Они могут быть=ПСТР(ПОДСТАВИТЬ(A4;"*";"");ПОИСК("-";A4)+2;15) № то вместоvikttur положение нужного нам символов будет содержатьВыборка чисел из строк были импортированные данные как это сделать, столбцов, можно предварительно появившемся окне «МастерАльтернативой этому инструменту может ячейки по нескольким. которые распространяться данные и распространение составные повлекут неработоспособность формул: Буду очень ВАМ любой сложности, толькоВ конце строки пустого значения, значение: Вариант:
Текст по столбцам формулой
текста в строке разделенная строка после
в отдельные ячейки. из другой программы. в статье "Как вставить столбцы. Как текстов», в строке служить применение формул Это может быть
в любой смежных
части нескольких смежных блок2 и блок6.
чтоб был желаемый перед знаком "*"
=1=ПСТР(B2&" №";1;ПОИСК(" №";B2&"
относительно второй открывающийся разделения, учитывая положение
Из-за несовместимости структуры разделить ячейку в это сделать, смотрите «Формат исходных данных» (см. статью Разнесение или полное имя:
столбцах. При необходимости ячеек. Например, если
Vanya7819
подробно, как работает результат. Ещё раз пробел может быть,Khlopkov
№")-1) квадратной скобки. Вычисление квадратной скобки.Функция ПСТР возвращает текстовое
данных при импорте Excel" здесь. в статье «Как указываем – «С текстовых строк по
«Иванов Иван Иванович»,
вставьте количество пустые лист содержит
: Спасибо. каждая строка в
Спасибо. а может и: Блин! Всего не=ЕСЛИ(E2=B2;"";ПРАВСИМВ(B2;ДЛСТР(B2)-ДЛСТР(E2)-2))
в третьем аргументеОбратите внимание! Что в значение содержащие определенное некоторые значение из
Удобнее работать в добавить строку, столбец разделителями». Нажимаем кнопку столбцам). либо адрес «г.Москва, столбцы, будет достаточноПолное имя
У меня ещё VBA (в ВашемVanya7819 не быть, поэтому,
предусматреть! А Если
Khlopkov более сложное и нашем примере все количество символов в разных категорий были
таблице, где в
в Excel» тут. «Далее».Здесь рассмотрим, Северный бульвар, д.133», для хранения каждогостолбца, можно разделить столбец
такой вопрос, может примере, - можно
: И Вам большое
если нужно удалить
после номера идет: Да работает, но оно подразумевает вычитание исходные и разделенные
строке. Аргументы функции:
внесены в одну
ячейках не видны
Можно указать форматЗдесь указываем в строкекак разделить текст в либо паспортные данные. составные части распределенных на двух столбцов глупый ))), а в екселе). У
Спасибо. Только я
последний пробел, формулу трех значное число, есть но.. не одной большей длинны строки имеют разнуюПервый аргумент – это ячейку. Необходимо из нули. Как их Ячеек столбцов. Например, «Символом – разделителем
ячейке Excel на Используйте для этого данных. — ВПР, в данной
меня пример только не совсем понял блок1 необходимо дорастить:
то он последний получается вытащить из текста от меньшей. длину и разное ссылка на ячейку этой ячейки отделить убрать, скрыть, заменить, если вы делите является» - «пробел». несколько ячеек. мастер текстов.Выделите ячейку, диапазон илиимени ситуации, ничем не
по 25 вариантах ВАШ ответ. Результат=СЖПРОБЕЛЫ(ПСТР(. )) знак обрезает! текста число если А чтобы учитывать количество символов. Именно с исходным текстом.
целые числовые значения. читайте в статье столбец на дни Поставили «пробел», потому
Например, предложение изПроще всего разнести по весь столбец, гдеи поможет?
- в остальном то что надо,Труднее с блок2vikttur
к примеру еще 2 пробела поэтому мы называлиВторой аргумент – это Пример таких неправильно "Как убрать нули
и месяцы, год, что в нашем первой ячейки разнести столбцам текстовую строку содержатся текстовые значения,
Фамилииvikttur я постараюсь розобратся только как это и блок6. Если: Пока еще не0,02 г №50 следует вычитать число такую формулу – позиция первого символа, импортированных данных отображен в Excel". то можно указать списке имя и по словам в используя инструмент Текст-по-столбцам которые требуется разделить.столбцом.
: Слабо представляю. Почему сам по ВАШИМ работает? условия задачи больше
массивная, но если табл 3. В результате гибкой, в начале с которого должна ниже на рисунке:Часто приходится оптимизировать структуру «Формат данных столбца» фамилия написаны через несколько других ячеек,
(в MS EXCELНа вкладкеСоветы: ВПР, а не, подсказкам. "TL" -Казанский не будут меняться еще всплывут условия.
25 мг №20упак чего получаем правильное
статьи. Она подходит начинаться разделенная строка.Сначала определим закономерность, по данных после импорта - «дата». пробел. Если бы
Разделение текста из одной ячейки по нескольким столбцам с сохранением исходной информации и приведением ее к нормальному состоянию – это проблема, с которой может столкнуться однажды каждый из пользователей Excel. Для разбивки текста по столбцам используются различные методы, которые определяются исходя из предложенной информации, необходимости получения конечного результата и степени профессионализма пользователя.
Необходимо разделить ФИО по отдельным столбцам
Для выполнения первого примера возьмем таблицу с прописанными в ней ФИО разных людей. Делается это с использованием инструмента «Текст по столбцам». После составления одного из документов была обнаружена ошибка: фамилии имена и отчества прописаны в одном столбце, что создает некоторые неудобства при дальнейшем заполнении документов. Для получения качественного результата, необходимо выполнить разделение ФИО по отдельным столбцам. Как это сделать – рассмотрим далее. Описание действий:
- Открываем документ с допущенной ранее ошибкой.
- Выделяем текст, зажав ЛКМ и растянув выделение до крайней нижней ячейки.
- В верхней ленте находим «Данные» — переходим.
- После открытия отыскиваем в группе «Работа с данными» «Текст по столбцам». Кликаем ЛКМ и переходим в следующее диалоговое окно.
- По умолчанию формат исходных данных будет установлен на «с разделителями». Оставляем и кликаем по кнопке «Далее».
- В следующем окне нужно определить, что является разделителем в нашем тексте. У нас это «пробел», а значит устанавливаем галочку напротив этого значения и соглашаемся с проведенными действиями кликнув на кнопку «Далее».
От эксперта! Для разделения текста могут быть использованы запятые, точки, двоеточия, точки с запятой, пробелы и другие знаки.
- Затем нужно определить формат данных столбца. По умолчанию установлено «Общий». Для нашей информации этот формат наиболее уместен.
- В таблице выбираем ячейку, куда будет помещаться отформатированный текст. Отступим от исходного текста один столбец и пропишем соответствующий адресат в адресации ячейки. По окончанию нажимаем «Готово».
Замечание эксперта! Размещенный отформатированный текст из-за разного количества символов в ФИО может не вмещаться в выбранные ячейки, поэтому полученная таблица нуждается в корректировке. Для этого используется расширение размеров ячейки.
6
Разделение текста с помощью формулы
Для самостоятельного разделения текста могут быть использованы сложные формулы. Они необходимы для точного расчета позиции слов в ячейке, обнаружения пробелов и деления каждого слова на отдельные столбцы. Для примера будем также использовать таблицу с ФИО. Чтобы произвести разделение, потребуется выполнить три этапа действий.
Этап №1. Переносим фамилии
Чтобы отделить первое слово, потребуется меньше всего времени, потому что для определения правильной позиции необходимо оттолкнуться только от одного пробела. Далее разберем пошаговую инструкцию, чтобы понять для чего нужны вычисления в конкретном случае.
- Таблица с вписанными ФИО уже создана. Для удобства выполнения разделения информации создайте в отдельной области 3 столбца и вверху напишите определение. Проведите корректировку ячеек по размерам.
- Выберите ячейку, где будет записываться информация о фамилии сотрудника. Активируйте ее нажатием ЛКМ.
- Нажмите на кнопку «Аргументы и функции», активация которой способствует открытию окна для редактирования формулы.
- Здесь в рубрике «Категория» нужно пролистать вниз и выбрать «Текстовые».
- Далее находим продолжение формулы ЛЕВСИМВ и кликаем по этой строке. Соглашаемся с выполненными действиями нажатием кнопки «ОК».
- Появляется новое окно, где нужно указать адресацию ячейки, нуждающейся в корректировке. Для этого нажмите на графу «Текст» и активируйте необходимую ячейку. Адресация вносится автоматически.
- Чтобы указать необходимое количество знаков, можно посчитать их вручную и вписать данные в соответствующую графу либо воспользоваться еще одной формулой: ПОИСК().
- После этого формула отобразится в тексте ячейки. Кликните по ней, чтобы открыть следующее окно.
- Находим поле «Искомый текст» и кликаем по разделителю, указанному в тексте. В нашем случае это пробел.
- В поле «Текст для поиска» нужно активировать редактируемую ячейку в результате чего произойдет автоматический перенос адресации.
- Активируйте первую функцию для возврата к ее редактированию. Это действие автоматически укажет количество символов до пробела.
- Соглашаемся и кликаем по кнопке «ОК».
В результате можно видеть, что ячейка откорректирована и фамилия внесена корректно. Чтобы изменения вступили в силу на всех строках, потяните маркер выделения вниз.
Этап №2. Переносим имена
Для разделения второго слова потребуется немного больше сил и времени, так как отделение слова происходит с помощью двух пробелов.
- В качестве основной формулы прописываем аналогичным предыдущему способу образом =ПСТР(.
- Выбираем ячейку и указываем позицию, где прописан основной текст.
- Переходим к графе «Начальная позиция» и вписываем формулу ПОИСК().
- Переходим к ней, используя предыдущую инструкцию.
- В строке «Искомый текст» указываем пробел.
- Кликнув по «Текст для поиска», активируем ячейку.
- Возвращаемся к формуле =ПСТР в верхней части экрана.
- В строке «Нач.позиция» приписываем к формуле +1. Это будет способствовать началу счета со следующего символа от пробела.
- Переходим к определению количества знаков – вписываем формулу ПОИСК().
- Перейдите по данной формуле вверху и заполните все данные уже понятным вам образом.
- Теперь в строке «Нач.позиция» можно прописать формулу для поиска. Активируйте еще один переход по формуле и заполните все строки известным способом, не указывая ничего в «Нач.позиция».
- Переходим к предыдущей формуле ПОИСК и в «Нач.позиция» дописываем +1.
- Возвращаемся к формуле =ПСТР и в строке «Количество знаков» дописываем выражение ПОИСК(« »;A2)-1.
Этап №3. Ставим Отчество
Примечание эксперта! Формула определит автоматически количество символов.
- Для точного определения количества знаков в конце необходимо написать: -ПОИСК().
- Перейдите к редактированию формулы. В «Искомый текст» укажите пробел. В «Текст для поиска» — адресацию ячейки. В «Нач.позиция» вставьте формулу ПОИСК(). Редактируйте формулу, установив те же самые значения.
- Перейдите к предыдущему ПОИСК и строке «Нач.позиция» допишите +1.
- Перейдите к формуле ПРАВСИМВ и убедитесь, что все действия произведены правильно.
Заключение
В статье прошло ознакомление с двумя распространенными способами разделения информации в ячейках по столбцам. Следуя нехитрым инструкциям, можно с легкостью освоить владение данными способами и использовать их на практике. Сложность разделения по столбцам, используя формулы, может оттолкнуть с первого раза неопытных пользователей Excel, но практическое применение метода, поможет привыкнуть к нему и применять его в дальнейшем без каких-либо проблем.
Читайте также: