Как поставить пробел в тексте в excel?

Инструмент «текст по столбцам».

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

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

Это все, что нужно сделать!

Измените таблицу Excel.

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

1. Экономьте время с помощью этих функций форматирования текста в Microsoft Excel

Для этого воспользуемся функцией ТЕКСТ в Excel. Эта функция имеет функцию преобразования чисел или дат в соответствующий текстовый формат. Однако у этого метода есть недостаток в том, что вам необходимо знать определенные коды формата. Хотя эти коды идентичны коду, используемому в функции форматирования настраиваемых чисел Excel, большинство пользователей редко их видят.

Например: формулы ТЕКСТ (B3, ‘$ #, ## 0.00’) и ТЕКСТ (C5, ‘M / dd / yy’) преобразуют числа и даты в текст (в этом случае преобразование станет ’12 345,67 долларов США’ и ’12 / 01.10′.

Совет: вам следует отформатировать поле, содержащее эти формулы, в другой форме (курсив, полужирный шрифт, другие цвета), чтобы знать эти числа только в виде обычного текста. В более старых версиях Excel эти числа не используются для вычислений, но в Excel 2016 вы можете использовать их во всех формулах, кроме таких функций, как СУММ и СРЕДНЕЕ. Преимущество форматирования дат и чисел в виде текста заключается в том, что при переключении с Excel на документ Word Mail Merge не возникает беспорядка.

Код формата для функции ТЕКСТ

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

Примечание: «0» и «#» действуют как заполнители для чисел, но «0» заставляет 0 в начале или в конце, «#» будет заменен числом, если оно не является верхним или нижним 0. Следовательно, код «00000» ‘гарантирует, что ноль в начале не будет удален из почтовых индексов Нью-Джерси.

Коды чисел, дат и времени можно использовать в текстовом или произвольном числовом формате Excel. Однако есть разница между тем, как эти коды работают в Excel и Word в Microsoft Office 2013. В Excel вы можете использовать M или m в течение месяцев или минут. В большинстве случаев Excel достаточно умен, чтобы определить, какую единицу вы используете, но Word не так умен, вам нужно использовать заглавные буквы для месяца и часто писать в течение минут. Кроме того, Excel добавил код MMMMM к результату единственного сокращения месяца (например, J, F, M, A и т. Д.)

Примечание: при преобразовании этих чисел в текст они не используются в функциях СУММ или СРЕДНЕЕ, если вы хотите, вам нужно преобразовать их снова, используя функцию ЗНАЧЕНИЕ.

ОКРУГЛ (функция ОКРУГЛ.)

Вместо функции ТЕКСТ некоторые пользователи предпочитают использовать функцию ОКРУГЛ, чтобы удалить дополнительные десятичные числа. Функция ОКРУГЛ. Округляет числа в большую или меньшую сторону до указанного вами десятичного разряда. Преимущество в том, что числа округления не в виде текста, вы все равно можете использовать их для расчета. Функция ОКРУГЛ может хорошо работать при слиянии писем, но редко используется, потому что:

1. Функция ОКРУГЛ удалит символ доллара или разделитель тысяч (запятую).
2. Функция ОКРУГЛ удалит нули в конце.
3. Иногда функция ОКРУГЛ отображает неправильные десятичные разряды при переключении на слияние. Например, в документе «Слияние» отображается 4 десятичных разряда, в то время как функция округления указывает 2 или 3 разряда.

В редких случаях функция ОКРУГЛ. Также отображает ошибки в слиянии писем, например, вместо 1.0014, слияния, показывающего 1.0013.

Как получить N-е слово из текста.

Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:

• ДЛСТР — чтобы получить общую длину.
• ПОВТОР — повторение определенного знака заданное количество раз.
• ПОДСТАВИТЬ — заменить один символ другим.
• ПСТР — извлечь подстроку.
• СЖПРОБЕЛЫ — удалить лишние интервалы между словами.

Общая формула выглядит следующим образом:

Где:

• Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
• N – порядковый номер слова, которое нужно получить.

Например, чтобы вытащить второе слово из A2, используйте это выражение:

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

Как работает эта формула?

По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:

ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))

Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.

• Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
• Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
• Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.

Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:

Следующий рисунок демонстрирует улучшенный вариант (2) в действии:

Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:

Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.

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

Если есть сотни чисел, которые хранятся в виде разброса текста на вашем листе, и вам необходимо преобразовать их все в числа сразу. Как этого добиться? В этой статье представлены три метода пакетного преобразования чисел, сохраненных в виде текста, в обычные числа в Excel.

Массовое преобразование соединительных чисел, хранящихся в виде текста, в числа в Excel

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

1. Выберите соединительные числа, которые хранятся в виде текста на вашем листе, и вы увидите небольшую кнопку отображать помимо выбора. Пожалуйста, щелкните по нему.

2. выберите Преобразовать в число из раскрывающегося списка. Затем все числа, которые хранятся в виде текста, немедленно преобразуются в числа.

С легкостью конвертируйте текст в числа или наоборот в Excel:

Kutools for Excel’s Преобразование текста в число Утилита поможет вам легко преобразовать весь текст в число или наоборот из выбранного диапазона в Excel, как показано ниже. Скачайте и попробуйте прямо сейчас! (30-дневная бесплатная трасса)

Массовое преобразование разрозненных чисел, хранящихся в виде текста, в числа в Excel

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

1. Введите число «1»В пустую ячейку, а затем скопируйте ее, нажав Ctrl + C ключи. Выделите разбросанные ячейки, которые нужно преобразовать в числа, удерживая Ctrl клавишу, а затем нажмите Ctrl + другой + V ключи одновременно.

2. в Специальная вставка в диалоговом окне выберите Размножаться вариант в Эксплуатация раздел, а затем щелкните OK кнопка. Смотрите скриншот:

Затем все числа, которые хранятся в виде текста в выбранных ячейках, сразу преобразуются в числа.

Массовое преобразование чисел, хранящихся в виде текста, в числа с Kutools for Excel

Наблюдения и советы этой статьи мы подготовили на основании опыта команды Преобразование текста в число полезности Kutools for Excel может помочь вам легко преобразовать все числа, которые хранятся в виде текста, в числа в диапазоне или нескольких диапазонах.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

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

2. в Преобразование текста в число в диалоговом окне выберите Текст на номер вариант, а затем щелкните OK кнопку.

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

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

Использование значений вставки

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

1. Выберите группу пустых ячеек, в которую вы хотите поместить вывод числовых данных. Выберите «Формат ячеек» во всплывающем меню.

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

1. Выберите весь столбец ячеек, который вы хотите преобразовать из текста в числа, щелкните правой кнопкой мыши и выберите «Копировать».

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

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

Как работать с символами и кодами в Excel

Каждый символ, вводимый вами и отображаемый на экране, имеет свой собственный числовой код. Например, код для прописной буквы А — это 65, а код для амперсанда (&) — 38. Эти коды присутствуют не только для алфавитных символов, вводимых вами с клавиатуры, но и для специальных символов, которые вы можете вывести на экран, используя их коды. Список всех символов и их кодов называется таблицей кодов ASCII.

Например, ASCII-код для символа равен 169. Для вывода данного символа необходимо ввести на клавиатуре Alt+0169, используя цифровую клавиатуру для ввода цифр. Таблица ASCII содержит коды от 1 до 255, при этом первые 31 цифра используются для непечатаемых символов, таких как возврат каретки и перевод строки.

Преобразовать в число

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

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

1. Выберите этот символ. В раскрывающемся списке выберите «Преобразовать в число».

Это обновит все выбранные вами текстовые числа до общего числового формата данных.

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

Как поставить пробел в Excel?

Как в формуле поставить пробел в Excel?

Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно сделать двумя способами. Добавьте двойные кавычки с пробелом между ними (» «). Например: =СЦЕПИТЬ(«Привет,»; » «; «мир!»).

Как обозначается пробел в Excel?

в поле Найти нужно ввести код символа неразрывного пробела (визуально этот символ ничем от обычного пробела не отличается). Для этого на цифровой клавиатуре (справа, там где клавиша NumLock), удерживая клавишу ALT , ввести цифры 0160. Подробнее об этом вводе читайте в статье Ввод символов с помощью клавиши ALT.

Как в Экселе сделать сцепку?

В случае, когда необходимо в одну ячейку объединить данные хранящиеся в разных ячейках, например, нужно «сцепить» ячейки хранящие отдельно Фамилию, Имя и Отчество, можно воспользоваться одним из вариантов сцепки: либо функцией Excel =Сцепить(), либо оператором «&».

Как увидеть пробелы в Excel?

1. Самый простой способ сделать это — просто изменить шрифт на шрифт, который имеет встроенный видимый глиф для пробела (или любой другой символ, который вам может понадобиться идентифицировать) …
2. CTRL+H заменяет все пробелы символом

Как убрать лишние символы в Excel?

Удаление непечатаемых символов

1. Аккуратно выделить мышью эти спецсимволы в тексте, скопировать их ( Ctrl+C ) и вставить ( Ctrl+V ) в первую строку в окне замены ( Ctrl+H ). Затем нажать кнопку Заменить все (Replace All) для удаления.
2. Использовать функцию ПЕЧСИМВ (CLEAN) .

Когда ставится пробел?

В русскоязычном наборе пробел ставится:

1. после запятой, точки (в том числе и обозначающей сокращения и инициалы), точки с запятой, двоеточия, вопросительного и восклицательного знака, многоточия (кроме многоточий, начинающих предложение);
2. перед многоточием, стоящим в начале предложения;

Как пишется неразрывный пробел?

Неразрывный пробел также можно ввести непосредственно, используя клавиатурную комбинацию Alt + 0160 . Случаи, когда следует использовать неразрывный пробел: инициалы («Иванов И.

Как сцепить диапазон ячеек Excel?

Для этого запишем формулу =СЦЕПИТЬ(A6:A9) , указав в качестве единственного аргумента весь диапазон сразу, а не отдельные ячейки. В результате получим лишь значение первой ячейки. Теперь в Строке формул выделим формулу =СЦЕПИТЬ(A6:A9) и нажмем клавишу F9 .

Как пользоваться формулой Правсимв?

Щелкнем в ячейку Н3 и нажмем кнопку мастер функций fx или комбинацию горячих клавиш SHIFT+F3. В окне мастера функций выберем категорию Текстовые, найдем функцию ПРАВСИМВ и нажмем кнопку ОК. На следующем шаге откроется окно аргументов функции ПРАВСИМВ.

Как связать значения ячеек Excel?

Создание связей между рабочими книгами

1. Открываем обе рабочие книги в Excel.
2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь.

Преобразовать в число

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

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

1. Выберите этот символ. В раскрывающемся списке выберите «Преобразовать в число».

Это обновит все выбранные вами текстовые числа до общего числового формата данных.

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

Извлечение числа из текста

Эта возможность будет полезной, если из строки нужно извлечь число. Для этого ее нужно применить в комбинации с функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР

Так, чтобы достать последние 3 знака из ячейки A2, а результат вернуть, как число, нужно применить такую формулу.

=ЗНАЧЕН(ПРАВСИМВ(A2;3))

Если не использовать функцию ПРАВСИМВ

внутри функцииЗНАЧЕН , то результат будет в символьном формате, что мешает выполнять любые вычислительные операции с получившимися цифрами.

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

Нам важно ваше мнение:

Функция СЖПРОБЕЛЫ в Excel и примеры ее использования

​ и убрать.​ пробела, который содержит​ALT​ интерпретировать в нашем​ первого (главного) вопроса​Selection.Find.ClearFormatting​ которые написаны с​ остальные ячейки и​ лишние доступы клавишей​ происходит подключение по​ форматирование. Не существует​. Как альтернативный вариант​

​ отобразился в поле,​ на помощь придет​ отображения формул, но​«Редактирование»​ о проделанной работе.​Скачать последнюю версию​ десятичное значение 160.​, ввести цифры 0160.​ случае как «избавляйся​ есть простые варианты​

Синтаксис функции СЖПРОБЕЛЫ в Excel

​Selection.Find.Replacement.ClearFormatting​ лишними пробелами. Поэтому​ смотрим результат. Программа​ Backspace, можно воспользоваться​ RDP к 1с​ универсального способа, который​ можно после выделения​ жмем на кнопку​ функция из текстовой​ в то же​на ленте во​ Жмем на кнопку​ Excel​

​ Этот знак как​ Подробнее об этом​ от пробелов».​ решения?​With Selection.Find​

​ команда ВПР не​ выдала ответ по​ специальной функцией –​ 8.3 (WinServer 2008​ было бы максимально​ набрать сочетание клавиш​«OK»​ группы операторов​ время некоторые пользователи​ вкладке​«OK»​Урок:​ HTML-сущности​

​ вводе читайте в​

Пример использования функции СЖПРОБЕЛЫ

​Если разряды числа разделены​карандаш​.Text = «(​ выдала по ним​ плюшевым зайцам: 3.​ СЖПРОБЕЛЫ.​ R2). В 1с​ удобно использовать во​Ctrl+C​.​СЖПРОБЕЛЫ​ считают, что без​«Главная»​.​Удаление больших пробелов в​& nbsp;​ статье Ввод символов​

​ неразрывными пробелами (код​: Невозможно отредактировать предыдущее​ ) «​ результат. Чтобы исправить​ Хотя мы видим,​Да, проговорить название команды​

​ формируется некая табличная​ всех ситуациях. В​.​Как видим, содержимое ячейки​.​ него таблица будет​. В добавочном меню​Далее опять появляется окно​ Microsoft Word​обычно используется на веб-страницах.​ с помощью клавиши​ символа 160), то​ сообщение (через сутки).​.Replacement.Text = «\1^s»​

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

​Выделяем исходный диапазон данных.​ отобразилось в области,​Данная функция удаляет все​ выглядеть лучше. Давайте​ выбираем пункт​«Найти и заменить»​Сразу нужно сказать, что​ Самостоятельно Функция СЖПРОБЕЛЫ​ ALT.​ EXCEL никакого «правила»​ Поэтому здесь.​.Forward = True​ ВПР функцию СЖПРОБЕЛЫ​ быть 3+2=5. В​ Но вот понять​ ячеек из этой​ оптимальным использовать один​

СЖПРОБЕЛЫ с другими функциями

​ Кликаем по выделению​ в которой находится​ пробелы из текста​ рассмотрим, как убрать​«Заменить»​. Выполняем в этом​ пробелы в Excel​ не удаляется этой​поле «Заменить на» должно​ на этот случай​Нашёл следующее (может​.Wrap = wdFindContinue​ и посмотрим, что​ чем проблема? В​ ее синтаксис и​

​ табличной формы и​ вариант, а во​ правой кнопкой мыши.​ функция, но уже​ выделенного диапазона, кроме​ такой вариант разделения.​.​ окне точно такие​ могут быть разного​ неразрывного пробела. Пример​ быть пустым;​ не имеет –​ кому пригодится):​

​.Format = False​ получится.​ лишних пробелах.​

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

​нажмите «Заменить все».​ после вставки мы​1. Правила оформления​.MatchCase = False​Т.к. пробелы нам нужно​Запишем функцию в ячейке​ легко. Если начать​ Excel 2013 на​ и т.д. Например,​ в блоке​ Мы выполнили удаление​ словами. То есть,​

Как еще можно удалить лишние пробелы в Excel?

​ сделаны с помощью​«Найти и заменить»​ описаны во втором​ быть пробелы между​ из текста читайте​Числа будут преобразованы из​ получим текстовое значение,​ записи для замены:​

​.MatchWholeWord = False​ удалить в искомом​ D3. В качестве​ вводить команду, то​ машину с Win10​ с удалением двойного​«Параметры вставки»​ пробелов только для​ она способна решить​ инструментов форматирования, то​

​во вкладке​ пункте данной инструкции​ словами, пробел в​ верхней 10 способов​ текстового в числовой​ с которым нельзя​ http://artefact.lib.ru/design/text_khozyainov.shtml​.MatchAllWordForms = False​ значении, команду СЖПРОБЕЛЫ​

Получить текст между двумя экземплярами одного и того же символа в Excel

Общая формула для извлечения текста из строки между двумя вхождениями одного и того же символа:

СРЕДНЯЯ(клеткаПОИСК(уголь, клетка) +1, ПОИСК (уголь, клеткаПОИСК (уголь, клетка) +1) — ПОИСК (уголь, клетка)-1)

Например, чтобы извлечь текст, заключенный в двойные кавычки, из строки в ячейке A2, введите следующую формулу в ячейке B2:

=СРЕДН(A2, ПОИСК(«»»», A2) +1, ПОИСК(«»»», A2, ПОИСК(«»»»,A2) +1) — ПОИСК(«»»», A2) -1 )

Обратите внимание на то, как вы ищете двойную кавычку в Excel. Между внешними кавычками вводится еще один набор кавычек

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

Другой способ поставить двойную кавычку («») в формулу Excel — использовать функцию СИМВОЛ с кодовым номером 34.

=MID(A2, ПОИСК(СИМВОЛ(34), A2) +1, ПОИСК(СИМВОЛ(34), A2, ПОИСК(СИМВОЛ(34),A2) +1) — ПОИСК(СИМВОЛ(34), A2) -1 )

Как работает эта формула

Первые два аргумента этой формулы MID не вызывают вопросов:

• A2 — это текстовая строка для поиска, а
• SEARCH(«»»», A2) +1 — начальный номер, т.е. позиция первой кавычки +1.

Самая сложная часть — вычисление количества символов для извлечения (num_chars):

Во-первых, мы находим позицию второй цитаты, вложив одну функцию ПОИСК в другую.

ПОИСК(«»»», A2, ПОИСК(«»»»,A2) +1)

Из позиции 2-й цитаты (в А2 это 27) вычесть позицию 1-й цитаты (в А2 это 10), а затем вычесть 1, чтобы исключить из результата саму цитату:

ПОИСК(«»»», A2, ПОИСК(«»»»,A2) +1) — ПОИСК(«»»», A2) -1

Приведенная выше формула возвращает 16, что является последней недостающей частью головоломки, в которой нуждается функция MID:

=СРЕДНЕЕ(A2, 10+1, 16)

Проще говоря, MID ищет ячейку A2, начиная с символа после первой кавычки (10+1), и возвращает следующие 16 символов.

Обходной путь

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

Способ 1. Форматирование ячейки как текста

Для этого выполните следующие действия:

Щелкните правой кнопкой мыши поле со списком и выберите пункт Формат ячеек.

На вкладке Число выберите Текст, а затем нажмите ОК.

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

Если вы не хотите видеть предупреждающие стрелки, нажмите на маленькую стрелку, а затем Пропустить ошибку.

Способ 2. Использование одинарной кавычки

При вводе длинного числа сначала введите одинарную кавычку (‘) в ячейке, а затем длинное число.

Например, введите ‘1234567890123456789, и одинарная кавычка не будет отображаться после нажатия ENTER.

Как извлечь число из текстовой строки в Excel 2016

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

Мы будем использовать функцию ОБЪЕДИНИТЬ. Что она делает? Она извлекает все численные значения из строки. Например, у нас есть строка: «Витя купил мороженку за 100 рублей, а Вася за 20 рублей». Результатом выполнения функции будет число «10020».

Формула:

Так как мы работаем с массивом, после написания формулы в ячейку нажмите CTRL + SHIFT + ENTER.

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

Что функция делает?

Давайте разберем функцию по частям:

СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))) — вычисление начнется с функции ДЛСТР, она вернет нам длину строки. Функция строка вернет массив чисел начиная с 1 и заканчивая тем числом, которое равняется длине нашей строки.

(ПСТР(A2,СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)))),1)*1) — в этой части функции «отметаются» все текстовые значения, они все «падают» в ошибку #!ЗНАЧЕНИЕ. А численные значения остаются.

ЕСЛИОШИБКА((ПСТР(A2,СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2))),1)*1),»») — в этой части функции все ошибки, полученные ранее удаляются и остаются только численные значения.

=ОБЪЕДИНИТЬ(«»;ИСТИНА;ЕСЛИОШИБКА((ПСТР(A2;СТРОКА(ДВССЫЛ(«1:»&ДЛСТР(A2)));1)*1);»»)) — и, наконец, все значения полученные ранее просто объединяются функцией ОБЪЕДИНИТЬ.

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

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

Разница между этими формулами в том, что функция ЕСЛИ проверяет ошибка ли полученное от ПСТР значение или нет.

Также, как и в прошлом варианте, функция ОБЪЕДИНИТЬ используется для объединения значений.

Синтаксис функции ОТБР

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

Обобщённый синтаксис выглядит следующим образом:ОТБР(число; )

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

Ещё раз повторим назначение обоих аргументов:

• «Число» — Усекаемое число.
• «Число_разрядов» — Целое число, определяющее точность усечения. Значение по умолчанию — ноль (если не указывать этот аргумент).

По принципу действия функция ОТБР похожа на формулу ЦЕЛОЕ, но в данном случае дробная часть просто отбрасывается.

При усечении положительных и отрицательных чисел функция ОТБР ведёт себя одинаково, то есть результат операции от знака числа не зависит.

Каких-то особенностей функция ОТБР не имеет. Единственное, что стоит учитывать, это обработка даты, — если её указать в качестве первого аргумента, то получим на выходе число, но это происходит только потому, что Excel воспринимает дату как число. Это можно считать ошибкой. Используйте ЕЧИСЛО, чтобы проверять усекаемое число.

При попытке обработать текстовое значение, функция ОТБР вернёт ошибку «#ЗНАЧ!», которую можно перехватить при помощи ЕСЛИОШИБКА. Пустая ячейка приравнивается к нулю.

Типичный сценарий

Функция ТЕКСТ редко используется сама по себе, а чаще применяется в сочетании с чем-то еще. Предположим, что вы хотите объединить текст и числовое значение, например, чтобы получить строку “Отчет напечатан 14.03.12” или “Еженедельный доход: 66 348,72 ₽”. Такие строки можно ввести вручную, но суть в том, что Excel может сделать это за вас. К сожалению, при объединении текста и форматированных чисел, например дат, значений времени, денежных сумм и т. п., Excel убирает форматирование, так как неизвестно, в каком виде нужно их отобразить. Здесь пригодится функция ТЕКСТ, ведь с ее помощью можно принудительно отформатировать числа, задав нужный код формата, например “ДД.ММ.ГГГГ” для дат.

В примере ниже показано, что происходит, если попытаться объединить текст и число, не применяя функцию ТЕКСТ. Мы используем амперсанд (&) для сцепления текстовой строки, пробела (” “) и значения: =A2&” “&B2.

Вы видите, что значение даты, взятое из ячейки B2, не отформатировано. В следующем примере показано, как применить нужное форматирование с помощью функции ТЕКСТ.

Вот обновленная формула:

ячейка C2:=A2&” “&ТЕКСТ(B2;”дд.мм.гггг”) — формат даты.

Как отличить настоящее число от числа, отформатированного как текст?

Разработчики Excel об этом позаботились. Числа в ячейках с числовым форматом выравниваются по правому краю ячеек, а с текстовым форматом – по левому. Кроме того, Excel осуществляет фоновый контроль ошибок, конечно при условии того, что он настроен в параметрах Excel, в разделе «Формулы».

Когда Excel встречает в ячейках листа числа, отформатированные как текст — помечает их индикатором ошибок. Индикатор ошибок представляет собой цветной треугольник (цвет настраивается, по умолчанию он зеленый) в левом верхнем углу ячейки. Индикатор ошибок появляется в ячейках только при нарушении одного из правил контроля ошибок.

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

1) Если в ячейку с текстовым форматом, внести числовое значение, а затем формат ячейки изменить на числовой, то число остается отформатированным как текст и не будет участвовать в расчетах;

2) Если в настройках Excel отключен контроль ошибок, то индикатор ошибок не отображается, при этом числа отформатированные как текст, становятся незаметными, что может привести к ошибкам в вычислениях;

3) Если контроль ошибок включен, а правило контроля «Числа, отформатированные как текст или с предшествующим апострофом» выключено, то индикатор ошибок не отображается и числа, отформатированные как текст, становятся незаметными, что также может привести к ошибкам в расчетах;

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

Внимательный читатель может спросить «А как же выравнивание числовых значений по правому краю, а текстовых – по левому? Видно же различие!» Видно, если не установлен автоподбор ширины столбцов, если ширина столбцов заметно больше длины значений, находящихся в ячейках, и выравнивание по разным краям ячейки визуально заметно, если не установлено выравнивание по центру. Кроме всех перечисленных «если» числа с текстовым форматом могут попадать в таблицы при переносе этих таблиц в Excel, например, из текстового редактора Word.

В некоторых случаях в ячейках с числами могут стоять какие-либо непечатаемые символы, что также превращает числа в текст.

Преобразовать текст в число с помощью формулы

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

В Microsoft Excel есть специальная функция — ЗНАЧЕН (VALUE в английском варианте). Она обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для трансформирования.

Функция ЗНАЧЕН может даже распознавать набор цифр, включающих некоторые «лишние» символы.

Например, распознает цифры, записанные с разделителем тысяч в виде пробела:

Конвертируем число, введенное с символом валюты и разделителем тысяч:

Обе эти формулы возвратят число 1000.

Точно так же она расправляется с пробелами перед цифрами.

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

Функция ЗНАЧЕН также пригодится, когда вы извлекаете что-либо из символьной строки с помощью одной из текстовых функций, таких как ЛЕВСИМВ, ПРАВСИМВ и ПСТР.

Например, чтобы получить последние 3 символа из A2 и вернуть результат в виде цифр, используйте следующее:

На приведенном ниже рисунке продемонстрирована формула трансформации:

Если вы не обернете функцию ПРАВСИМВ в ЗНАЧЕН, результат будет возвращен в виде набора символов, что делает невозможным любые вычисления с извлеченными значениями.

Этот метод подходит, когда вы точно знаете, сколько символов и откуда вы желаете получить, а затем превратить их в число.