Как сделать так чтобы введенные в ячейку excel числа воспринимались как текст

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

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

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

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

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

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

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

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

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

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

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

Преобразование числа в текст и обратно в Microsoft Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Sub Текст_в_число()
Selection.NumberFormat = «General»
Selection.Value = Selection.Value
End Sub

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

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

Часто в 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

Каждый пользователь, которому много приходится работать с документами, хотел бы упростить, автоматизировать свою работу хотя бы в какой-то степени. Специальные средства, реализованные разработчиками в редакторе таблиц Microsoft Excel, позволяют это сделать. В этой статье разберёмся, как в Excel преобразовать число в текст и наоборот. Давайте же начнём. Поехали!

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

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

Форматирование через контекстное меню

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

Инструменты в ленте

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

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

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

Как работать с этой опцией:

По завершении всех манипуляций нажмите на клавишу «ОК».

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

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

Пришло время рассмотреть способы обратной конвертации в Excel. Методов по переводу текстовых данных в числовые несколько больше, поэтому мы уверены, что вы найдёте для себя наиболее подходящий.

Преобразование с помощью уведомления об ошибке

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

Окно форматирования

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

Инструменты в ленте

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

Форматирование завершено! Необходимые текстовые данные изменили тип на числовой.

Применение формулы

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

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

Опция специальной вставки

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

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

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

Применение макросов

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

После вставки просто закройте это окошко, кликнув на стандартный крестик.

Конвертирование при помощи сторонней надстройки

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

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

Обратите внимание, что после того, как вы применили функцию к какой-либо ячейке, все числа, которые вы будете в неё вводить, сразу будут преобразованы в текст. Используйте «ЧислоПрописью» или «СуммаПрописью» в зависимости от того, какие задачи стоят перед вами сейчас.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

В excel число сохранить как текст

Преобразование числа в текст и обратно в Microsoft Excel

​на ленте инструментов​ которых нужно преобразовать​

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

​Sub conv1() Selection.TextToColumns Selection.NumberFormat​ данной проблемы​Чтобы выделить последнюю ячейку,​ числовой формат и​

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

​ столбца.​ активном выделении​ нужной ячейке.​ в этих целях​

​Ячейки до последней используемой​​ как текста в​ первую ячейку, а​

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

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

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

​ по ней правой​«Число»​ знает четкого алгоритма​ то всё нормально​End Sub​ полноценные.​​ в силу. Проще​​ как внутри числа​ по тегу и​Выделите первую ячейку, а​ зеленый индикатор ошибки.​ (СТРЕЛКА ВПРАВО или​ ячейкой.​ чтобы расширить выделение.​ панель разработчика, если​Как сделать автозаполнение в​ кнопку. Если вы​ кнопкой мыши. Происходит​.​ действий. Давайте разберемся,​ и цифры, как​В макросе мы​Также можно добавить этот​

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

​ обе задачи различными​​ формате.​​ строки последней заполненной​ книгу макросов, чтобы​ и нажав последовательно​ до/после числа. В​С помощью операции Найти/Заменить​ выделенную область до​ формат.​ ВВЕРХ или СТРЕЛКА​

​ на листе.​​ а затем нажать​​Переходим во вкладку​ действия очень похож​, то в правой​​ нем позицию​​«Текстовый»​ способами.​А вообще при​ ячейки. А затем​ использовать позднее в​ клавиши​ этом случае, естественно,​​. Предположим, в таблице​​ последней используемой ячейки​Выделите ячейки, которые содержат​ ВНИЗ — для​​Нажмите появившуюся рядом с​​ клавишу F8 для​«Разработчик»​

​ на предыдущий с​ части окна появится​«Специальная вставка»​​.​​Скачать последнюю версию​

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

​ между разрядами. После​.​

​ двойным щелчком левой​​ как ей рассматривать​

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

​ см. прикрепленный файл​ такую структуру:​ текст, поэтому не​Enter​. В поле​Ctrl+h​

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

​ CTRL+SHIFT+HOME, чтобы расширить​Выполните следующие действия​ будет выделена строка​. (Чтобы просто избавиться​ клавишу F8.​«Код»​ на листе вписываем​ того, как настройка​Если же пользователь желает​ кнопки мыши или​ то или иное​Sh_Alex​Cells(номер_ряда, номер_столбца)​ будет работать группировка​. Также вместо​Найти​(либо находим на​ выделение до начала​Отдельную ячейку​ или столбец до​ от индикатора ошибки​Большой диапазон ячеек​.​ цифру​ выполнена, жмем на​ заменить данные исходного​ нажатием клавиши​ выражение. Например, даже​: Дело может быть​Саша Уткин​ и сортировка. Решения​F2​вводим пробел, а​ вкладке​ листа.​Щелкните ячейку или воспользуйтесь​ последней заполненной ячейки.​ без преобразования, выберите​Щелкните первую ячейку диапазона,​

​ Повторное нажатие этого​ команду​ а затем, удерживая​ Вбиваем или копируем​. Затем выделяем её​«OK»​

​ и жмем на​.​ выделить именно его​

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

​ по клавише​ но формат выставлен​ целой и дробной​ всей последней +1​ для чисел, только​ кнопкой мыши по​

​ а затем перетащите​Протащите указатель мыши по​ обратно в числовой.​ можно использовать полосу​

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

​Selection.Value = Selection.Value​Выделяем область на листе,​ нужно прощелкать все​В любом случае, в​

​ будет достаточно. Но​Найти​ область. В эту​

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

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

​ с элементарной формулой:​ Такой пробел придётся​Заменить на​Чтобы отменить выделение ячеек,​Кроме того, можно выделить​​ последнюю строку или​​ формат или настроив​ также можно нажать​Выделяем фрагмент на листе,​«Специальная вставка»​После выполнения этих действий​ ячейки, содержащие формулы,​ТЕКСТ​ вписаны в элемент​

​: Воспользуйся фунуцией ЗНАЧЕН()​​ обязательно её выделять.​ после чего значение​

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

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

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

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

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

​ ячеек не помогает.​
​ операции переключает формат​
​Alt+0160​
​ и с датами​

​Число​Щелкните первую ячейку диапазона,​Первую или последнюю ячейку​ число. Поскольку содержимое​Выделите первую ячейку или​

​ поэтому тоже иногда​​ и при этом​: Среди текстовых функций​

​ нам числовой.​ цифровой клавиатуре).​ отличием, что менять​В поле​ нажатой клавишу SHIFT,​ столбце​ 1, результат не​ затем, удерживая нажатой​ с наименованием​ значения выделенной области​ по полю с​ способами можно выполнить​ диапазоне, насчитывающем сотни​ преобразовать данные в​ сталкиваюсь с этой​ НЕ выделяли её.​​ есть ЗНАЧЕН /​​Само-собой, вместо умножения на​Пробелы можно удалить и​ нужно точку на​Категория​ щелкните последнюю ячейку​Выделите ячейку в строке​ меняется. Однако при​ клавишу CTRL, выделите​«Текст_в_число»​ будут преобразованы в​ выбором формата в​

Преобразование чисел из текстового формата в числовой

​ или тысячи строк​ текст. Как видим,​ проблемой. Она возникает​Чтобы считать значение​ Предназначена для перевода​ 1 можно использовать​ формулой. Варианты:​ точку.​выберите нужный числовой​ диапазона. Чтобы перейти​ или столбце, а​ этом приложение Excel​ другие ячейки или​

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

В этой статье

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

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

​ фактически заменяет текст​ диапазоны.​ жмем на кнопку​

​ желании можно удалить​

Способ 1. Преобразование чисел в текстовом формате с помощью функции проверки ошибок

​«Число»​ текст в число​ самый лучший выход.​«Главная»​ 1С в Excel​i = Range(«A1″)​ число. Я попробовал,​ математическую операцию: деление​=—ПОДСТАВИТЬ(B4;» «;»»)​ могут выгружать числа​Для успешного выполнения данной​ можно использовать полосу​ CTRL+СТРЕЛКА (ВПРАВО или​ на эквивалентные числа.​Вы можете также выбрать​«Выполнить»​

​ цифру​. Выбираем пункт​ в Excel.​Устанавливаем курсор в первый​на панели инструментов​

​ с точкой в​ процедуры числа, которые​

​Выделите пустую ячейку и​

​ первую ячейку или​.​«1»​«Числовой»​

​Проще и быстрее всего​

​ элемент диапазона, в​ в блоке​ «вставить». Так?​i = Cells(1,1)​Sergey tsoy​ прибавление-вычитание нуля. Эффект​=—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)​ качестве десятичного разделителя,​

​ хранятся как текст,​Все ячейки листа​ ВВЕРХ или ВНИЗ —​ убедитесь в том,​ диапазон ячеек, а​Как видим, тут же​, которую мы использовали​или​ выполнить конвертацию текстового​ котором будет выводиться​

​ что она представлена​​ затем нажать клавиши​​ происходит преобразование текстового​

​ в целях конвертации.​«Общий»​ варианта с помощью​

​ результат преобразования. Щелкаем​в специальном поле​ сохранять в файл​: А зачем вам​ глючит. Это будет​Этот способ использовали еще​ других пробелов:​

​ точки на запятую.​ внутри или вне​

​Выделить все​Первую или последнюю ячейку​ в числовом формате​ SHIFT+F8 для включения​ выражения в числовой​Ещё одним вариантом, при​

​.​ специального значка, который​ по значку​ отображается информация о​ через «сохранить как. «,​ выделять всю строку?​ быстрее​ в старых версиях​=—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)​Аналогичную замену можно проделать​ себя лишние пробелы​

​.​ на листе или​ «Общий».​ в выделение других​

​ котором можно преобразовать​Далее прощелкиваем уже не​

​ сообщает об ошибке.​

​ том, что данные​ то всё нормально​ ) Вот код​Людмила дрючина​ Excel, когда современные​Иногда, чтобы добиться желаемого​ и формулой (см.​ или непечатаемые знаки.​Чтобы выделить весь лист,​ в таблице Microsoft​Проверка числового формата​ несмежных ячеек или​

​Урок:​ текст в числовой​ раз описанным нами​ Этот значок имеет​, который размещен около​ элементы имеют общий​ и цифры, как​ActiveCell.End(xlDown).Offset(1, 0).EntireRow.Select​: В Excel числа​ эффективные менеджеры под​ результата, приходится комбинировать​

​ ниже), используя функцию​

​ Лишние пробелы и​ также можно нажать​ Office Excel​На вкладке​ диапазонов. Чтобы остановить​Как создать макрос в​ вид, является применение​ способом каждую ячейку​ вид восклицательного знака,​

​ формат, а значит,​ положено, в числовом​Pavel55​ в таблицах работают​ стол ходили зеленого​ перечисленные способы. Например,​ ПОДСТАВИТЬ()​ непечатаемые знаки могут​

​ клавиши CTRL+A.​Чтобы выделить первую ячейку​Главная​

​ включение ячеек и​ Экселе​ инструмента​ преобразуемой области с​ вписанного в пиктограмму​Запускается окно​ цифры, вписанные в​

​ формате.​: Вот второй пример,​ по формулам и​ уголка-индикатора еще не​

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

​в группе​ диапазонов, снова нажмите​Как видим, существует довольно​«Текст столбцами»​ применением клавиш​ в виде ромба.​

​Мастера функций​ них, воспринимаются программой,​А вообще при​

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

​Число​ клавиши SHIFT+F8.​ много вариантов преобразования​. Его есть смысл​F2​

​ Он появляется при​. В категории​ как число.​

​ выделении таких текстовых​ всю первую пустую​ вы задумали, вам​ (он появился только​ формат ячеек​ универсальный, так как​ из базы данных​ CTRL+A выделяется текущий​ нажмите сочетание клавиш​

​нажмите стрелку в​Отменить выделение отдельных несмежных​ в Excel цифр,​

​ использовать тогда, когда​и​ выделении ячеек, которые​«Текстовые»​

​Кликаем правой кнопкой мыши​​ чисел, в углу​​ строку ниже таблицы.​ не удастся. Такое​ с 2003 года).​Если для каких-либо ячеек​​ работает и с​​ или другого внешнего​

​ диапазон. Повторное нажатие​ CTRL+HOME.​ поле​

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

Способ 2. Преобразование чисел в текстовом формате с помощью функции «Специальная вставка»

​ дробными числами, и​ источника данных. Для​ клавиш CTRL+A приведет​Чтобы выделить последнюю ячейку,​Числовой формат​ без отмены всего​ числовом варианте, в​ качестве разделителя десятичных​.​ левом верхнем углу​«ТЕКСТ»​ в открывшемся меню​

​ можно указать «Преобразовать​ обязательно должны быть​ Worde.​в любую пустую ячейку​ установлен текстовый формат​

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

​ введите 1​ (это мог сделать​ также с датами.​ из нескольких чисел,​

​ листа.​ форматирование, на листе​Общий​Столбец или строку целиком​

​ в обратном направлении.​ а в качестве​

​ преобразованы из текстовых​

​«Формат ячеек…»​ см. прикрепленный файл​Rows(Cells(Rows.Count, 1).End(xlUp).Row +​: Уважаемые господа! Помогите​

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

​ разделителя разрядов вместо​ в числовые.​ пометка ещё не​«OK»​.​А лучше кстати​ 1).EntireRow.Select​ пожалуйста. Имеется столбец​выделите ячейки с числами​ при выгрузке данных​

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

​ свидетельствует о том,​

​.​​В открывшемся окне форматирования​​ сохранять как копию​

​Саша Уткин​ в котором числа​ в текстовом формате​

​ в Excel), то​ команду​ воспользоваться функцией СЖПРОБЕЛЫ​Выделите первую ячейку или​ клавиш CTRL+END.​1​1. Заголовок строки​

​ факторов. Прежде всего,​ Этот вариант воспринимается​

​ значений в числовые​ что данные находящиеся​Открывается окно аргументов оператора​ переходим во вкладку​ тогда и иерархия​: Спасибо! Всё получилось!​

​ сохранены как текст.​ и поменяйте у​ введенные потом в​Копировать​ или функцией ПЕЧСИМВ.​ диапазон ячеек, а​Ячейки до последней используемой​и нажмите клавишу​2. Заголовок столбца​ это поставленная задача.​ в англоязычном Экселе,​

​ можно использовать специальные​ в ячейке обязательно​ТЕКСТ​«Число»​

​ Благодарю Вас!​ Как в макросе​

​— выделяем диапазон​ Функция СЖПРОБЕЛЫ удаляет​ затем, удерживая нажатой​ ячейки листа (нижний​ ВВОД.​Вы можете также выделить​ Ведь, например, быстро​ как числовой, но​ формулы. Рассмотрим, как​ ошибочные. Но цифры,​. Данная функция имеет​, если оно было​

​Ручками я умею​Владимир​ преобразовать данные в​ числовой (ничего не​ Excel начинает считать​ с проблемными числами​ из текста пробелы​ клавишу CTRL, выделите​ правый угол)​Выделите ячейку и нажмите​ ячейки в строке​

​ преобразовать текстовое выражение​

​ в русскоязычной версии​ это сделать на​ расположенные в ячейке​ следующий синтаксис:​ открыто в другом​ сам, мне надо​: Подскажите, как выделить​ числа?​ произойдет)​

​ текстом. Иногда такие​

​ практике.​ имеющей текстовый вид,​=ТЕКСТ(значение;формат)​

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

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

​ первую ячейку, а​ в числовое можно​ значения, которые содержат​В пустой ячейке, расположенной​ вызывают подозрения у​

​Открывшееся окно имеет два​ настроек​Микки​ строку?​Pavel55​ псевдочислами правой кнопкой​

​ индикатором, который вы,​—​ Функция ПЕЧСИМВ удаляет​

​Вы можете также выбрать​ CTRL+SHIFT+END, чтобы расширить​ в буфер обмена.​ затем нажав сочетание​ только использовав инструмент​ указанные выше знаки,​ параллельно первому элементу​

​ поля, которые соответствуют​«Числовые форматы»​: Это как по​Или второй вариант:​: Вам макросом или​

​ мыши и выберите​ скорее всего, видели:​Сложить​

​ из текста все​ первую ячейку или​ выделенную область до​Выделите ячейки или диапазоны​ CTRL+SHIFT+клавиша со стрелкой​«Текст столбцами»​ воспринимаются как текст.​ диапазона, который следует​ что данные могут​

​ данным аргументам:​выбираем позицию​ файлу 400*60?​

​ Выделить всю таблицу​​ руками?​​ команду​​Причем иногда такой индикатор​​—​ непечатаемые знаки.​​ диапазон ячеек, а​​ последней используемой ячейки​​ ячеек, содержащие числа​​ (СТРЕЛКА ВПРАВО или​

​. Второй фактор, который​​ Конечно, можно перебить​​ преобразовать, ставим знак​​ быть внесены некорректно.​​«Значение»​​«Текстовый»​​ss_sany​

​ затем нажать клавиши​ листа (нижний правый​ в текстовом формате,​ СТРЕЛКА ВЛЕВО —​​ влияет на выбор​​ данные вручную, но​ «равно»​ Поэтому на всякий​и​. Для сохранения изменений​: если не помогает​ строка?​ выделяете мышкой нужный​или используйте сочетание​ гораздо хуже).​

​. Таким образом, мы​Если проверка ошибок в​ SHIFT+F8 для включения​ угол).​ которые необходимо преобразовать.​

Способ 3. Применение числового формата к числам в текстовом формате

​«Формат»​ жмем на кнопку​ эта функция, значит​Range(«A43:L43»).Select​ диапазон и запускаете​ клавиш​В общем и целом,​ к числам (или​ Excel включена, при​ в выделение других​Ячейки до начала листа​Выделение ячеек, диапазонов, строк​ ВВЕРХ или СТРЕЛКА​ объемы и частота​ это займет значительное​и двойной символ​ помечает, чтобы пользователь​.​ «​ есть «лишние знаки»​

​ появление в ваших​ датам) прибавляем 0,​

​ несмежных ячеек или​

​Выделите первую ячейку и​

​ и столбцов​ ВНИЗ — для​ выполняемых преобразований. Например,​ количество времени, тем​

​ что никак не​ ячейку с текстовым​ диапазонов. Чтобы остановить​ нажмите сочетание клавиш​Чтобы выделить​ столбцов).​ если вы часто​ более что существует​(—)​ к сожалению, такие​

​ CTRL+SHIFT+HOME, чтобы расширить​​Выполните следующие действия​​Если в строке или​

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

​ пометки Эксель выдает​нужно указать преобразовываемое​ окна.​ от разделителя знаков,​ конкретный адрес, а​Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd​Значения (Values)​

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

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

​ быстрого решения проблемы.​ первого элемента трансформируемого​ не всегда даже​ число или ссылку​Как видим, после данных​ установленного в системе)​ именно последнюю не​End Sub​и​ последствий:​ в числовой формат​

​ отображать индикаторы ошибок​ клавиши SHIFT+F8.​ листа.​Щелкните ячейку или воспользуйтесь​

​ при нажатии сочетания​

​ запись макроса. И​Выделяем фрагмент листа, содержимое​

​ диапазона. Таким образом,​

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

​ В нашем случае​ о том, что​ через формулы или​Selection.Borders(xlDiagonalUp).LineStyle = xlNone​ пустую ячейку с​ же самое, что​ вниз, а не​ на 1​

​Откройте вкладку​ без отмены всего​ активном выделении​

​ нужной ячейке.​ или столбец до​Автор: Максим Тютюшев​«Данные»​«-1»​ описанный способ подходит​ это будет ссылка​

​ ячейки были преобразованы​ найти-заменить. все достаточно​Selection.Borders(xlEdgeLeft).LineStyle = xlNone​ общим форматом (вобщем​

​ и в прошлом​ располагаются по-порядку как​С помощью инструмента Текст​Файл​ выделения невозможно.​

​Удерживая нажатой клавишу SHIFT,​Диапазон ячеек​ последней заполненной ячейки.​Иногда числа форматируются и​. На ленте инструментов​. Как известно, умножение​

​ не для всех​ на первый элемент​ в текстовый вид.​

​Столбец или строку целиком​

​ щелкните последнюю ячейку,​Щелкните первую ячейку диапазона,​ Повторное нажатие этого​ сохраняются в ячейках​ в блоке​

​ «минус» на «минус»​ случаев.​ обрабатываемого числового диапазона.​

​Но если мы попытаемся​ss_sany​. ​2) Выделяете нужный​ содержимого ячеек на​функции типа​. Этот приём удобно​В группе Справка нажмите​Щелкните заголовок сроки или​

​ которую нужно включить​ а затем перетащите​ сочетания приведет к​

​ВПР (VLOOKUP)​​ использовать если преобразовать​​ кнопку​ столбца.​

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

​Private Sub Замена()​

Отключение проверки ошибок

​3) меню Правка​ напрямую из буфера.​ значения, потому как​ так как если​.​2. Заголовок столбца​ область войдет прямоугольный​ Или удерживая нажатой​Смежные строки или столбцы​

​ вычислениях или нарушению​​.​​ то же значение,​

​ столбцов несколько, то​​В диалоговом окне Параметры​​Вы можете также выделить​​ диапазон между активная​ клавишу SHIFT, нажимайте​Протащите указатель мыши по​​ порядка сортировки. Эта​

​Запускается​​ которое было изначально,​​ пиктограмме.​

Число сохранено как текст или Почему не считается сумма?

​ проблема может возникнуть​Мастер текстов​ но уже в​Открывается список действий. Выбираем​

​«0»​​ полностью. В этом​ расчет формул. Результат​

​If c.Errors.Item(xlNumberAsText).Value =​ «Операция» выбераете «Сложить»​ и записаны с​ число-как-текст различаются:​ для каждого столбца​Формулы​ или столбце, выделив​ ячейкой.​ чтобы расширить выделение.​ столбцов. Либо выделите​ при импорте или​. В первом окне​ числовом виде. Даная​

​ в нем значение​, то текстовый вариант​ и заключается одна​ вычислений копируешь и​ True Then​4) Нажимаете ОК​ неправильными разделителями целой​при фильтрации псевдочисла отбираются​ отдельно. Итак, выделяем​.​ первую ячейку, а​Чтобы отменить выделение ячеек,​Кроме того, можно выделить​

​ первую строку или​ копировании данных из​

​ также перестают нормально​ как текст, устанавливаем​установлен флажок​ (СТРЕЛКА ВПРАВО или​На вкладке​ клавишу F8 для​

​ клавишу SHIFT, выделите​ данных.​«С разделителями»​Enter​В выделенном элементе данные​ в исходнике они​

​ наиболее интуитивно понятным​можно использовать макрос​End If​ реализовать возможность, чтобы​

​ должен находиться в​ готовое преобразованное значение.​ преобразованы в числовой​

​ ячеек диапазона, используем​

​ не одно, а​ десятичным знаком, если​ в каждый элемент​

​ с 0.​​ текст, можно ли​mazayZR​

​Данные (Data)​ помогает. Т.е. вы,​Финансовый​.​ столбце содержатся данные,​и выберите команду​Большой диапазон ячеек​ нажатой клавишу CTRL,​ ошибки.​

​«Далее»​ маркер заполнения, который​ множество, то в​​«0,00»​​ диапазона в отдельности​​Нужна только 1​​ макросом преобразовать их​: а если не​​. На самом деле​​ буквально, выделяете ячейки,​​). Далее выполняем команду​​К началу страницы​ при нажатии сочетания​Специальная вставка​Щелкните первую ячейку диапазона,​ щелкните заголовки столбцов​Способ 1. Преобразование чисел​.​ ранее был применен​ этом случае можно​, то с двумя,​ и нажать на​ свободная ячейка для​​ в числа, ручками​​ макросом, то вот​​ этот инструмент предназначен​​ щелкаете по ним​Данные​​Достаточно часто в Excel​​ CTRL+SHIFT+клавиша со стрелкой​​.​​ а затем, удерживая​ или строк, которые​

​ в текстовом формате​Во втором окне также​

​ нами для функции​​ ускорить процедуру преобразования.​

​ и т.д.​​ клавишу​

​ ввода 1 или​ умею. Макрорекордером не​​ так:​

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

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

​ нажатой клавишу SHIFT,​ требуется добавить в​ с помощью функции​ оставляем все без​ТЕКСТ​Выделяем весь диапазон, в​После того, как все​Enter​ 0. Потом копируете​ получилось.​Саша Уткин​ текста по столбцам,​ выбираете​Текст по столбцам​

​ сторонних программ для​ или столбец до​Операция​

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

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

​«OK»​ можно использовать нажатие​ данными для преобразования​ 1 или прибавить​ Вы очень помогли,​ его с другой​Числовой (Number)​С помощью формул​ использование этих данных​

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

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

​. Если таблица позволяет​ в формулах даёт​ выделению строки или​

​ОК​ прокрутки.​Выделите ячейку в строке​ «Специальная вставка»​ третьего окна​ его и жмем​​ а не для​Как видим, значение первого​F2​ вставку в разделе​ :-)​​ наглость, ещё хочу​

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

​Пропустите первых два шага​ОК​ задействовать дополнительные столбцы,​ непредсказуемый результат: числа​ столбца полностью.​.​Все ячейки листа​ или столбце, а​Способ 3. Применение числового​Мастера текстов​ на кнопку​​ каждой ячейки в​​ элемента заданного диапазона​.​ ОПЕРАЦИИ (ОФ2003) умножить​Бухг​​ спросить. Вот я​​ нажатием на кнопку​​- и ничего​​ то для преобразования​ не суммируются, между​Смежные строки или столбцы​Чтобы удалить содержимое ячейки,​

​Нажмите кнопку​ затем нажмите клавиши​ формата к числам​нужно нажать на​

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

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

​: Микки, а почему​ добился, чтобы курсор​Далее (Next)​ не происходит! Совсем!​ в число можно​ датами невозможно рассчитать​Протащите указатель мыши по​ введенное на этапе​Выделить все​ CTRL+СТРЕЛКА (ВПРАВО или​ в текстовом формате​ кнопку​во вкладке​

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

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

​Возможно, «это не баг,​ использовать формулы. Чтобы​ количество дней и​ заголовкам строк или​ 2, после успешного​.​ ВЛЕВО — для строк,​Отключение проверки ошибок​«Подробнее»​«Главная»​

​ очень нужно, чтобы​Дополнительно (Advanced)​ но нам от​ в число, можно​В этой статье рассматриваются​ первую строку или​ выделите ячейку и​ также можно нажать​

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

​ для столбцов).​ Excel из другого​Открывается окно дополнительной настройки​ клавиш​ в прошлый раз,​ Для того, чтобы​ программой, как текстовые​Артем​ отчёты из 1С​ он спустился на​​. Откроется диалоговое окно,​ этого не легче.​​ использовать двойной минус,​​ причины появления таких​​ первый столбец, а​ нажмите клавишу DEL.​ клавиши CTRL+A.​Первую или последнюю ячейку​ источника, а также​ импорта текста. В​Ctrl+C​ выбираем позицию​

​ перенести и другие​ выражения, а, следовательно,​​: вообщем не пойму​​ в Excel’е и​ одну строчку ниже.​​ где можно задать​​ Так что давайте-к​ сложение с нулём,​ проблем и различные​ затем, удерживая нажатой​

​Некоторые программы бухгалтерского учета​​Если лист содержит данные,​​ на листе или​ при вводе чисел​ поле​

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

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

​Выделяем исходную область и​.​ формулу в смежные​ равна нулю. Кроме​

​ производим щелчок по​Все данные массива будут​ элементы листа. Устанавливаем​ того, как видим,​ через замену не​

​(все форматы) #​Спасибо!​После нажатия на​ один из них​ Более подробно можно​. Число сохранено как​ последний столбец.​ знаком минус (​ диапазон. Повторное нажатие​Чтобы выделить первую ячейку​ как текст, в​устанавливаем точку, а​ ней правой кнопкой​

В Excel числа сохранены как текст.

​ преобразованы в указанный​ курсор в нижний​ левый верхний угол​ работатет. ​ ##0,00;[Красный]-# ##0,00 и​Pavel55​Готово​ вам обязательно поможет.​ почитать здесь. После​ текст​Несмежные строки или столбцы​-​ клавиш CTRL+A приведет​ на листе или​ левом верхнем углу​ в поле​
​ мыши. В активировавшемся​ вид.​

​ правый угол элемента,​​ ячеек будет окрашен​методом тыка придумал​ всё прекрасно считается.​: ActiveCell.End(xlDown).Offset(1, 0).Select​Excel преобразует наш​Если на ячейке с​

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

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

Число сохранено как текст

​Артем​​P.S. сейчас ниже​ текст в нормальные​ числом с текстовом​ можно скопировать и​ что числа или​ или столбца выделения,​ Чтобы преобразовать эти​
​ листа.​

​ нажмите сочетание клавиш​​ маленький зеленый треугольник.​— апостроф. Затем​

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

​Sub xz()​
​: У вас там​
​ (в другом сообщении)​
​ числа.​

​ формате вы видите​
​ вставить как значения​ даты прижаты к​ а затем, удерживая​ текстовые строки в​
​Несмежные ячейки или диапазоны​ CTRL+HOME.​ Этот индикатор ошибки​
​ делаем один щелчок​«Специальная вставка»​ вида в текст,​ маркер заполнения, имеющий​
​ косвенным признаком того,​

​For Each cell​​ пробелы в числах.​ напишу, как более​Если подобные преобразования вам​ зеленый уголок-индикатор, то​ на место исходных​
​ левому краю ячейки​

​ нажатой клавишу CTRL,​​ значения, необходимо с​

​ ячеек​​Чтобы выделить последнюю ячейку,​ указывает на то,​ по кнопке​

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

​«OK»​​«Значения и форматы чисел»​

​ возможность обратного конвертирования​ Зажимаем левую кнопку​ которых находятся цифры,​cell.Formula = cell.Value​ нужному дипазону с​ в таблицу (ниже​

​ то имеет смысл​​ повезло. Можно просто​
​С помощью макросов​
​ как правило, в​ или строк, которые​ все знаки текстовой​ диапазон ячеек, а​ форматирование, на листе​

​ в текстовом виде,​​.​.​ через окно форматирования.​ мыши и протаскиваем​ преобразованы в текстовый​ + 0​

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

​ вариант отображения. Хотя​Next​ на «», запишите​Саша Уткин​ при помощи несложного​ с данными и​ перечисленных способов можно​ ячейки имеется маркер​

​ выделение.​ правого (знака минус)​

​ клавишу CTRL, выделите​

​ Excel, нажмите сочетание​​ данном примере.​Мастера текстов​ нужном нам виде.​ в текстовом варианте.​

​ параллельно диапазону, в​​ этот признак не​End Sub​ операцию макроредактором​: Павел спасибо. Методика​ макроса. Нажмите сочетание​ нажать на всплывающий​ выполнить макросом. Если​ ошибки (зелёный треугольник)​Первую или последнюю ячейку​ и умножить результат​ другие ячейки или​ клавиш CTRL+END.​Если это нежелательно, выполните​

​и жмем на​
​ Теперь можно удалить​
​ Кликаем правой кнопкой​

​ котором находятся исходные​ всегда является обязательным​вроде работает​Микки​ActiveCell.End(xlDown).Offset(1, 0).Select​ клавиш Alt+F11 или​

​ желтый значок с​ Вам приходится часто​

​ диапазоны.​​Ячейки до последней используемой​ указанные ниже действия,​ кнопку​

​ транзитный диапазон, в​

​ мыши. В контекстном​​ данные.​ и в некоторых​Микки​: Микки, а почему​сработала успешно, но​ откройте вкладку​ восклицательным знаком, а​

​ выполнять подобное преобразование,​ при наведении мышки​

​ столбце​​Например, если в ячейке​Вы можете также выбрать​

​ ячейки листа (нижний​​ чтобы преобразовать число​«Готово»​ котором находится формула​
​ меню выбираем позицию​Теперь весь ряд заполнен​ случаях такая пометка​:​

​ он сохраняет его​
​ почему то не​
​Разработчик (Developer)​ затем выбрать команду​ то имеет смысл​ поясняет, что число​Выделите ячейку в строке​

​ A2 содержится значение​
​ первую ячейку или​
​ правый угол)​
​ в текстовом формате​
​.​

​ двойного бинарного отрицания.​​«Формат ячеек…»​

​ требуемыми данными. Но​

​ отсутствует.​Guest​
​ как текст? Мне​ выделяется эта последняя​
​и нажмите кнопку​Преобразовать в число (Convert​
​ написать макрос и​
​ сохранено как текст.​
​ или столбце, а​

​ «156-«, приведенная ниже​

Число как текст

​ диапазон ячеек, а​​Выделите первую ячейку, а​ в обычное число.​Как видим, после выполнения​ Для этого выделяем​.​ и это ещё​Урок:​

​: У меня почему-то​​ постоянно приходится сохранять​ запись. Наверно это​Visual Basic​ to number)​

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

​ это тоже не​​ отчёты из 1С​ глюк программы?​
​. В появившемся окне​:​ мере необходимости.​ Числовой, Общий или​ CTRL+СТРЕЛКА (ВПРАВО или​

​ в значение «-156».​​ SHIFT+F8 для включения​ CTRL+SHIFT+END, чтобы расширить​ диапазон смежных ячеек​ приняли привычный для​ правой кнопки мыши​ Как и в​ сути, все элементы​ Excel​
​ работает. Когда ручками​ в Excel’е и​Pavel55​
​ редактора добавьте новый​Все числа в выделенном​Приведу два примера макросов:​

​ Дата ситуацию не​​ ВЛЕВО — для строк,​Данные​ в выделение других​ выделенную область до​ с индикатором ошибки​ русскоязычной версии формат,​ вызываем контекстное меню​ предыдущий раз, переходим​
​ нового диапазона содержат​Преобразовать число в текстовый​ делаешь- все ок!​ формат при сохранении​: Допустим у вас​ модуль через меню​ диапазоне будут преобразованы​

​1) умножение на 1​ исправляют, но если​ ВВЕРХ или ВНИЗ —​Формула​ несмежных ячеек или​ последней используемой ячейки​ в верхнем левом​

​ диапазонов. Чтобы остановить​​ листа (нижний правый​

​(все форматы) #​​ и вам надо​

​ вставить число 5​ следующий код:​ совсем, то проверьте​ Each c In​ F2), а затем​ на листе или​К началу страницы​

​ диапазонов, снова нажмите​Ячейки до начала листа​ и столбцов​ текстовых данных в​
​.​«Числовые форматы»​ значок​

​ использовав поле для​​ как и было​ всё прекрасно считается.​

​Если вам часто приходится​​ данным методом совсем​ значения, которые позволят​, который расположен во​
​ котором шел разговор​ треугольники в уголках. ​ у меня не​Sub Макрос2()​ = Selection.Value End​ настройках вашего Excel​ c.Value * 1​ дата датой. При​Чтобы выделить первую ячейку​ из текстового формата​ ячеек или диапазонов​
​ CTRL+SHIFT+HOME, чтобы расширить​Отдельную ячейку​ преобразовывать большие области​

​ не обязательно использовать​​ преобразовать текст в​ вкладке​ выше.​ Можт кто-нибудь поборол​ считается. ​

​Dim iLastRow As​ Sub​
​ (​

​ c.NumberFormat = «#,##0.00″​ большом количестве таких​
​ на листе или​ обратно в числовой,​
​ без отмены всего​
​ выделение до начала​

​Щелкните ячейку или воспользуйтесь​

​ данных из текстового​​ исключительно двойное умножение​

Excel как сохранить число как текст

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

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

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

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

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

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

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

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

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

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

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

Excel как сохранить число как текст

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

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

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

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

Вариантом этого приёма может быть умножение диапазона на 1

Приведу два примера макросов:

2) текст по столбцам

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)

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

Как сохранить число в виде текста в Microsoft Excel 2013

Пошаговая инструкция

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

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

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

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

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

И третий способ, в котором необходимо кликнуть правой кнопкой мыши по ячейке, в которую Вы хотите ввести число. Затем в открывшемся окошке кликнем на строку «Формат ячеек…».

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

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

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

Для того, чтобы сохранить числа введенные через значок «слеш» необходимо аналогично поставить апостроф перед введенными числами или изменить формат ячейки на текстовый. Если информация помогла Вам – жмите Спасибо!

Число сохранено как текст Excel

Описание проблемы

Часто мы копируем данные в excel из интернета или текстовых редакторов (word). В результате этого сталкиваемся с проблемой, что некоторые числа вставились как текст, или ячейки с датами преобразовались в числовые значения. Сохраненные как текст числа не участвуют в расчетах и приводят к серьезным математическим ошибкам. Не сходится сумма ячеек, посчитанная в excel со значением на калькуляторе. Хуже всего то, что найти какое же число сохранено как текст довольно проблематично (особенно если в файле не одна тысяча ячеек с данными). Надстройка VBA-Excel поможет быстро найти ошибку.

Решение проблемы

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

Получаем следующую картину.

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

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

Преобразование текста в число в ячейке Excel

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

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

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

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

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

Последний способ подходит в том случае, если значения находятся в одном столбце.

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

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

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

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

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

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

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

В excel число сохранить как текст

Как сохранить число в виде текста в Microsoft Excel 2013

Пошаговая инструкция

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

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

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

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

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

И третий способ, в котором необходимо кликнуть правой кнопкой мыши по ячейке, в которую Вы хотите ввести число. Затем в открывшемся окошке кликнем на строку «Формат ячеек…».

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

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

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

Для того, чтобы сохранить числа введенные через значок «слеш» необходимо аналогично поставить апостроф перед введенными числами или изменить формат ячейки на текстовый. Если информация помогла Вам – жмите Спасибо!

В excel число сохранить как текст

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

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

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

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

Вариантом этого приёма может быть умножение диапазона на 1

Приведу два примера макросов:

2) текст по столбцам

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

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

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

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

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

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

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

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

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

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

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

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

Преобразование текста в число в ячейке Excel

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

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

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

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

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

Последний способ подходит в том случае, если значения находятся в одном столбце.

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

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

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

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

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

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

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

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

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

Как в Excel сделать сумму прописью

Скачать файл надстройки

Чтобы получить эту возможность, вы можете скачать надстройку «Сумма прописью» в Excel бесплатно на нашем сайте.

Разместите его в любом удобном каталоге и подключите. Для этого следует пройти следующие этапы:

Эта функция «сумма_прописью» работает в Excel 2007, 2010, 2016

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

Для проверки введем в ячейке A3 произвольное число. Пусть это будет десятичная дробь. Также вызовем окно вставки функций и найдем новые функции для вывода суммы прописью. Как видите, нам стали доступны:

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

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

Формула суммы прописью в Excel — как написать число

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

В англоязычном Excel формула суммы прописью будет выглядеть следующим образом:

SUBSTITUTE(PROPER(INDEX(n_4,MID(TEXT(A1,n0),1,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),2,1)+1,MID(TEXT(A1,n0),3,1)+1)&IF(-MID(TEXT(A1,n0),1,3),»миллиард»&VLOOKUP(MID(TEXT(A1,n0),3,1)*AND(MID(TEXT(A1,n0),2,1)-1),мил,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),4,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),5,1)+1,MID(TEXT(A1,n0),6,1)+1)&IF(-MID(TEXT(A1,n0),4,3),»миллион»&VLOOKUP(MID(TEXT(A1,n0),6,1)*AND(MID(TEXT(A1,n0),5,1)-1),мил,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),7,1)+1)&INDEX(n1x,MID(TEXT(A1,n0),8,1)+1,MID(TEXT(A1,n0),9,1)+1)&IF(-MID(TEXT(A1,n0),7,3),VLOOKUP(MID(TEXT(A1,n0),9,1)*AND(MID(TEXT(A1,n0),8,1)-1),тыс,2),»»)&INDEX(n_4,MID(TEXT(A1,n0),10,1)+1)&INDEX(n0x,MID(TEXT(A1,n0),11,1)+1,MID(TEXT(A1,n0),12,1)+1)),»z»,» «)&IF(TRUNC(TEXT(A1,n0)),»»,»Ноль «)&»рубл»&VLOOKUP(MOD(MAX(MOD(MID(TEXT(A1,n0),11,2)-11,100),9),10),<0,»ь «;1,»я «;4,»ей «>,2)&RIGHT(TEXT(A1,n0),2)&» копе»&VLOOKUP(MOD(MAX(MOD(RIGHT(TEXT(A1,n0),2)-11,100),9),10),<0,»йка»;1,»йки»;4,»ек»>,2)

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

Преобразование с помощью макросов Excel

Можно написать собственную функцию, которая произведет конвертацию суммы прописью. Нажмите сочетание ALT+F11 и в открывшемся разработчике Excel VBA вставьте новый модуль. В него добавьте следующий код.

Function SUMMPROPIS(n As Double) As String
Dim Chis1, Chis2, Chis3, Chis4, Chis5 As Variant
Chis1 = Array(«», «один «, «два «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Chis2 = Array(«», «десять «, «двадцать «, «тридцать «, «сорок «, «пятьдесят «, «шестьдесят «, «семьдесят «, «восемьдесят», «девяносто «)
Chis3 = Array(«», «сто «, «двести «, «триста «, «четыреста «, «пятьсот «, «шестьсот «, «семьсот «, «восемьсот «, «девятьсот «)
Chis4 = Array(«», «одна «, «две «, «три «, «четыре «, «пять «, «шесть «, «семь «, «восемь «, «девять «)
Chis5 = Array(«десять «, «одиннадцать «, «двенадцать «, «тринадцать «, «четырнадцать «, «пятнадцать «, «шестнадцать «, «семнадцать «, «восемнадцать «, «девятнадцать «)
If n 0 Then thous_txt = Chis4(thous) & «тысяч »
Case 1
thous_txt = Chis4(thous) & «тысяча »
Case 2, 3, 4
thous_txt = Chis4(thous) & «тысячи »
Case 5 To 9
thous_txt = Chis4(thous) & «тысяч »
End Select
If desthous = 0 And thous = 0 And hundthous 0 Then hundthous_txt = hundthous_txt & » тысяч »
eee:
hund_txt = Chis3(hund)
Select Case des
Case 1
cifr_txt = Chis5(cifr)
GoTo rrr
Case 2 To 9
des_txt = Chis2(des)
End Select
cifr_txt = Chis1(cifr)
rrr:
SUMMPROPIS = desmil_txt & mil_txt & hundthous_txt & desthous_txt & thous_txt & hund_txt & des_txt & cifr_txt
End Function
Private Function Retclass(M, I)
Retclass = Int(Int(M — (10 ^ I) * Int(M / (10 ^ I))) / 10 ^ (I — 1))
End Function

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

Перевод в сумму прописью осуществляется только для целых чисел. Дробное число требует дополнительной формулы: =SUMMPROPIS(A7)&» руб. «&ТЕКСТ((A7-ЦЕЛОЕ(A7))*100;»00″)&» коп.»

Вместо ячейки A7 подставляйте свой адрес. Копейки в таком исполнении будут выводиться в виде числа. Мы рассмотрели все способы представления суммы прописью в Excel. Выбирайте для себя самый удобный и пользуйтесь с удовольствием!

Преобразование текста в число в ячейке Excel

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

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

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

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

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

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

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

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

Читайте так же: как перевести число и сумму прописью в Excel.

Скачать примеры преобразования текста в число.

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

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

72570 13.08.2016 Скачать пример

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

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

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

Особенно забавно, что естественное желание просто изменить формат ячейки на числовой — не помогает. Т.е. вы, буквально, выделяете ячейки, щелкаете по ним правой кнопкой мыши, выбираете Формат ячеек (Format Cells), меняете формат на Числовой (Number), жмете ОК — и ничего не происходит! Совсем!

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

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

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

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

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

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

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

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

Также вместо F2 можно просто делать двойной щелчок левой кнопкой мыши по ячейке.

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

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

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

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

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

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

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

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

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

Выделите исходный диапазон с данными и нажмите кнопку Текст по столбцам (Text to columns) на вкладке Данные (Data).

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

Пропустите первых два шага нажатием на кнопку Далее (Next), а на третьем воспользуйтесь кнопкой Дополнительно (Advanced). Откроется диалоговое окно, где можно задать имеющиеся сейчас в нашем тексте символы-разделители:

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

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

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

Sub Convert_Text_to_Numbers()
Selection.NumberFormat = «General»
Selection.Value = Selection.Value
End Sub

Теперь после выделения диапазона всегда можно открыть вкладку Разрабочик — Макросы (Developer — Macros), выбрать наш макрос в списке, нажать кнопку Выполнить (Run) — и моментально преобразовать псевдочисла в полноценные.

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

С датами бывает та же история. Некоторые даты тоже могут распознаваться Excel’ем как текст, поэтому не будет работать группировка и сортировка. Решения — те же самые, что и для чисел, только формат вместо числового нужно заменить на дату-время.

Ссылки по теме

Преобразование чисел в текст в Excel — Microsoft Excel для начинающих

По умолчанию числа выровнены по правому краю, а текст – по левому. Этот пример научит вас преобразовывать числа в текстовые строки, представленные цифрами.

Примечание: Код #,## используется для добавления запятой в больших числах. В русской локализации Excel запятую необходимо заменить на пробел.

Еще один пример с применением процентного формата:

И еще один пример. Форматирование даты.

Примечание: Используйте меньшее или большее количество символов m (м), d (д) и y (г), чтобы изменить внешний вид даты.

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

Excel. Сумма прописью без макросов и надстроек

Дано: Excel c числами и суммами в рублях в ячейках.
Задача: написать данные числа прописью без использования макросов и каких-либо надстроек программы Excel, т.е. написать число прописью при помощи стандартных формул.

Пример файла excel с таким решением можно скачать здесь.

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

1. Формула 1 преобразовывает число в сумму прописью следующего формата:

«0 (пропись) рублей 00 копеек»

2. Формула 2 преобразовывает число в сумму прописью следующего формата:
«Пропись рублей 00 копеек»

3. Формула 3 просто переводит число в текст: число = пропись

Вот формулы, которые пишут сумму прописью:

При необходимости формулу можно подогнать под нужный формат.

Как использовать данную формулу?

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

Чтобы создать справочник таких диапазонов, переходим на вкладку «Формулы» — «Диспетчер имен«:

В открывшемся окне нажимаем кнопку «Создать» и в форме вводим данные первого диапазона из таблицы:

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

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

Основано на материалах форума excelworld.ru/forum/3-9902

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

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

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

Варианты решения

Существует несколько способов исправления ошибки формата. Будем рассматривать от простых методов к более сложным.

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

Массово применить сочетание функций можно при помощи автозаполнения.

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

Число сохранено как текст или Почему не считается сумма?

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

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

Вариантом этого приёма может быть умножение диапазона на 1

Sub conv()
Dim c As Range
For Each c In Selection
If IsNumeric(c.Value) Then
c.Value = c.Value * 1
c.NumberFormat = «#,##0.00»
End If
Next
End Sub

Sub conv1()
Selection.TextToColumns
Selection.NumberFormat = «#,##0.00»
End Sub

Причина вторая. В записи числа присутствуют посторонние символы.

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

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

Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить.

Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

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

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

Как определить, где в ячейке текст, а где число?

Как перевести ячейку из текстового в числовой формат?

Способ 1

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

Способ 2

Выйти на то же меню можно и другим способом: выделите ячейки и нажмите правую кнопку мыши (ПКМ), в открывшемся меню выберите Формат ячеек.

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

Способ 3

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

Для этого левой кнопкой мыши кликните на соседнюю ячейку и напишите формулу =ЗНАЧЕН(E4) (возможные названия: =ЗНАЧ(E4) или =VALUE(E4) зависит от используемой версии программы). ENTER.

Обратите внимание!

«Преобразовать текст в число Excel» — не совсем правильная формулировка. Правильнее будет сказать: «Преобразование из текстового формата в числовой».

Пашкова Екатерина Ивановна, преподавать основ компьютерной грамотности, офисных программ.

Автор скриншотов: Белицкий Алексей Викторович, учитель МКОУ «СОШ №7», с. Пелагиада, Шпаковский район, Ставропольский край.

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

и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.

Преобразование в ТЕКСТ в Excel. Бесплатные примеры и статьи

Преобразуем число из текстовой формы в обычное число из цифр: например, «сто пятьдесят три» преобразуем в 153.

Числовой пользовательский формат – это формат отображения числа задаваемый пользователем. Например, число 5647,22 можно отобразить как 005647 или, вообще в произвольном формате, например, +(5647)руб.22коп. Пользовательские форматы также можно использовать в функции ТЕКСТ(). Эта функция возвращает текстовое значение в нужном пользователю виде.

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

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

Функция ТЕКСТ(), английская версия TEXT(), преобразует число в текст и позволяет задать формат отображения с помощью специальных строк форматирования, например, формула =ТЕКСТ(100;»0,00 р.

«) вернет текстовую строку 100,00 р., а формула =ТЕКСТ(1;»#%») вернет текст (не число!) 100%.

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

Функция ФИКСИРОВАННЫЙ(), английская версия FIXED(), округляет число до заданного количества десятичных цифр и возвращает результат в виде текста.

Функция РИМСКОЕ(), английский вариант ROMAN(), преобразует арабское число в римское, которое возвращается в текстовом формате.

Запишем число прописью в Excel без использования VBA. Вспомогательные диапазоны разместим в личной книге макросов. Кроме того, добавим руб./коп. для записи денежных сумм, например: четыреста сорок четыре руб. 00 коп.

Превратим число 256 в строку «двести пятьдесят шесть». Число прописью запишем без использования VBA.

В EXCEL легко отформатировать шрифт, чтобы отобразить надстрочные (x2) и подстрочные (Al2O3) символы. Это можно сделать выделив часть текста в ячейке и через диалоговое окно Формат ячеек / Шрифт (CTRL+SHIFT+F) применить необходимое форматирование шрифта. Но этот подход не работает, если в ячейке находится не значение, а формула. И все же, в ряде случаев, выход есть.

Если Вам нужно отобразить широту или долготу в формате 56°54’31″ (56 градусов 54 минуты 31 секунда), то этот пример для Вас.

При составлении формул для отображения в ячейке фразы содержащей текст и дату, например, «Сегодня 02.10.10», можно получить вот такой результат: «Сегодня 40453», т.е. дата будет отражена в числовом виде. Решим задачу путем предварительного преобразования даты в текст в нужном нам формате.

Учимся корректно склонять сутки и часы: 1 сутки 2 часа; 3 суток 6 часов.

При составлении формул для отображения в ячейке фразы содержащей текст и время, например, «Сейчас 10:35», могут возникнуть сложности с правильным отображением времени. Решим задачу путем предварительного преобразования времени в текстовое значение.

Учимся корректно записывать число лет: 1 год, 2 года, 5 лет, 11 лет.

Учимся корректно склонять число месяцев: 1 месяц, 2 месяца, 10 месяцев, 1 день, 2 дня, 10 дней.

При составлении формул для отображения в ячейке фразы содержащей текст и число могут возникнуть сложности с правильным отображением формата числа. Например, требуется составить фразу «Масса груза 66,00 кг», но получается «Масса груза 66 кг». Решим задачу путем предварительного преобразования числа в текст.

EXCEL хранит и выполняет вычисления с точностью 15 значащих цифр. Поэтому, не получится ввести 20-и значное число без того, чтобы EXCEL автоматически округлил его с точностью до 100 тыс. Используем текстовый формат для отображения таких чисел.

Сохранить число в текст в excel

Сохранить число в текст в excel

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

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

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

Никакие изменения формата на Числовой, Общий или Дата ситуацию не исправляют, но если кликнуть в строку формул (или нажать F2), а затем Enter, то число становится числом, а дата датой. При большом количестве таких чисел вариант, согласитесь, неприемлемый.

Есть несколько способов решения данной проблемы

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

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

Аналогичную замену можно проделать и формулой (см. ниже), используя функцию ПОДСТАВИТЬ()

Вариантом этого приёма может быть умножение диапазона на 1

Приведу два примера макросов:

2) текст по столбцам

Чаще всего этими посторонними символами являются пробелы. Они могут располагаться как внутри числа в качестве разделителя разрядов, так и до/после числа. В этом случае, естественно, число становится текстом.

Убрать лишние пробелы также можно с помощью операции Найти/Заменить. В поле Найти вводим пробел, а поле Заменить на оставляем пустым, далее Заменить все. Если в числе были обычные пробелы, то этих действий будет достаточно. Но в числе могут встречаться так называемые неразрывные пробелы (символ с кодом 160). Такой пробел придётся скопировать прямо из ячейки, а затем вставить в поле Найти диалогового окна Найти/Заменить. Либо можно в поле Найти нажать сочетание клавиш Alt+0160 (цифры набираются на цифровой клавиатуре).

Пробелы можно удалить и формулой. Варианты:

Для обычных пробелов: =—ПОДСТАВИТЬ(B4;» «;»»)

Для неразрывных пробелов: =—ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»)

Сразу для тех и других пробелов: =—ПОДСТАВИТЬ(ПОДСТАВИТЬ(B4;СИМВОЛ(160);»»);» «;»»)

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

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

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

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

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

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

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

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

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

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

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

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

Как сохранить число в виде текста в Microsoft Excel 2013

Пошаговая инструкция

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

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

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

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

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

И третий способ, в котором необходимо кликнуть правой кнопкой мыши по ячейке, в которую Вы хотите ввести число. Затем в открывшемся окошке кликнем на строку «Формат ячеек…».

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

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

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

Для того, чтобы сохранить числа введенные через значок «слеш» необходимо аналогично поставить апостроф перед введенными числами или изменить формат ячейки на текстовый. Если информация помогла Вам – жмите Спасибо!

Преобразование в ТЕКСТ в Excel. Бесплатные примеры и статьи.

Переводим число прописью в число цифрами в Excel

Преобразуем число из текстовой формы в обычное число из цифр: например, «сто пятьдесят три» преобразуем в 153.

Пользовательский ЧИСЛОвой формат в MS EXCEL (Функция ТЕКСТ())

Название месяца прописью в MS EXCEL

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

Отображение в MS EXCEL ЧИСЕЛ в формате миллионов и тысяч

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

Функция ТЕКСТ() в MS EXCEL

Функция ФИКСИРОВАННЫЙ() в MS EXCEL

Преобразование текста в число в ячейке Excel

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

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

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

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

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

Последний способ подходит в том случае, если значения находятся в одном столбце.

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

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

Есть набор значений, сохраненных в текстовом формате:

Чтобы вставить макрос, на вкладке «Разработчик» находим редактор Visual Basic. Открывается окно редактора. Для добавления кода нажимаем F7. Вставляем следующий код:

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

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

Цифры переместились вправо.

Следовательно, значения в ячейках «стали» числами.

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

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