Как изменить формат ячейки на числовой: Изменение числа в текстовом формате на числовой формат в Excel для Интернета

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

Автор Елизавета КМ На чтение 10 мин Опубликовано 18.12.2020

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

Содержание

1. Основные виды форматирования и их изменение
2. Способ 1: контекстное меню
3. Способ 2: блок инструментов «Число» на ленте
4. Способ 3: блок инструментов «Ячейки»
5. Способ 4: горячие клавиши
6. Формат даты со временем в Exel и 2 системы его отображения
7. Вкладка «Выравнивание»
8. Настраиваем текст
9. Как изменить шрифт Excel
10. Как выбрать способы начертания Excel
11. Выравнивание текста в ячейках
12. Автоформатирование текста в Excel
13. Как объединить ячейки в Excel
14. Изменение ориентации и направления текста
15. Работа со стилями форматирования ячеек Excel
16. Зачем нужны именованные стили
17. Применение стилей к ячейкам листа
18. Создание новых стилей
19. Изменение существующих стилей
20. Перенос стилей в другую книгу
21. Заключение

Основные виды форматирования и их изменение

Всего имеется десять основных форматов:

1. Общий.
2. Денежный.
3. Числовой.
4. Финансовый.
5. Текстовый.
6. Дата.
7. Время.
8. Дробный.
9. Процентный.
10. Дополнительный

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

Способ 1: контекстное меню

Применение контекстного меню для редактирования формата – это один из самых часто применяемых методов. Пошаговое руководство:

1. Нужно выделить те ячейки, формат которых необходимо отредактировать. Нажимаем на них правой кнопкой мыши. Открылось специальное контекстное меню. Нажимаем на элемент «Формат ячеек…».

1

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

2

3. Готово. Редактирование формата произошло успешно.

Способ 2: блок инструментов «Число» на ленте

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

1. Осуществляем переход в раздел «Главная». Далее выделяем желаемую ячейку или же диапазон ячеек и в блоке «Число» раскрываем окошко выбора.

3

2. Раскрылись основные варианты формата. Выбираем тот, который нужен в выделенной области. Форматирование изменилось.

4

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

5

4. После нажатия на этот элемент появится привычное окошко со всеми возможными вариантами форматирования (основные и дополнительные).

6

Способ 3: блок инструментов «Ячейки»

Следующий метод редактирования формата выполняется через блок «Ячейки». Пошаговое руководство:

1. Производим выделение ячейки или диапазона ячеек, формат которых хотим изменить. Перемещаемся в раздел «Главная», нажимаем на надпись «Формат». Этот элемент располагается в блоке «Ячейки». В раскрывшемся списке нажимаем на «Формат ячеек…».

7

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

Способ 4: горячие клавиши

Формат ячейки можно отредактировать, используя специальные горячие клавиши табличного процессора. Для начала необходимо произвести выделение нужных ячеек, а потом нажать сочетание клавиш Ctrl+1. После проведения манипуляций откроется знакомое окошко изменения формата. Как и в предыдущих методах выбираем нужный формат и кликаем «ОК». Дополнительно существуют другие сочетания клавиш, которые позволяют отредактировать формат ячейки без отображения окошка форматирования:

• Ctrl+Shift+- — общий.
• Ctrl+Shift+1 — числа с запятой.
• Ctrl+Shift+2 — время.
• Ctrl+Shift+3 — дата.
• Ctrl+Shift+4 – денежный.
• Ctrl+Shift+5 – процентный.
• Ctrl+Shift+6 — формат О.ООЕ+00.

Формат даты со временем в Exel и 2 системы его отображения

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

8

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

9

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

1. Число 1 – это 1 января 1900 года.
2. Число 0 – это 1 января 1904 года, а число 1 – это 02.01.1904.

Изменение отображения дат проводится следующим образом:

1. Переходим в «Файл».
2. Жмем «Параметры» и перемещаемся в раздел «Дополнительно».
3. В блоке «При пересчете этой книги» выбираем пункт «Использовать систему дат 1904».

Вкладка «Выравнивание»

При помощи вкладки «Выравнивание» можно задать расположение значения внутри ячейки по нескольким параметрам:

• по направлению;
• по горизонтали;
• по вертикали;
• относительно центра;
• и так далее.

По умолчанию набранное число в ячейке выравнивается по правому краю, а текстовая информация – по левому. В блоке «Выравнивание», вкладки «Главная» можно найти основные элементы форматирования.

10

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

Настраиваем текст

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

Как изменить шрифт Excel

Разберем несколько способов изменения шрифта:

1. Способ первый. Выделяем ячейку, переходим в раздел «Главная» и выбираем элемент «Шрифт». Открывается список, в котором каждый пользователь сможет подобрать для себя подходящий шрифт.

11

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

12

3. Способ третий. Выделяем ячейку и при помощи комбинации клавиш Ctrl+1 вызываем «Формат ячеек». В появившемся окошке выбираем раздел «Шрифт» и проводим все необходимые настройки.

13

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

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

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

1. При помощи комбинации клавиш:

• Ctrl+B – полужирное;
• Ctrl+I – курсивное;
• Ctrl+U – подчеркнутое;
• Ctrl+5 – перечеркнутое;
• Ctrl+= – подстрочное;
• Ctrl+Shift++ – надстрочный.

2. При помощи инструментов, находящихся в блоке «Шрифт» вкладки «Главная».

14

3. Используя окошко «Формат ячеек». Здесь можно выставить нужные настройки в разделах «Видоизменение» и «Начертание».

15

Выравнивание текста в ячейках

Выравнивание текста в ячейках осуществляется следующими методами:

• Заходим в блок «Выравнивание» раздела «Главная». Здесь при помощи иконок можно произвести выравнивание данных.

16

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

17

Автоформатирование текста в Excel

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

Два метода автоформатирования:

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

Как объединить ячейки в Excel

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

18

Изменение ориентации и направления текста

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

19

Перейдя в раздел «Главная» блок «Выравнивание» и элемент «Ориентация», можно применить эти два параметра.

Работа со стилями форматирования ячеек Excel

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

20

Зачем нужны именованные стили

Основные цели использования стилей:

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

Применение стилей к ячейкам листа

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

1. Переходим во вкладку «Главная», находим блок «Стили ячеек».

21

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

Создание новых стилей

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

1. Выделяем любую ячейку и производим ее форматирование. Мы будем создавать стиль на основе этого форматирования.
2. Переходим в раздел «Главная» и перемещаемся в блок «Стили ячеек». Кликаем «Создать стиль ячейки». Открылось окошко под названием «Стиль».

22

3. Вводим любое «Имя стиля».
4. Выставляем все необходимые параметры, которые вы желаете применять к создаваемому стилю.
5. Кликаем «ОК».
6. Теперь в библиотеку стилей добавился ваш уникальный стиль, который можно использовать в этом документе.

Изменение существующих стилей

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

1. Переходим в раздел «Главная» и выбираем «Стили ячеек».
2. Жмем правой кнопкой мыши по стилю, который желаем отредактировать, и кликаем «Изменить».
3. Открылось окошко «Стиль».
4. Кликаем «Формат» и в отобразившемся окошке «Формат ячеек» настраиваем форматирование. После проведения всех манипуляций кликаем «ОК».
5. Снова нажимаем «ОК», чтобы закрыть окошко «Стиль». Редактирование готового стиля завершено, теперь его можно применять к элементам документа.

Перенос стилей в другую книгу

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

Пошаговое руководство:

1. Отрываем документ, в котором находятся созданные стили.
2. Дополнительно открываем другой документ, в который желаем перенести созданный стиль.
3. В документе со стилями переходим во вкладку «Главная» и находим блок «Стили ячеек».
4. Кликаем «Объединить». Появилось окошко под названием «Объединение стилей».
5. В этом окошке находится список всех открытых документов табличного процессора. Выбираем тот документ, в который хотим перенести созданный стиль и кликаем кнопку «ОК». Готово!

Заключение

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

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

почему не работает формат в таблице – WindowsTips.Ru. Новости и советы

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

Причины, почему не меняется форматирование в Excel

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

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

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

Что делать

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

Вариант №1

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

• Проверьте, в каком формате сохранена книга. Если в XLS, жмите на «Сохранить как» и «Книга Excel (*.xlsx). Иногда ничего не меняется из-за неправильного расширения.

• Закройте документ.
• Измените расширение для книги с RAR на ZIP. Если расширение не видно, войдите в «Панель управления», а далее «Свойства / Параметры папки», вкладка «Вид». Здесь снимите отметку со «Скрывать расширение для зарегистрированных типов файлов».

• Откройте архив любой специальной программой.
• Найдите в нем следующие папки: _rels, docProps и xl.
• Войдите в xl и деинсталлируйте Styles.xml.
• Закройте архив.
• Измените разрешение на первичное .xlsx.
• Откройте книгу и согласитесь с восстановлением.
• Получите информацию об удаленных стилях, которые не получилось восстановить.

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

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

Вариант №2

Если формат ячеек вдруг не меняется в Excel, можно воспользоваться другим методом:

1. Копируйте любую ячейку, с которой не возникало трудностей в этом вопросе.
2. Выбелите проблемный участок, где возникла проблем, с помощью ПКМ.
3. Выберите «Специальная вставка» и «Форматы».

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

Вариант №3

Следующее решение, когда не меняется форматирование в Excel — попытка правильного выполнения работы. Сделайте следующее:

1. Выделите ячейки, требующие форматирования.
2. Жмите на Ctrl+1.
3. Войдите в «Формат ячеек» и откройте раздел «Число».
4. Перейдите в раздел «Категория», а на следующем шаге — «Дата».
5. В группе «Тип» выберите подходящий формат информации. Здесь будет возможность предварительного просмотра формата с 1-й датой в данных. Его можно найти в поле «Образец».

Для быстрой смены форматирования даты по умолчанию жмите на нужный участок с датой, а после кликните сочетание Ctrl+Shift+#.

Вариант №4

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

• Жмите на любую секцию с цифрой и кликните комбинацию Ctrl+C.
• Выделите диапазон, требующий конвертации.
• На вкладке «Вставка» жмите «Специальная вставка».

• Под графой «Операция» кликните на «Умножить».

• Выберите пункт «ОК».

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

Вариант №5

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

Для решения вопроса сделайте следующее:

1. Выберите весь столбец.
2. Кликните на «Данные», а далее «Текст по столбцам».
3. Жмите на клавишу «Готово».
4. Применяйте форматирование, которое вас интересует.

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

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

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

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

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

1. Использование ‘ (апостроф) перед числом.
   • Многие люди вводят апостроф перед числом, чтобы сделать его текстовым. Иногда это также происходит, когда вы загружаете данные из базы данных. Хотя это заставляет числа отображаться без апострофа, это влияет на ячейку, заставляя ее обрабатывать числа как текст.
2. Получение чисел в результате формулы (например, LEFT, RIGHT или MID)
   • Если вы извлекаете числовую часть текстовой строки (или даже часть числа) с помощью функций ТЕКСТ, результатом будет число в текстовом формате.

Теперь давайте посмотрим, как действовать в таких случаях.

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

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

• Использование опции «Преобразовать в число».
• Измените формат с Текст на Общий/Число.
• Использование специальной вставки.
• Использование текста в столбцах.
• Использование комбинации функций VALUE, TRIM и CLEAN.

Преобразование текста в числа с помощью параметра «Преобразовать в число»

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

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

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

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

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

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

Вот шаги:

• Выберите все ячейки, которые вы хотите преобразовать из текста в числа.
• Перейти на главную -> номер. В раскрывающемся списке Формат числа выберите Общий.

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

 Читайте также: Преобразование серийных номеров в даты в Excel 

Преобразование текста в числа с помощью специальной опции вставки

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

• Введите 1 в любую пустую ячейку на листе. Убедитесь, что он отформатирован как число (т. е. выровнен по правому краю ячейки).
• Скопируйте ячейку, содержащую 1.
• Выберите ячейки, которые вы хотите преобразовать из текста в числа.
• Щелкните правой кнопкой мыши и выберите «Специальная вставка».
• В диалоговом окне «Специальная вставка» выберите «Умножение» в категории «Операция».
• Нажмите OK.

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

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

Вот шаги:

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

Хотя вы по-прежнему можете обнаружить, что результирующие ячейки имеют текстовый формат, а числа по-прежнему выровнены по левому краю, теперь это будет работать в таких функциях, как СУММ и СРЗНАЧ.

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

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

• Функция ЗНАЧ преобразует любой текст, представляющий число, обратно в число.
• Функция TRIM удаляет все начальные и конечные пробелы.
• Функция CLEAN удаляет лишние пробелы и непечатаемые символы, которые могут проникнуть внутрь, если вы импортируете данные или загружаете их из базы данных.

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

 =ЗНАЧ(ОТРЕЗАТЬ(ОЧИСТИТЬ(A1))) 

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

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

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

• Умножение в Excel с помощью специальной вставки.
• Как преобразовать текст в дату в Excel (8 простых способов)
• Как преобразовать числа в текст в Excel
• Преобразование формулы в значения с помощью специальной вставки.
• Пользовательское числовое форматирование Excel.
• Преобразование времени в десятичное число в Excel
• Изменить отрицательное число на положительное в Excel
• Как сделать первую букву текстовой строки заглавной в Excel
• Преобразование научной нотации в число или текст в Excel
• Как преобразовать дату в серийный номер в Excel?

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

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

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

• Как определить числа, отформатированные как текст
• Как преобразовать текст в число в Excel
  • Функция преобразования Excel в число
  • Преобразование текста в число путем изменения формата
  • Изменить текст на число с помощью специальной вставки
  • Изменить строку на число с текстом в столбцы
  • Преобразование текста в числовую формулу
  • Превратите текст в число с помощью математических операций

Как идентифицировать числа, отформатированные как текст в Excel

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

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

Цифры	Строки (текстовые значения)
По умолчанию с выравниванием по правому краю. Если выбрано несколько ячеек, в строке состояния отображается Среднее , Подсчет и СУММ .	По умолчанию выравнивается по левому краю. Если выбрано несколько ячеек, в строке состояния отображается только Count . В поле Числовой формат отображается текстовый формат (во многих случаях, но не всегда). В строке формул может быть виден начальный апостроф.

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

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

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

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

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

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

Готово!

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

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

1. Выберите ячейки с числами в текстовом формате.
2. На вкладке Домашний в группе Номер выберите Общий или Номер из раскрывающегося списка Формат номера .

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

Преобразование текста в число с помощью специальной вставки

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

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

1. Выберите ячейки с текстом и установите для них формат Общий , как описано выше.
2. Скопируйте пустую ячейку. Для этого либо выделите ячейку и нажмите Ctrl + C, либо щелкните правой кнопкой мыши и выберите Копировать из контекстного меню.
3. Выберите ячейки, которые вы хотите преобразовать в числа, щелкните правой кнопкой мыши и выберите Специальная вставка . Либо нажмите сочетание клавиш Ctrl + Alt + V.
4. В диалоговом окне Специальная вставка выберите Значения в разделе Вставка и Добавить в разделе Операция .
5. Щелкните OK .

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

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

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

1. Выберите ячейки, которые вы хотите преобразовать в числа, и убедитесь, что их формат установлен на Общий .
2. Перейдите на вкладку Data , группу Data Tools и нажмите кнопку Text to Columns .
3. На шаге 1 Мастера преобразования текста в столбцы выберите Разделители в разделе Исходный тип данных и нажмите Готово .

Вот и все!

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

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

Формула 1. Преобразование строки в число в Excel

Microsoft Excel имеет специальную функцию для преобразования строки в число — функцию ЗНАЧ. Функция принимает как текстовую строку, заключенную в кавычки, так и ссылку на ячейку, содержащую преобразуемый текст.

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

Например, формула ЗНАЧЕНИЕ распознает число, введенное с символом валюты и разделителем тысяч:

=ЗНАЧ("$1,000")

=ЗНАЧ(A2)

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

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

Формула 2. Преобразование строки в дату

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

Например:

=ЗНАЧ("1 января-2018")

Или

=ЗНАЧ(A2)

Где A2 содержит текстовую дату.

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

Тот же результат может быть достигнут с помощью функции ДАТАЗНАЧ:

=ДАТАЗНАЧ(A2)

Дополнительные сведения см. в разделе Как преобразовать текст в дату в Excel.

Формула 3. Извлечение числа из строки

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

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

=ЗНАЧ(ПРАВО(A2,3))

На приведенном ниже снимке экрана показана наша формула преобразования текста в число в действии:

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