Меню

Число в этой ячейке отформатировано как текст как сделать

Форматирование чисел в виде текста

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

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

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

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

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

Примечание: Если вы не видите параметр «Текст», прокрутите список до конца с помощью крутки.

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

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

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

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

Источник

Преобразование чисел-как-текст в нормальные числа

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

Причем иногда такой индикатор не появляется (что гораздо хуже).

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

Способ 1. Зеленый уголок-индикатор

Если на ячейке с числом с текстовом формате вы видите зеленый уголок-индикатор, то считайте, что вам повезло. Можно просто выделить все ячейки с данными и нажать на всплывающий желтый значок с восклицательным знаком, а затем выбрать команду Преобразовать в число (Convert to number) :

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

Способ 2. Повторный ввод

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

Само-собой, что если ячеек много, то такой способ, конечно, не подойдет.

Способ 3. Формула

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

Читайте также:  Как сделать покрытие гель лаком кошачий глаз с магнитом

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

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

Этот способ использовали еще в старых версиях Excel, когда современные эффективные менеджеры под стол ходили зеленого уголка-индикатора еще не было в принципе (он появился только с 2003 года). Алгоритм такой:

Способ 5. Текст по столбцам

После нажатия на Готово Excel преобразует наш текст в нормальные числа.

Способ 6. Макрос

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

Источник

Вводим в ячейку число как текст

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

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

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

Вводить числа как текст в Excel приходится достаточно часто. Например:

Поэтому важно научиться управлять форматами ячеек.

Попробуйте заполнить табличку, так как показано на рисунке:

Как видно на примере проблема возникает при введении текста «10/12». После нажатия клавиши «Enter» получаем дату:

Необходимо заставить Excel распознавать числа как текст.

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

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

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

Теперь обратите внимание на результаты вычислений функций и формул в колонке D.

В ячейке D2 функция =СУММ(A2:C2) отображает правильный результат 1+100+10=111. А в ячейке D3 функция суммирования меняет значение ячейки B3 на число 0, так как там данные отображаются в текстовом формате. В результате: 1+0+10=11.

Внимание! При вычислении аналогичных значений ячеек, но уже с помощью обычной формулы =A6+B6+C6 все значения ячеек воспринимаются как числовые значения, несмотря на то, что ячейка B6 имеет текстовый формат. Формулы могут суммировать, умножать, разделять и вычитать текст, если он состоит только из символов чисел (0-9), а функции не могут.

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

Источник

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

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

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

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

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

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

Число

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

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

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

Читайте также:  Как сделать чтобы верхняя панель не исчезала в браузере

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

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

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

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

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

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

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

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

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

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

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

Вот что вы делаете:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Как сделать чтобы экран не потухал на андроид

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

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

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

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

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

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

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

Источник

Формула форматирует число суммы как текст в одной ячейке Excel

Часто в отчетах Excel необходимо объединять текст с числами. Проблема заключается в том, что вовремя объединения текста с числом нельзя сохранить числовой формат данных ячейки. Число в ячейке отформатировано как текст.

Отформатировать число как текст в Excel с денежным форматом ячейки

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

Обратите внимание, что каждое число после объединения с текстом (в столбце D) не сохраняет свой денежный формат, который определен в исходной ячейке (столбца B).

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

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

Функция ТЕКСТ требует 2 обязательных для заполнения аргумента:

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

Например, ниже заполненная аргументами функция ТЕКСТ возвращает число в денежном формате пересчитанному по курсу 68 руб./1$:

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

Простой способ проверки синтаксиса кода формата, который распознает Excel – это использование окна «Формат ячеек». Для этого:

Функция РУБЛЬ для форматирования числа как текст в одной ячейке

Более упрощенным альтернативным решением для данной задачи может послужить функция РУБЛЬ, которая преобразует любое число в текст и отображает его в денежном формате:

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

Функция РУБЛЬ требует для заполнения только 2 аргумента:

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

Если в исходном столбце содержаться значения в ячейках с разным форматом валют тогда нам потребуется распознать все форматы в каждой ячейке. По умолчанию в Excel нет стандартной функции, которая умеет распознавать и возвращать форматы ячеек. Поэтому напишем свою пользовательскую макрофункцию и добавим ее в нашу формулу. Макрофункция будет называться ВЗЯТЬФОРМАТ (или можете назвать ее по-своему). Исходный VBA-код макрофункции выглядит так:

Public Function ВЗЯТЬФОРМАТ(val As Range) As String
Dim money As String
money = WorksheetFunction.Text(val, val.NumberFormat)
ВЗЯТЬФОРМАТ = Replace(money, Application.ThousandsSeparator, » » )
End Function

Скопируйте его в модуль («Insert»-«Module») VBA-редактора (ALT+F11). При необходимости прочитайте:

Теперь изменяем нашу формулу и получаем максимально эффективный результат:

Благодаря функции ВЗЯТЬФОРМАТ написанной на VBA-макросе мы просто берем значение и формат из исходной ячейки и подставляем его как текстовую строку. Так наша формула с пользовательской макрофункцией ВЗЯТЬФОРМАТ автоматически определяет валюту для каждой суммы в исходных значениях ячеек.

Источник

Adblock
detector