Как преобразовать текст в число в Excel (эксель): ЗНАЧЕН

=ЗНАЧЕН(A2) превращает число, сохранённое как текст в A2, в настоящее число. Это функция ЗНАЧЕН (по-английски VALUE). Текстовые числа выглядят обычно, но СУММ, СРЗНАЧ и СЧЁТ их пропускают, поэтому сумма может получиться равной 0. В таблицах формулы записаны по-английски, но их можно вводить и по-русски, с точкой с запятой: =СУММПРОИЗВ(--A2:A5).

ФормулыСбросить

C2fx

=VALUE(A2)

	A	B	C
1	Imported		VALUE
2	120		120
3	85		85
4	240		240
5	15		15
6	0		460

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЗНАЧЕН(A2)

A6 даёт 0, потому что четыре ячейки в столбце A текстовые (введены с апострофом, как часто бывает с данными, импортированными из CSV или с веб-страницы). Столбец C преобразует каждую, и C6 даёт настоящую сумму, 460.

Как понять, что число сохранено как текст

В Excel число, сохранённое как текст:

• стоит у левого края ячейки, тогда как числа стоят у правого (если выравнивание не меняли);
• отмечено маленьким зелёным треугольником в левом верхнем углу ячейки, а при выделении появляется значок предупреждения с надписью «Число сохранено как текст»;
• учитывается функцией СЧЁТЗ, но не функцией СЧЁТ.

ФормулыСбросить

B2fx

=ISNUMBER(A2)

	A	B	C	D	E
1	Value	ISNUMBER	ISTEXT		Text numbers
2	120	TRUE	FALSE		2
3	85	FALSE	TRUE
4	240	TRUE	FALSE
5	15	FALSE	TRUE

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЕЧИСЛО(A2)

E2 считает, сколько ячеек диапазона заполнено, но не являются числами: здесь две, введённые с апострофом. На чистом столбце чисел формула возвращает 0.

Четыре формулы для преобразования

ФормулыСбросить

B2fx

=VALUE(A2)

	A	B	C
1	Text	Result	How
2	1250	1250	VALUE
3		1250	double minus
4		1250	multiply by 1
5		1250	add 0

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЗНАЧЕН(A2)

Любая арифметика заставляет Excel прочитать текст как число, а ЗНАЧЕН это явный вариант. -- (два минуса: минус и ещё раз минус) обычно выбирают внутри других формул, потому что это коротко: =СУММПРОИЗВ(--A2:A5) складывает столбец текстовых чисел без вспомогательного столбца. ЗНАЧЕН читает и текст со знаком валюты, разделителями тысяч или знаком процента: в английской форме VALUE("$1,250") даёт 1250, а VALUE("12%") даёт 0.12.

ФормулыСбросить

B2fx

	A	B
1	Quantity (text)	Quantity
2	125

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

Ваша очередь: Количество в A2 импортировано как текст. В B2 преобразуйте его в число.

ПроверитьПоказать ответ

Текст с единицами измерения или другими разделителями

ЗНАЧЕН возвращает #ЗНАЧ! (по-английски #VALUE!), если в тексте есть что-то, что нельзя прочитать как число. Таблицы на этой странице показывают ошибки под английскими именами. Два частых случая:

ФормулыСбросить

B2fx

=VALUE(SUBSTITUTE(A2," kg",""))

	A	B	C
1	Text	Fixed	Without the fix
2	120 kg	120	#VALUE!
3	1.234,5	1234.5	#VALUE!

Нажмите на ячейку, чтобы увидеть её формулу. Измените число или формулу, и таблица пересчитается.В русском Excel: =ЗНАЧЕН(ПОДСТАВИТЬ(A2;" kg";""))

• Единица измерения или слово: сначала удалите их функцией ПОДСТАВИТЬ, как в B2.
• Запятая как десятичный разделитель, как в 1.234,5 из немецкой или бразильской системы: ЧЗНАЧ (NUMBERVALUE, Excel 2013 и новее) принимает десятичный разделитель и разделитель групп разрядов вторым и третьим аргументами. ЗНАЧЕН знает только разделители вашего Excel, поэтому C3 не срабатывает.

Обычные пробелы до или после цифр ЗНАЧЕН не мешают, а неразрывные пробелы с веб-страниц могут помешать; ПОДСТАВИТЬ(A2;СИМВОЛ(160);"") сначала их удаляет. Общие причины этой ошибки описаны на странице #ЗНАЧ!.

ФормулыСбросить

B2fx

	A	B
1	Amount	Total
2	120
3	45
4	80

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

Ваша очередь: Суммы в A2:A4 это числа, сохранённые как текст. В B2 верните их сумму одной формулой.

ПроверитьПоказать ответ

Преобразовать на месте без формулы

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

1. Преобразовать в число. Выделите ячейки (первая выделенная ячейка должна быть с зелёным треугольником), нажмите значок предупреждения рядом с выделением и выберите «Преобразовать в число». Это самый быстрый способ.
2. Текст по столбцам. Выделите столбец, выберите Данные > Текст по столбцам и сразу нажмите «Готово». Excel заново введёт каждую ячейку и превратит текстовые числа в числа.
3. Специальная вставка, умножить. Введите 1 в пустой ячейке и скопируйте её. Выделите текстовые числа, выберите Главная > Вставить > Специальная вставка, отметьте «умножить» и нажмите «ОК».

Если у ячеек формат «Текстовый» (Главная > Числовой формат показывает «Текстовый»), сначала поставьте «Общий»; иначе всё, что вы в них введёте, снова сохранится как текст.

Частая ошибка: поиск между текстом и числами

Искомое значение 101 не совпадает с текстом 101: ВПР, ПРОСМОТРX и ПОИСКПОЗ возвращают #Н/Д (по-английски #N/A), а =A2=101 даёт ЛОЖЬ, хотя обе ячейки выглядят одинаково. Преобразуйте одну из сторон, чтобы типы совпадали. Если текстовый именно столбец поиска, преобразуйте искомое значение:

=VLOOKUP(TEXT(E2,"0"), A2:C6, 3, FALSE)   E2 is a number, column A holds text numbers
=VLOOKUP(--E2, A2:C6, 3, FALSE)           E2 holds a text number, column A holds numbers

В русском Excel: =ВПР(ТЕКСТ(E2;"0");A2:C6;3;ЛОЖЬ) и =ВПР(--E2;A2:C6;3;ЛОЖЬ). На странице о ЕПУСТО и ЕЧИСЛО показано, как проверить каждую сторону.