Excel строка символов в число - Word и Excel - помощь в работе с программами

Как преобразовать текст в число в Excel — 10 способов.

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

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

Перестает правильно работать сортировка данных, поскольку числовые и текстовые значения упорядочиваются по-разному. Функции поиска, подобные ВПР, также не могут найти нужные значения (подробнее об этом читайте – Почему не работает ВПР?). Подсчет по условиям СУММЕСЛИ и СЧЁТЕСЛИ даст неверные результаты. Если они находятся среди «нормальных» чисел, то функция СУММ их проигнорирует, а вы этого даже не заметите. В результате – неверные расчеты.

Как выглядят числа-как текст?
Используем индикатор ошибок.
Изменение формата ячейки может преобразовать текст в число
Специальная вставка
Инструмент «текст по столбцам»
Повторный ввод
Формулы для преобразования текста в число
Как можно использовать математические операции
Удаление непечатаемых символов
Использование макроса VBA
Как извлечь число из текста при помощи инструмента Ultimate Suite

Из этого материала вы узнаете, как преобразовать строки в «настоящие» числа.

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

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

В некоторых случаях индикатор ошибки не отображается для чисел, записанных в виде текста. Но есть и другие визуальные индикаторы текстовых чисел:

Число

Строка (текстовое значение)

На изображении ниже вы можете видеть текстовые представления чисел справа и реальные числа слева:

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

Используем индикатор ошибок.

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

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

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

Смена формата ячейки.

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

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

Выберите ячейки с цифрами в текстовом формате.
На вкладке «Главная » в группе «Число» выберите « Общий» или « Числовой» в раскрывающемся списке «Формат» .

Или же можно воспользоваться контекстным меню, вызвав его правым кликом мышки.

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

Этот способ не слишком удобен и достался нам «в наследство» от предыдущих версий Excel, когда еще не было индикатора ошибки в виде зелёного уголка.

Примечание. Этот метод не работает в некоторых случаях. Например, если вы примените текстовый формат, запишете несколько цифр, а затем измените формат на «Числовой». Тут ячейка все равно останется отформатированной как текст.

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

Совет. Если зеленых уголков нет совсем, то проверьте — не выключены ли они в настройках вашего Excel (Файл — Параметры — Формулы — Числа, отформатированные как текст или с предшествующим апострофом).

Специальная вставка.

По сравнению с предыдущими методами этот метод требует еще нескольких дополнительных шагов, но работает почти на 100%.

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

Выделите клетки таблицы с текстовым номером и установите для них формат «Общий», как описано выше.
Скопируйте какую-нибудь пустую ячейку. Для этого либо установите в нее курсор и нажмите Ctrl + C, либо щелкните правой кнопкой мыши и выберите «Копировать» в контекстном меню.
Выберите клетки таблицы, которые вы хотите трансформировать, щелкните правой кнопкой мыши и выберите «Специальная вставка». В качестве альтернативы, нажмите комбинацию клавиш Ctrl + Alt + V.
В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить» и затем «Сложить» в разделе «Операция».
Нажмите ОК.

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

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

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

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

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

Повторный ввод.

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

Для этого сначала установите их формат на «Обычный». Затем в каждую из них введите цифры заново.

Думаю, вы знаете, как корректировать ячейку — либо двойным кликом мышки, либо через клавишу F2.

Но это, конечно, если таких «псевдо-чисел» немного. Иначе овчинка не стоит выделки. Есть много других менее трудоемких способов.

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

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

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

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

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

=ЗНАЧЕН(«1 000»)

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

=ЗНАЧЕН(«1 000 $»)

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

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

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

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

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

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

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

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

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

Математические операции.

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

Что это может быть? Например, сложение с нулём, умножение или деление на 1.

=A2+0

=A2*1

=A2/1

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

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

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

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

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

Эту напасть можно удалить программным путем при помощи формулы. Аналогично предыдущему примеру, в С2 можно записать примерно такое выражение:

=ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))

Поясню, как это работает. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН, как мы уже говорили ранее, преобразует текст в число.

Макрос VBA.

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

Нажмите сочетание клавиш Alt+F11 или откройте вкладку Разработчик (Developer) и нажмите кнопку Visual Basic. В появившемся окне редактора добавьте новый модуль через меню Insert — Module и скопируйте туда следующее небольшое выражение:

Sub Текст_в_число()   
   Dim rArea As Range
   On Error Resume Next
   ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
   If Err Then Exit Sub
   With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
   For Each rArea In Selection.Areas
   rArea.Replace ",", "."
   rArea.FormulaLocal = rArea.FormulaLocal
   Next rArea
   With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.

Что делает этот макрос?

Вы можете выделить несколько областей данных для конвертации (можно использовать мышку при нажатой клавише CTRL). При этом, если в ваших числах в качестве разделителя десятичных разрядов используется запятая, то она будет автоматически заменена на точку. Ведь в Windows чаще всего именно точка отделяет целую и дробную части числа. А при экспорте данных из других программ запятая в этой роли встречается почему-то очень часто.

Чтобы использовать этот код, выделяем область на рабочем листе, которую нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.

Открывается окно имеющихся макросов. Находим «Текст_в_число», указываем на его и жмем на кнопку «Выполнить».

Извлечь число из текстовой строки с помощью Ultimate Suite

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

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

Вот как вы можете быстро получить число из любой буквенно-цифровой строки:

Перейдите на вкладку Ablebits Data > Текст и нажмите Извлечь (Extract) :

Выделите все ячейки с нужным текстом.
На панели инструмента установите переключатель «Извлечь числа (Extract numbers)».
В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите переключатель «Вставить как формулу (Insert as formula)» или оставьте его неактивным (по умолчанию).

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

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

Нажмите кнопку «Вставить результаты (Insert results)». Вот и всё!

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

В этом примере мы решили вставить результаты как формулы , и надстройка сделала именно то, что было запрошено:

=ЕСЛИ(МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9},A2&»_0123456789″))>ДЛСТР(A2), «»,СУММПРОИЗВ(ПСТР(0&A2,НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2,СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))),1))*СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))),0),СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))))+1,1)*10^СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2)))/10))

Сложновато самому написать такую формулу, не правда ли?

Если отсутствует флажок «Вставить как формулу», вы увидите число в строке формул.

Любопытно попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами

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

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

Также рекомендуем:

Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…

Как быстро извлечь число из текста в Excel — В этом кратком руководстве показано, как можно быстро извлекать число из различных текстовых выражений в Excel с помощью формул или специального инструмента «Извлечь». Проблема выделения числа из текста возникает достаточно…

Как умножить число на процент и прибавить проценты — Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак…

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

Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…

Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…

5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…

Источник

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

Смотрите также строки. аргумента используется записьФункция ЛЕВСИМВ в Excel база данных которого совместимости с прочимиили что пустые ячейкитекст0.00р. «избыток»;-0.00р. «дефицит»  образом. Можно отформатировать ячейки, которые содержат не так просто, «ОК» — текст  ячейки и значением, числа, считая их
 окне на закладкуПри работе сПримечания 1: ДЛСТР(B5)-НАЙТИ(«:»;B5)-1, определяющая позицию используется для получения не поддерживает денежный программными продуктами, поддерживающимиокрашены цветом вручную в не считаются в, чтобы управлятьЭто значение отображается в ячейки, содержащие 15, данные. При выборе
как предыдущая. Существуют Excel преобразуется в которое в нее текстом. Это может
«Заменить». Ничего не данными из других
Если необязательный аргумента [число_знаков]
символа «u» в части текста и формат данных (только
работу с таблицами. Excel строке состояния. Получится способом отображения чисел. формате «Денежный». Кроме 100 и 200, блока ячеек подсчитывается специальные надстройки, которые  число. внесено, и сигнализирует произойти при копировании ставим, если не
программ (например, явно не указан, слове «unknown» (единица возвращает заданное количество числовой). Было принято
Функция полезна в случаях,. так. В формуле используется того, если в чтобы они отображались количество выбранных ячеек. добавляются в программу.Ту же операцию можно
нам об этом. данных из другого нужно пробела междускопировали данные из 1С по умолчанию будет вычтена для удаления символов текстовой строки, решение создать отдельную
когда числовые значенияМожно применить фильтрВыделены три строки. базовое значение из ячейке находится положительное на листе как Если выбранная строка Затем для изменения произвести с помощью Теперь нужно только источника – другой
цифрами. Или ставим) в Excel, возникает возвращен первый символ пробела).
при этом отсчет таблицу, в которой внесены в ячейки по цвету.Быстро выделить столбец ячейки, на который
 значение (или 0),
15 # продукт, или столбец содержит отображения чисел вызываются контекстного меню. Выделив верно использовать этот программы, интернет-странички и пробел (нажимаем один проблема форматов чисел,
строки. Например, функцияВ результате получим: символов начинается слева. вся информация о с установленным текстовымЗатем выделить ячейки и можно так. Выделяем указывает ссылка (в
после него будет продукт #100 и только одну ячейку функции. нужный диапазон, кликните сигнал. т. д. Проблема раз на клавишу п.ч. число записано =ЛЕВСИМВ(«текст») вернет значениеПример 3. Создать функциюПример 1. В таблице зарплате представлена в форматом данных. Также посмотреть количество в верхнюю ячейку столбца. данном примере.4) —

excel-office.ru

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

показан текст «(излишек)», 200 # продукта. с данными, строкаЕще один способ – правой кнопкой мыши,Выделите поле или ряд также может появиться пробела). Нажимаем кнопку в текстовом формате. «т». для округления дробных Excel содержатся имена виде числовых значений. в Excel можно строке состояния. Нажимаем на клавишу

Число в текстовом формате: причины и проблемы

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

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

целых значений с текстом, определяющим их образом: поиск чисел, отформатированных форматированием. Отфильтровали по курсор в нижнюю (40%). Чтобы восстановить после него будет с текстом, не так как подразумевает Откроется то же Несколько разрозненных ячеек или БД.Исчезла запятая, но числаExcel должно быть взято использованием формул для тип данных. Необходимо

Для конвертирования денежных данных	в качестве текста
цвету. В строке	ячейку столбца. Нажимаем
форматов чисел используйте	показан текст «(недостаток)».
изменяя порядок сортировки	.
создание пользовательских справочников	окно.
можно выделить с	Обратите внимание на то,
в ячейках написаны	,
из диапазона неотрицательных	работы с текстом.

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

Исправление ошибки

функциюПри объединении чисел и число. Таким образомIf you need to – именованных диапазоновЕще один способ преобразовать помощью клавиши Ctrl, как выглядит поле справа, значит ещекак изменить формат числа чисел, то естьФорма для округления числовых переменных и записать использована функция ЗНАЧЕН. их в числовой выделено 3 строкиИли нажимаем сочетание

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

(ячейки). клавиш «Ctrl» +. текст и больше номера без изменения in cells, see Затем эти имена в Excel – выделяют, кликнув мышкой в уголке поляТеперь снова выделяемExcel нулю или больше вид: столбец. каждого сотрудника по-отдельности.Чаще всего функция ЗНАЧЕНКак преобразовать формат

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

«А». Активная ячейка,Функция СЦЕПИТЬ не функция числа значения. Count characters in используются в формуле формула. Вставьте рядом на заголовке. Чтобы вы видите маленький столбец и, через. нуля. В противном

В ячейке B3 введенаИсходная таблица данных: Приведем пример использования используется для преобразования данных из других при этом, находитсяСЦЕП становятся как числовыеВыполните следующие действия. cells.

на основе функции с проблемной ячейкой выделить весь лист, зеленый треугольник, это функцию «Найти иВ Excel есть два случае будет возвращен следующая сложная формула:Для выделения первого символа, для сотрудника с результатов работы функций программ в формат в таблице.Функция ТЕКСТ значения. Это означает,

Выделите ячейки, формат которыхIf you want to «ПОДСТАВИТЬ». или графой пустой нужно нажать на значит, что числовая заменить», меняем точку формата код ошибки #ЗНАЧ!.

Использование формул и макросов

=5,ЛЕВСИМВ(A2+1,НАЙТИ(«.»,A2)-1),ЛЕВСИМВ(A2,НАЙТИ(«.»,A2)-1)),»Число не введено»)’ которым является искомое фамилией Иванов: ПСТР и ЛЕВСИМВ числа Excel, чтобыО других сочетанияхФункция TEXTJOIN что больше не требуется изменить. know how manyКакой бы способ вы столбец. Введите в соединение заголовков записей информация в нем на запятую.– формат ячеекЕсли в качестве аргумента

class=’formula’> название переменной, используем=ЗНАЧЕН(B2) (ПРАВСИМВ), однако для формулы считали такие клавиш, чтобы выделитьГоворят, что нет может выполнять любыеНа вкладке cells have data, ни выбрали, чтобы верхнее поле нового и граф в введена как текстовая.Получится так. и формат данных. [число_знаков] будет переданыЛогика работы: формулу:Аргументом функции является поле корректного отображения к числа, смотрите в

ячейки, смотрите в специальной функции без математических операций наГлавная see Use COUNTA в Excel преобразовать столбца формулу. Для верхнем левом углу Описанная выше проблемаОбратите внимание. Если мы скопировали текстовые данные, функцияПервая функция ЕСЛИ проверяет

Второй равен 1 по

денежного формата, содержащее результатам вычислений данных

статье "Преобразовать текст

Обратная задача

статье «Сочетание клавиш макросов них.в группе to count cells текст в число, этого напишите «=». страницы Excel. при попытке произвестиЧисла сразу сдвинулись данные из другой ЛЕВСИМВ вернет код с помощью формулы

умолчанию, поэтому функция информацию о заработной функций можно просто в число Excel». в Excel».посчитать количество выделенных строкДля объединения чисел счисло that aren’t blank. применив его на Программа воспринимает этот

Рядом с выделенным диапазоном вычисления также говорит вправо ячейки – программы с другим ошибки #ЗНАЧ!. ЕПУСТО, заполнена ли возвращает первый символ. плате Иванова. Аналогично прибавить 0 (нуль).Функция ЗНАЧЕН в Excel

Четвертый способ

fb.ru

Подсчет числа строк или столбцов

 в Excel помощью функции СЦЕПИТЬщелкните стрелку.Чтобы контролировать сообщения, которые практике несколько раз, знак как намерение полей появится значок об этом. это числовой формат. форматом значений (чисел),Если число, переданное в ячейка, которая должна Аналогично выделим названия производится расчет для Подробнее рассмотрим это выполняет операцию преобразования.. Неправда. Есть много или функции ОБЪЕДИНЕНИЯ,В списке появляются в строке вы приспособитесь делать

добавления вычисления и – кнопка сЕще одна проблема при Всё. Теперь можно то нужно изменить качестве необязательного аргумента, содержать значение, которое переменных из других остальных сотрудников. В в одном из строки в числовоеПосчитать количество строк других приемов, чтобы

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

хранении цифр как

считать эти числа, формат значений. А превышает количество знаков требуется округлить. строк. В результате результате получим: примеров. значение в тех можно с помощью посчитать выделенные строки, и амперсанд (&)выберите категорию, например

 состояния правой кнопкой эффективно, получая нужный

Нажмите значок функции

на наличие ошибки. строк возникает при т.д. Excel теперь, изменение формата ячеек, в текстовой строке,
Если число введено, запись получим:Пример 3. Самолет вылетел случаях, где этофункции «Автосумма» в Excel
ячейки Excel. Было оператор.настраиваемые мыши и выберите результат. fx под панелью При нажатии на

support.office.com

Объединение текста и чисел

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

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

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

Итак, мы скопировалиExcel выполняет автоматическое преобразование после запятой и ЛЕВСИМВ получилось вытащить был совершить посадку выгружены некоторые данные случае удачно выполненного цифра количества строк.Один из способовВ Excel 2016Excel Mobile и на то, котороеПримечание:Мы стараемся как строке поиска введите показано, какая именно 211, 15, 1021,

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

Нажимаем стрелку у

узнать количество выделенных Excel Online с
вы хотите.Мы стараемся как можно оперативнее обеспечивать текст «значен» и проблема в выделенном
26, 100, 128. таблицами Excel, когда в Excel написаны где это возможно. с числом 5. из каждой ячейки перелета в 17:20. в ассортименте интернет-магазина.
код ошибки #ЗНАЧ!, кнопки «Автосумма» на строк – это помощью функцииВ поле можно оперативнее обеспечивать

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

закладке Excel «Главная»	настроить порядковые номера	ОБЪЕДИНЕНИЯ
Тип	вас актуальными справочными	материалами на вашем В параметрах функции сохранено как текст»). представляет трудности, и строк, смотрите вПро форматы читайте статью возможные варианты записи 5, будет возвращен
символу. произошел инцидент, в	как числовую, так	в число невозможно. и выбираем функцию строк после фильтра.замененаизмените коды числовых
материалами на вашем языке. Эта страница укажите нужную ячейку	Нажмите пункт «Преобразовать	программа легко с статье «Не работают «Формат Excel». функции ЛЕВСИМВ и результат выполнения функции связи которым рейс и текстовую информацию,Данная функция имеет следующую «Число». Смотрите об этом

Объединение текста и чисел из разных ячеек в одной ячейке с помощью формулы

функции СЦЕПИТЬ форматов в формате, языке. Эта страница переведена автоматически, поэтому и нажмите «ОК». в число». Зеленые ней справляется. Но, формулы в Excel»Вспоминаем. результаты ее выполнения:

ЛЕВСИМВ(B2+1;НАЙТИ(«,»;B2)-1), то естьПример 2. В таблицу был задержан на поскольку используемый для синтаксическую запись:Эта функция считает только

 способе статью «Порядковый. Несмотря на

который вы хотите переведена автоматически, поэтому ее текст может В соседней с индикаторы ошибки сразу если эти числа тут.Значения в текстовомЛЕВСИМВ(«текст»;200%) вернет значение «те» целой части числа Excel подтягиваются данные определенное время. Самолет выгрузки данных парсер=ЗНАЧЕН(текст) заполненные строки. Если номер строк по то, что функция создать.
 ее текст может содержать неточности и искомой ячейкой вы исчезнут, и над записаны в строковомКак преобразовать текстовый формате пишутся у (преобразование процентов в +1 по правилам из другого программного приземлился в 19:13. сработал некорректно. НеобходимоЕдинственный аргумент — текст есть пустые ячейки порядку после фильтра

Примеры

СЦЕПИТЬДля отображения текста и

содержать неточности и грамматические ошибки. Для получите число вместо числами можно будет формате, сортировка будет формат даты в левой границы ячейки. числовое значение); округления. продукта. Одно из Необходимо определить время выполнить подсчет общего является обязательным для в столбце, то в Excel»по-прежнему доступен для чисел в ячейке, грамматические ошибки. Для нас важно, чтобы текста. Чтобы скопировать проводить необходимые действия. кардинально отличаться от формат даты вВ нашем случае,

См. также

при аргументах: («текст»;ИСТИНА) вернет
Если
полей данных получило
задержки в часах

support.office.com

Количество выделенных строк в Excel.

числа товаров. заполнения. То есть, в формуле исправляемВторой способ обеспечения обратной совместимости, заключите текст в нас важно, чтобы эта статья была формулу на всеЕще один способ, помогающий привычной и необходимой.
 Excel, смотрите в числа написаны в «т» (логическое ИСТИНАПримечание: данный алгоритм нельзя ошибку. Для обработки и минутах. ДанныеЧасть таблицы выглядит следующим данная функция принимает адрес первой ячейки–
 следует использовать двойные кавычки (» эта статья была вам полезна. Просим поля столбца, кликните
преобразовать текст в Эта разница показана статье «Преобразовать текст ячейке слева, значит, эквивалентно числовому значению назвать гибким и ошибки необходимо выделить о времени вылета, образом: на вход текстовые диапазона. Например, мыпосчитать количество выделенных ячеекОБЪЕДИНЕНИЯ «), или чисел вам полезна. Просим вас уделить пару мышкой в правом число в Excel, в таблице: в дату Excel».
 числа записаны как 1);
он имеет некоторые ее код и расчетного и фактическогоДля расчета общего количества данные, которые могут вставили формулу функцией в столбце Excel, так как с помощью обратной вас уделить пару секунд и сообщить, нижнем углу ячейки,
— использование спецвставки.Сортировка в числовом формате Как изменить размер текст. Формулы непри аргументах: («текст»;ЛОЖЬ) вернет недостатки (например, не описание из строки. времени прибытия занесены
представленных 5 наименований быть представлены в «Автосумма». В этой.функции СЦЕПИТЬ
косой черты () секунд и сообщить, помогла ли она содержащей ее, получив В одну из
Сортировка в текстовом формате ячеек, высоту строк,
будут считать такие пустое значение «», учтен вариант вводаИсходные данные: в ячейки таблицы продукции используем следующую виде текстовой строки, формуле указан диапазонВыделяем ячейки вмогут быть недоступны в начале. помогла ли она вам, с помощью курсор в виде свободных ячеек внесите10 ширину столбцов, ширину числа. Нам нужно поскольку логическое ЛОЖЬ сразу целого числа).Выделим код ошибки с формата «Дата». формулу: введенной в кавычках до первой пустой столбце и смотрим в будущих версиях
Примечание:
 вам, с помощью кнопок внизу страницы. изображения крестика, зажмите
число 1, предварительно10 объединенных строк, какпреобразовать текст в число эквивалентно числовому 0 Он приведен в
 помощью следующей формулы:
Таблица имеет следующий вид:Функция СУММ принимает числовые (например, ЗНАЧЕН(“9 300 ячейке столбца. =СЧЁТ(A4:A6) внизу листа Excel Excel.изменение встроенного формата
 кнопок внизу страницы. Для удобства также
 его и проведите установив формат как15 воспользоваться автоподбором данныхExcel (нуль);
качестве примера использованияОписание:
Для решения задачи запишем значения и производит ₽”) или ссылки
Мы адрес ячейки в строке состоянияTEXTJOIN не приводит к Для удобства также приводим ссылку на
вниз по всей общий: на вкладке100 в ячейках или, значение преобразовать впри аргументах: («текст»;15/7) вернет функции ЛЕВСИМВ. ДляЛЕВСИМВ(B5;НАЙТИ(«3»;B5)+1) – функция, выделяющая

excel-office.ru

Функция ЗНАЧЕН в Excel для преобразования текста в число

следующую формулу: их суммирование. В на ячейку таблицы, А4 исправляем на цифру «Количество». ЭтоОбъединение текста из удалению формат. приводим ссылку на оригинал (на английском длине. Формула появится «Главная» программы, панель26

Примеры функции ЗНАЧЕН в Excel и особенности ее использования

строк, столбцов, т.д., числовой формат. Есть

значение «те».

расчетов лучше использовать часть текстовой строкиПримечание: функция ЗНАЧЕН использована данном примере наименование в которой содержится адрес ячейки А1. число заполненных выделенных нескольких диапазонах и/илиДля отображения оригинал (на английском языке) . по всей графе. инструментов «Число» показывает1021

читайте в статье

несколько способов.Примечание 2: в Excel встроенную функцию ОКРГУГЛ слева, количество символов для явного преобразования и количество единиц текст. Получится так. ячеек. Пустые ячейки строки, а такжеИспользуйте код языке) .
Если вам нужно быстроТем, кто знаком с формат выделенного поля.100 «Как изменить ширинуРассмотрим самый простой также существует схожая и подобные ей. в которой соответствуют дат в числа некоторых товаров записаныПримечания:Пятый способ.
так Excel не разделитель, указанный междуПринцип действияПредположим, что нужно создать подсчитать количество строк программированием, поможет преобразоватьСкопируйте единицу. Для этого128 столбца, высоту строки и быстрый способ. по принципу работыФункция ЛЕВСИМВ используется наряду
порядковому номеру символа в коде времени в одной строке.Формат текста, принимаемого вПосчитать, если выделено цветом считает. Но, если каждой парой значений,12 как Продукт №12 предложение грамматически правильные с данными, выберите текст в число сделайте на ней

128

Примеры использования функции ЗНАЧЕН в Excel

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

все ячейки в Excel VBA. Этот

правый клик и15При работе с MS функцией «Найти и

определенное количество символов для работы с выполняет поиск порядкового «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» часть строки кроме ЗНАЧЕН, должен быть. в которой в текст. Если разделительТекст, заключенный в кавычки данных для подготовки первом столбце с встроенный язык предлагает выберите команду «Копировать»211 Excel вы, возможно, заменить». текстовой строки слева текстовыми строками в

номера этого символа

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

Как преобразовать денежный формат в числовое значение

Можно написать такую столбце А нет пустую текстовую строку, (включая пробел) отображается массовой рассылки. Или, этими данными (это команды трансформации типов из меню. Второй211 не раз сталкивалисьВыделяем столбец, столбцы, на основе определенного Excel (ПРАВСИМВ, СИМВОЛ, и возвращает соответствующее

Excel выполняет неявное отображающих числовое значение

в Excel (время, формулу. =СЧЁТ(A1:A6) Эта значений, а есть эта функция будет в ячейке перед возможно, вам нужно не обязательно будет данных. Для целых

способ – кнопка

1021 с ошибкой при в которых нужно количества байтов. Если СЦЕПИТЬ, ДЛСТР и значение). Единица (+1) преобразование. Прямое преобразование

Функция ЗНАЧЕН и формат времени

– количество товаров. дата, число, денежный). формула считает только в столбце В, эффективно объединять диапазоны. числом. В этом форматирование чисел с столбец A). Просто чисел подойдет операция на панели инструментов26 попытке произвести арифметические поменять формат числа. по умолчанию используется прочими) и имеет добавлена для выделения имеет практический смысл С помощью функции Результатом выполнения функции ячейки с числами,

то выделяем ячейку

TEXTJOIN коде «0» обозначает

текстом, не затрагивая щелкните заголовок столбца. CInt, для дробных «Буфер обмена». ИЧто же делать в действия над числами. Как быстро выделить язык с поддержкой следующую синтаксическую запись: всего кода ошибки. при использовании полученных ЗНАЧЕН, принимающей в будет являться ошибка а с текстом столбца В. Главное,

в Excel 2013 число, которое содержится формулы, которые могут

В строке состояния – CDbl. Ниже третий – горячие этом случае и

Программа почему-то не столбец, столбцы, смотрите двухбайтовой кодировки, для

exceltable.com

Примеры использования функции ЛЕВСИМВ в Excel работа с текстом

=ЛЕВСИМВ(текст;[число_знаков])2 – количество символов, значений в других качестве параметра результат #ЗНАЧ!, если исходный – не считает. чтобы были выделены

Как вырезать часть текста из строки функцией ЛЕВСИМВ в Excel?

и более ранние в ячейке (например, использовать эти числа. в нижнем правом показан пример использования клавиши Ctrl+C. как преобразовать текст выполняет вычисления и в статье «Как

получения первого символа

Описание аргументов: которые вернет функция программных продуктах, не работы функции ПРАВСИМ,

текст не соответствуетШестой способ. ячейки всех нужных версии не поддерживается. 12). В Excel существует углу окна Excel

VBA для нашегоВыделите диапазон чисел в в число в выдает сообщение о выделить в Excel строки требуется втекст – ссылка на

ПРАВСИМВ, работающая аналогично

Как вытащить часть текста из ячейки в Excel используя функцию ЛЕВСИМВ?

поддерживающих форматы даты мы производим прямое одному из этихЧтобы посчитать ячейки строк. Например, так.Примеры различных на рисунке12:00 как 12:00 центральноевропейское несколько способов для отобразится количество строк.

случая:

текстовом формате, который Excel?

том, что такую

ячейки, таблицу, др.» качестве аргумента указать ячейку, содержащую текст, рассматриваемой ЛЕВСИМВ (отличие и времени Excel. преобразование выделенных символов типов данных. (строки) по определенномуЗдесь выделили четыре строки. ниже. время объединения текста и
Так же вычисляется количествоSub TestModule() собираетесь приводить кКак уже было указано операцию следует проводить здесь. число 2, а

или текстовая строка,

– возвращает символы

В результате будет получено в числовое значение.На практике функция ЗНАЧЕН условию, используем функциюТретий способВнимательно посмотрите на использованиеч:мм «центральноевропейское время» чисел. столбцов, но вCells(1, 12) = CInt(Cells(1, числовому. На той

выше, на проблему

Округление чисел текстовыми функциями в текстовой строке

над числовыми элементами.На закладке «Главная» для получения последующих в которой требуется справа, а не число в коде

В результате получим следующее: используется довольно редко, «СЧЁТЕСЛИ». Примеры формул

. функции

Текущее время показано вЕсли столбец, который вы

этом случае щелкните

11)) же панели «Буфер указывает зеленая отметка Почему случаются такие в разделе «Редактирование» второго, третьего и
выделить требуемое количество слева строки). времени Excel:То есть, в данной поскольку в Excel смотрите в статье
Можно в таблицетекст формате даты/времени ч:мм хотите отсортировать содержит область выделения строки,End Sub обмена» нажмите «Вставить»
в уголке поля.

проблемы и что нажимаем на кнопку прочих символов необходимо символов слева. АргументВ результате получим:Для получения искомого значения части таблицы представлены реализован механизм автоматического «Функция «СЧЁТЕСЛИ» в фильтром отфильтровать нужныево втором примере AM/PM, а текст

Функция ЛЕВСИМВ в Excel и особенности ее использования

числа и текст которая находится слеваИногда стоит обратная задача / «Специальная вставка». Такая пометка может с ними делать, «Найти и выделить». указывать соответствующие числа,

обязателен для заполнения.

Выделим часть текста ошибки:

необходимо преобразовать формат 265 единиц товаров. преобразования значений подобно Excel» здесь. данные. Затем выделить на рисунке. При «московское время» отображается
— 15 # от строки. – преобразовать в Откроется окно для быть на единичных читайте ниже.

В появившемся диалоговом

кратные двум. Для[число_знаков] – необязательный дляВ данном случае удобнее данных ячейки вПример 2. Данные о некоторым языкам программирования,Седьмой способ
отфильтрованные ячейки в присоединении к числа после времени. продукта, продукт #100Пример результата: Excel число в ввода параметров. В полях, целом диапазоне,Ошибка возникает в случае, окне на закладке
языков с однобайтовой заполнения аргумент; числовое использовать ПРАВСИМВ, которая «Время»: зарплатах сотрудников некоторого
в которых присутствует. столбце. В строке в строку текста-12 как -12р. дефицит и 200 #
Если вы выберете всю текст прописью. Эта нем отметьте точкой столбце или записи. если программа по «Найти» в строке кодировкой функции ЛЕВСИМВ

значение, характеризующее количество является, по сути,То есть, самолет опоздал
предприятия представлены в неявное преобразование типовПосчитать строки, выделенные условным состояния будет видно
с помощью оператор и 12 как продукта — не строку или столбец, проблема так же
элемент «умножить» блока Программа самостоятельно определяет

каким-то причинам не «Найти» ставим запятую. и ЛЕВБ возвращают символов, которые требуется зеркальным отражением ЛЕВСИМВ. на 1 час таблице Excel. Предприятие данных. Эта функция форматированием количество строк. Помним, объединения, используйте функцию 12р. избыток может сортировать должным Excel посчитает только решаема, хоть и «Операция» и нажмите несоответствие между форматом воспринимает числа какПереходим в диалоговом идентичные результаты. выделить из текстовой Для определения второго и 53 минуты. приобрело программный продукт,

exceltable.com

реализована с целью

Источник

Подсчет символов в ячейках

Excel для Microsoft 365 Excel для Интернета Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше

Если необходимо подсчитать символы в ячейках, используйте функцию LEN, которая подсчитывает буквы, цифры, символы и все пробелы. Например, длина фразы «На улице сегодня 25 градусов, я пойду купаться» (не учитывая кавычки) составляет 46 символов: 36 букв, 2 цифры, 7 пробелов и запятая.

Чтобы использовать функцию, введите =LEN(cell) в строке формул, а затем нажмите клавишу ВВОД на клавиатуре.

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

Чтобы получить общее количество всех символов в нескольких ячейках, необходимо использовать функции СУММ вместе с LEN. В этом примере функция LEN подсчитывает символы в каждой ячейке, а функция СУММ добавляет счетчики:

=СУММ((LEN(
cell1
),LEN(
cell2
),(LEN(
cell3
))
))).

Попробуйте попрактиковаться

Ниже приведены некоторые примеры использования функции LEN.

Скопируйте приведенную ниже таблицу и вставьте ее в ячейку A1 на листе Excel. Перетащите формулу из B2 в B4, чтобы увидеть длину текста во всех ячейках столбца A.

Текстовые строки	Формулы
Быстрая бурая лиса.	=ДЛСТР(A2)
Быстрая бурая лиса вскочила.
Съешь же еще этих мягких французских булок, да выпей чаю.

Подсчет символов в одной ячейке

Щелкните ячейку B2.
Введите =LEN(A2).

Формула подсчитывает символы в ячейке A2, которая составляет 27, включая все пробелы и точку в конце предложения.

ПРИМЕЧАНИЕ: LEN подсчитывает пробелы после последнего символа.

Подсчет символов
в
нескольких ячейках

Щелкните ячейку B2.
Нажмите клавиши CTRL+C, чтобы скопировать ячейку B2, затем выделите ячейки B3 и B4, а затем нажмите клавиши CTRL+V, чтобы вставить формулу в ячейки B3:B4.

При этом формула копируется в ячейки B3 и B4, а функция подсчитывает символы в каждой ячейке (20, 27 и 45).

Подсчет общего количества символов

В книге с примерами щелкните ячейку В6.
Введите в ячейке формулу =СУММ(ДЛСТР(A2);ДЛСТР(A3);ДЛСТР(A4)) и нажмите клавишу ВВОД.

Это подсчитывает символы в каждой из трех ячеек и суммирует их (92).

Нужна дополнительная помощь?

Источник

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

Содержание

Как преобразовать текст в число в Эксель?
Зеленый уголок-индикатор
Повторный ввод
Формула преобразования текста в число
Специальная вставка
Инструмент «Текст по столбцам»
Макрос «Текст – число»
Как определить числа, записанные в Экселе, как текст?
Как воспользоваться индикатором ошибок?
Удаление непечатаемых символов
Извлечение числа из текста

Как преобразовать текст в число в Эксель?

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

Зеленый уголок-индикатор

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

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

Повторный ввод

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

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

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

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

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

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

Специальная вставка

Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:

Ввести единицу в любую ячейку, не содержащую никаких значений.
Скопировать ее.
Выделить ячейки с записанными в текстовом формате числами и изменить его на числовой. На этом этапе ничего не изменится, поэтому выполняем дальнейший этап.
Вызвать меню и воспользоваться «Специальной вставкой» или Ctrl + Alt + V.
Откроется окно, в котором нас интересует радиокнопка «значения», «умножить».

3

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

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

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

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

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

Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.

Макрос «Текст – число»

Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.

Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).

Sub Convert_Text_to_Numbers()

Selection.NumberFormat = «General»

Selection.Value = Selection.Value

End Sub

Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас.

Как определить числа, записанные в Экселе, как текст?

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

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

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

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

Как воспользоваться индикатором ошибок?

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

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

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

Чтобы избавиться от этой проблемы, можно воспользоваться формулой =ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))

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

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

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

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

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

Когда нужен подсчет символов в ячейках
- Количество символов в ячейке – формула
Посчитать количество определённых символов в ячейке
- Количество цифр в ячейке
- Количество английских букв в ячейке
- Количество русских букв в ячейке
- Количество любых букв в ячейке
Процедура подсчета символов в ячейках
Посчитать количество символов в нескольких ячейках одновременно

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

Когда нужен подсчет символов в ячейках

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

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

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

Количество символов в ячейке – формула

ДЛСТР — замечательная функция, и с её помощью можно решить уйму задач, связанных с подсчетом символов в ячейках. Вот простейший пример ее применения.

Результат работы формулы

Посчитать количество определённых символов в ячейке

Чтобы посчитать количество определённых символов в ячейке, есть старый как мир лайфхак – достаточно посчитать длину ячейки целиком функцией ДЛСТР, а затем вычесть из неё длину той же ячейки, в которой удалёны эти самые символы. Для удаления используется функция ПОДСТАВИТЬ, третьим аргументом которой делают строковую пустоту, обозначаемую двойными кавычками, между которыми ничего нет.

Например, вот формула для подсчёта количества букв “у” в ячейке A1.

=ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;"у";""))

Подсчет встречаемости буквы “у” в ячейке

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

Количество цифр в ячейке

Подробное руководство в моей исчерпывающей статье “Посчитать цифры в ячейках и ячейки с цифрами“. А я приведу лишь формулу массива оттуда:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(A1;{1;2;3;4;5;6;7;8;9;0};"")))

Есть и другой вариант – с помощью функции СЧЁТ:

=СЧЁТ(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1)+1)

Количество английских букв в ячейке

Английские буквы можно перечислить в массиве явно, как и цифры. Но, поскольку функция ПОДСТАВИТЬ регистрозависимая, чтобы не перечислять все 26*2=52 варианта английских букв, можно добавить в формулу функцию ПРОПИСН:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);{A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z};"")))

Можно не перечислять английские буквы явно, обратившись к соответствующему им диапазону ANSI через функции СИМВОЛ и СТРОКА:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);СИМВОЛ(СТРОКА(ДВССЫЛ("65:90")));"")))

Количество русских букв в ячейке

Проблема с русскими буквами (также известными как кириллица) в том, что буква ё, как строчная, так и заглавная, находится за пределами сплошного диапазона в таблицах ANSI и UNICODE, поэтому нужно либо перечислять русские буквы явно:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);{А;Б;В;Г;Д;Е;Ё;Ж;З;И;Й;К;Л;М;Н;О;П;Р;С;Т;У;Ф;Х;Ц;Ч;Ш;Щ;Ъ;Ы;Ь;Э;Ю;Я};"")))

либо подсчет этой буквы вести отдельно и суммировать с результатом для диапазона из 32 остальных букв. Формула вводится сочетанием Ctrl + Shift + Enter

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);СИМВОЛ(СТРОКА(ДВССЫЛ("192:233")));"")))+ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);"Ё";""))

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

Поэтому рекомендуется либо явное перечисление букв, либо использование вместо СИМВОЛ функции ЮНИСИМВ, где для символов кириллицы “железно” застолбили диапазон 1040-1071:

=СУММ(ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);ЮНИСИМВ(СТРОКА(ДВССЫЛ("1040:1071")));"")))+ДЛСТР(A1)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A1);"Ё";""))

Я все же за явное перечисление букв

Количество любых букв в ячейке

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

=СУММ(НЕ(СОВПАД(ПРОПИСН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1));СТРОЧН(ПСТР(A1;СТРОКА(ДВССЫЛ("1:"&ДЛСТР(A1)));1))))+0)

Формула вводится сочетанием клавиш Ctrl + Shift + Enter и считает количество букв (способных менять регистр) в ячейке A1.

Процедура подсчета символов в ячейках

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

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

Подсчет количества символов в нескольких ячейках процедурой !SEMTools

Посчитать количество символов в нескольких ячейках одновременно

Если нет задачи считать длину содержимого каждой из ячеек, а нужна сразу сумма длин, можно использовать ту же функцию ДЛСТР, но в несколько ином виде — в виде формулы массива:

Формула подсчета суммарной длины содержимого нескольких ячеек в символах

Текст формулы:

Важно понимать, что формула вернет корректный результат, только если обретет фигурные скобки после ввода ее в ячейку сочетанием клавиш Ctrl + Shift + Enter.

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

Источник

Функция ЗНАЧЕН в Excel выполняет операцию преобразования строки в числовое значение в тех случаях, где это возможно. Данная функция возвращает число в случае удачно выполненного процесса преобразования или код ошибки #ЗНАЧ!, если преобразование текста в число невозможно.

Примеры функции ЗНАЧЕН в Excel и особенности ее использования

Данная функция имеет следующую синтаксическую запись:

=ЗНАЧЕН(текст)

Единственный аргумент — текст является обязательным для заполнения. То есть, данная функция принимает на вход текстовые данные, которые могут быть представлены в виде текстовой строки, введенной в кавычках (например, ЗНАЧЕН(“9 300 ₽”) или ссылки на ячейку таблицы, в которой содержится текст.

Примечания:

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

Примеры использования функции ЗНАЧЕН в Excel

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

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

Для расчета общего количества представленных 5 наименований продукции используем следующую формулу:

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

В результате получим следующее:

То есть, в данной части таблицы представлены 265 единиц товаров.

Как преобразовать денежный формат в числовое значение

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

Изначально таблица выглядит следующим образом:

Для конвертирования денежных данных в числовые была использована функция ЗНАЧЕН. Расчет выполняется для каждого сотрудника по-отдельности. Приведем пример использования для сотрудника с фамилией Иванов:

=ЗНАЧЕН(B2)

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

Функция ЗНАЧЕН и формат времени

Пример 3. Самолет вылетел согласно расписанию в 13:40 и должен был совершить посадку в конечном пункте перелета в 17:20. Во время полета произошел инцидент, в связи которым рейс был задержан на определенное время. Самолет приземлился в 19:13. Необходимо определить время задержки в часах и минутах. Данные о времени вылета, расчетного и фактического времени прибытия занесены в ячейки таблицы формата «Дата».

Таблица имеет следующий вид:

Для решения задачи запишем следующую формулу:

Примечание: функция ЗНАЧЕН использована для явного преобразования дат в числа в коде времени Excel. Фактически, записи «=ЗНАЧЕН(C2)-ЗНАЧЕН(B2)» и «C2-B2» являются эквивалентными, поскольку Excel выполняет неявное преобразование. Прямое преобразование имеет практический смысл при использовании полученных значений в других программных продуктах, не поддерживающих форматы даты и времени Excel.

В результате будет получено число в коде времени Excel:

Для получения искомого значения необходимо преобразовать формат данных ячейки в «Время»:

То есть, самолет опоздал на 1 час и 53 минуты.

Источник

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

А если у вас останутся вопросы или захочется что-то добавить – добро пожаловать в комментарии к этой статье. Там можно помочь тысячам других читателей нашего портала WiFiGid.

Содержание

Проблема
Главный способ – Как преобразовать ячейку в число?
Как преобразовать в число весь столбец или всю строку?
Другие способы
Способ 2 – Инструменты
Способ 3 – Сообщение об ошибке
Способ 4 – Формула
Способ 5 – Специальная вставка и другие странности
Видео по теме
Задать вопрос автору статьи

Проблема

Суть проблемы: обычно при вставке чего-то в лист Excel данные превращаются в текст (даже если изначально это было числом). Если затем эту ячейку обернуть в какую-то формулу, то формула выдаст ошибку, так как предполагалось, что на вход все-таки будет подано число. Решение тут простое – нужно просто преобразовать эти ячейки в число, чем мы и займёмся ниже.

А еще бонус для различения «на глаз» чисел и текст: по умолчанию они имеют разное выравнивание (числа вправо, а текст влево):

Главный способ – Как преобразовать ячейку в число?

Выделяем эту ячейку (а можно сразу и несколько ячеек).
Щелкаем по выделенным ячейкам правой кнопкой мыши и выбираем пункт «Формат ячеек».

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

Все! С этого момента ячейка является числом и ее можно включать в любые формулы.

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

Как преобразовать в число весь столбец или всю строку?

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

Щелкните по заголовку столбца (A, B, C…) – выделится целый столбец. А если зажать кнопку Ctrl, то можно выделить несколько столбцов.
Щелкните по заголовку строки (1, 2, 3…) – выделится целая строка. А если зажать кнопку Ctrl, то можно выделить несколько строк.

Получилось выделить? А дальше все по накатанной как было описано разделом выше – щелкнули правой кнопкой мыши, «Формат ячеек», выбрали «Числовой». И весь ваш столбец или строка с этого момента является числовым.

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

Другие способы

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

Способ 2 – Инструменты

Все инструменты в Excel дублируются. Разумеется, они просто обязаны быть в ленте инструментов. На «Главной» вкладке можно сразу найти выбор формата (ориентируемся на слово «Общий», а там раскрываем список и находим «Числовой»).

Способ 3 – Сообщение об ошибке

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

Способ 4 – Формула

Для преобразования чисел в текст в Excel используется формула ТЕКСТ, а вот обратной функции я чего-то не нашел. Но можно проделать трюк с двойным отрицание. Если поставить два минуса в формуле перед вашей ячейкой, то, по сути, произойдет двойное умножение на -1 этой ячейки, что в конечном итоге даст то же самое число. Еще этот способ можно как раз назвать умножением на число – в этом случае текст сам преобразуется в число. Итого конечная формула у вас может выглядеть примерно так (ячейку используем свою):

=–А2

Способ 5 – Специальная вставка и другие странности

А еще в Excel уже на этапе вставки можно выбрать формат вставляемого значения. Для вставки чисел щелкаем правой кнопкой мыши по стартовой для вставки ячейке и выбираем «Специальная вставка», а из того меню еще раз «Специальная вставка» и ставим операцию «Умножить»… А нужно ли это вам? Лично я думаю, что метод бесполезный, но раз его пишут все, то тут тоже для умного словца черкану, а там уже думайте сами с этими экспериментами. Еще раз – главный способ является самым полезным из всех.

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

Видео по теме

Источник

Содержание

Конвертация числа в текстовый вид
- Способ 1: форматирование через контекстное меню
- Способ 2: инструменты на ленте
- Способ 3: использование функции
Конвертация текста в число
- Способ 1: преобразование с помощью значка об ошибке
- Способ 2: конвертация при помощи окна форматирования
- Способ 3: конвертация посредством инструментов на ленте
- Способ 4: применение формулы
- Способ 5: применение специальной вставки
- Способ 6: использование инструмента «Текст столбцами»
- Способ 7: применение макросов
Вопросы и ответы

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

Конвертация числа в текстовый вид

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

Для начала рассмотрим различные варианты решения задачи конвертации чисел в текстовый вид.

Способ 1: форматирование через контекстное меню

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

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

Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».

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

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

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

Чтобы завершить преобразование, нам нужно последовательно двойным щелчком левой кнопки мыши поместить курсор в каждый элемент диапазона в отдельности и нажать на клавишу Enter. Чтобы упростить задачу вместо двойного щелчка можно использовать нажатие функциональной клавиши F2.

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

Урок: Как изменить формат в Excel

Способ 2: инструменты на ленте

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

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

В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».

Далее, как и в предыдущем способе, последовательно устанавливаем курсор в каждый элемент диапазона двойным щелчком левой кнопки мыши или нажатием клавиши F2, а затем щелкаем по клавише Enter.

Данные преобразовываются в текстовый вариант.

Способ 3: использование функции

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

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

Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».

Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:
=ТЕКСТ(значение;формат)

Открывшееся окно имеет два поля, которые соответствуют данным аргументам: «Значение» и «Формат».

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

В поле «Формат» нужно указать вариант отображения результата. Например, если мы введем «0», то текстовый вариант на выходе будет отображаться без десятичных знаков, даже если в исходнике они были. Если мы внесем «0,0», то результат будет отображаться с одним десятичным знаком, если «0,00», то с двумя, и т.д.

После того, как все требуемые параметры введены, щелкаем по кнопке «OK».

Как видим, значение первого элемента заданного диапазона отобразилось в ячейке, которую мы выделили ещё в первом пункте данного руководства. Для того, чтобы перенести и другие значения, нужно скопировать формулу в смежные элементы листа. Устанавливаем курсор в нижний правый угол элемента, который содержит формулу. Курсор преобразуется в маркер заполнения, имеющий вид небольшого крестика. Зажимаем левую кнопку мыши и протаскиваем по пустым ячейкам параллельно диапазону, в котором находятся исходные данные.

Теперь весь ряд заполнен требуемыми данными. Но и это ещё не все. По сути, все элементы нового диапазона содержат в себе формулы. Выделяем эту область и жмем на значок «Копировать», который расположен во вкладке «Главная» на ленте инструментов группе «Буфер обмена».

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

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

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

На этом процедуру преобразования можно считать оконченной.

Урок: Мастер функций в Excel

Конвертация текста в число

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

Способ 1: преобразование с помощью значка об ошибке

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

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

Открывается список действий. Выбираем в нем значение «Преобразовать в число».

В выделенном элементе данные тут же будут преобразованы в числовой вид.

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

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

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

Все данные массива будут преобразованы в указанный вид.

Способ 2: конвертация при помощи окна форматирования

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

Выделяем диапазон, содержащий цифры в текстовом варианте. Кликаем правой кнопкой мыши. В контекстном меню выбираем позицию «Формат ячеек…».

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

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

После выполнения этих действий все значения выбранного диапазона преобразуются в нужный нам вид.

Способ 3: конвертация посредством инструментов на ленте

Перевести текстовые данные в числовые можно, воспользовавшись специальным полем на ленте инструментов.

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

Далее прощелкиваем уже не раз описанным нами способом каждую ячейку преобразуемой области с применением клавиш F2 и Enter.

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

Способ 4: применение формулы

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

В пустой ячейке, расположенной параллельно первому элементу диапазона, который следует преобразовать, ставим знак «равно» (=) и двойной символ «минус» (—). Далее указываем адрес первого элемента трансформируемого диапазона. Таким образом, происходит двойное умножение на значение «-1». Как известно, умножение «минус» на «минус» дает «плюс». То есть, в целевой ячейке мы получаем то же значение, которое было изначально, но уже в числовом виде. Даная процедура называется двойным бинарным отрицанием.

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

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

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

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

Кстати, для преобразования значений данным методом совсем не обязательно использовать исключительно двойное умножение на «-1». Можно применять любое другое арифметическое действие, которое не ведет к изменению значений (сложение или вычитание нуля, выполнение возведения в первую степень и т.д.)

Урок: Как сделать автозаполнение в Excel

Способ 5: применение специальной вставки

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

В любую пустую ячейку на листе вписываем цифру «1». Затем выделяем её и жмем на знакомый значок «Копировать» на ленте.

Выделяем область на листе, которую следует преобразовать. Кликаем по ней правой кнопкой мыши. В открывшемся меню дважды переходим по пункту «Специальная вставка».

В окне специальной вставки выставляем переключатель в блоке «Операция» в позицию «Умножить». Вслед за этим жмем на кнопку «OK».

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

Способ 6: использование инструмента «Текст столбцами»

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

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

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

Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».

А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».

Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».

Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».

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

Способ 7: применение макросов

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

Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».

Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:
Sub Текст_в_число() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub

После этого закрываем редактор, выполнив нажатие стандартной кнопки закрытия в верхнем правом углу окна.

Выделяем фрагмент на листе, который нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код».

Открывается окно записанных в вашей версии программы макросов. Находим макрос с наименованием «Текст_в_число», выделяем его и жмем на кнопку «Выполнить».

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

Урок: Как создать макрос в Экселе

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

Источник