Как в эксель перевести в число

Преобразование в MS EXCEL ЧИСЕЛ в формат ВРЕМЯ

EXCEL хранит ВРЕМЯ в числовой форме (в дробной части числа). Например, 0,75 соответствует 18:00, 0,5 — 12:00. Если, по какой-то причине, значения ВРЕМЕНИ сохранены в десятичной форме, например, 10,5 часов, то их можно преобразовать в стандартный формат времени (часы : минуты : секунды). В статье также показано как преобразовать число 1800 в 18:00.

Если в ячейке содержится число 40638,5, то изменив соответствующим образом Формат ячеек можно его отобразить в виде 05.04.2011 12:00:00 (40638 соответствует дате 05.04.2011, а 0,5 соответствует 12 часам дня, подробнее в статье Как EXCEL хранит дату и время). Для этого вызовите Диалоговое окно Формат ячеек, нажав CTRL+1, выберите числовой формат Дата, затем выберите в поле Тип — формат 14.03.01 13:30. Также с помощью Формата ячеек число 40638,5 можно отобразить как 12:00:00.

Преобразование ВРЕМЕНИ из десятичного числа в стандартный формат времени (часы : минуты : секунды)

Пример1а

Если в ячейке A1 содержится значение времени (часов) в десятичной форме (10,5), то для отображения в стандартном формате времени используйте формулу =ТЕКСТ(A1/24; «ч:мм») Результат 10:30 в текстовом формате (функция =ТЕКСТ() возвращает текстовое значение). 0,5 означает долю часа, т.е. полчаса, т.е. 30 минут.

Если значение нужно в формате времени, то можно записать формулу =A1/24 . Формат ячейки, содержащий формулу, необходимо перевести в формат Время (см. файл примера ).

Другой вариант: =ВРЕМЯ(10;30;0) Аргументами функции ВРЕМЯ() могут быть только целые числа, поэтому 0,5 часа были заменены 30 минутами. Чтобы не переводить доли часов в минуты, можно написать формулу =ВРЕМЯ(A1;ОСТАТ(A1;1)*60;0) Результат 10:30 АМ.

Пример1б

Пусть в ячейке А1 находится 52,84. 52 — это часы, а 84 — доля часа. Результат нужно отобразить в виде 52 ч 50 мин (в текстовом формате). Используйте формулу =ЦЕЛОЕ(A1)&» ч «&ТЕКСТ(A1/24;»мм»)&» мин» . Если нужен результат в 2-х ячейках (отдельно часы, отдельно минуты), то =ЦЕЛОЕ(A1) вернет часы, а =ЦЕЛОЕ(ОСТАТ(A1;1)*60) минуты.

В отличие от примера 1а, здесь количество часов превышает 24 (количество часов в сутках). Поэтому формула =ТЕКСТ(A1/24;»ч:мм») вернет 4:50, т.е. количество часов будет уменьшено на 48=2*24 (на максимальное количество часов кратных 24, которое меньше заданного значения часов). Следовательно, формат ВРЕМЯ использовать нельзя, т.к. он отображает количество часов от 0 до 23.

Пример2

Если целая часть числа содержит часы, а десятичная минуты, например, 10,75, то формула =ВРЕМЯ(ЦЕЛОЕ(A1);100*(A1-ЦЕЛОЕ(A1));0) переведет это число в формат времени 11:15. Этот подход отличается от рассмотренного выше Примера 1а (число 10,5), когда десятичная часть обозначала долю часа, а не минуты. Т.е. 0,5 в первом случае 30 минут, а во втором — 50 минут. Главное не запутаться. Лучше — не сохраняйте время в десятичном формате. Храните часы и минуты в разных ячейках, затем используйте функцию ВРЕМЯ() .

Пример3

Если число 145 означает 145 минут, то перевести его в формат ВРЕМЯ можно формулой =ВРЕМЯ(0;145;0) . Результат 2:25 АМ (утра), т.к. 2 (часа)*60 минут + 25 минут = 145 минут.

Преобразование 1800 в 18:00

Если вы вводили (с целью ускорения ввода) значения времени без двоеточия (1800 вместо 18:00), то EXCEL будет обращаться с этими значениями как с числами (т.е. при прибавлении 65, вы получите 1865, а не 19:05). Преобразуем число 1800 (в ячейке А1) в значение в формате времени (в шесть часов вечера 18:00) 4-мя способами.

1. Использование функции ВРЕМЯ
Напишем формулу: =ВРЕМЯ(ЛЕВСИМВ(A1;ДЛСТР(A1)-2);ПРАВСИМВ(A1;2);) Если в А1 введено 427, формула вернет 04:27. Результат — значение в формате времени.

2. Использование функции ВРЕМЗНАЧ
Напишем формулу: =ВРЕМЗНАЧ(ЗАМЕНИТЬ(A1;ДЛСТР(A1)-1;0;»:»)) . В формуле использована функция ЗАМЕНИТЬ() , которая вставляет двоеточие в нужное место, и функция ВРЕМЗНАЧ() , которая преобразует текстовое значение в формат времени ( ЗАМЕНИТЬ() возвращает в текстовом формате). Формат ячейки необходимо поменять на формат Время.

3. Преобразование числа в текст, затем в формат времени
Переведем наше число 1800 в текстовый формат с помощью функции ТЕКСТ() и одновременно отформатируем его по шаблону «00:00″ (обратный слеш нужен для корректного интерпретирования двоеточия). Запишем формулу: =ТЕКСТ(A1;»00:00») . Тем самым, из числа 1800 получим текст «18:00». Другой вариант =ТЕКСТ(A1;»ч:мм»)

Затем слегка преобразуем формулу: =—ТЕКСТ(A1;»00:00″) . Применив к тексту арифметические операции (два минуса), мы запустим механизм автоформатирования: EXCEL попытается интерпретировать текст «18:00» как число, дату или время (мы как бы моделируем ситуацию, когда в пустую ячейку пользователь вручную вводит «18:00»). Единственное, что не сделает EXCEL в этом случае — это не изменит формат ячейки на Время. Сделаем это вручную.

Читать еще:  Excel где хранятся резервные копии

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

4. Прямое преобразование в формат времени
В формате Дата принято, что одним суткам соответствует число единица. Соответственно, конкретное время, это часть суток: двенадцать дня это 0,5; шесть вечера это 0,75. Число равное 1/24 (т.е. 0,041667) соответствует одному часу. Учитывая, что в сутках 24 часа, в часе 60 минут, а в сутках 1440 минут, напишем следующую формулу: =ЦЕЛОЕ(A1/100)/24+ОСТАТ(A1;100)/1440 . Далее изменяем формат ячейки на Время.

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

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

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

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

  • То, что выровнено по левому краю ячейки, это — текстовый формат, по правому краю — число (если сам пользователь не применил выравнивание принудительно). Однако выравнивание ячейки по левому и правому краю через инструмент Выравнивание не меняет формата ячейки.
  • Нажать на ячейку и в верхнем меню на вкладке Главная найти формат: общий, текстовый, числовой и т.д.

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

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

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

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

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

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

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

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

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

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

Есть мнение?
Оставьте комментарий

Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями

Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст

Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.

Кстати, такая возможность есть
на всех страницах нашего сайта

2007-2019 «Педагогическое сообщество Екатерины Пашковой — PEDSOVET.SU».
12+ Свидетельство о регистрации СМИ: Эл №ФС77-41726 от 20.08.2010 г. Выдано Федеральной службой по надзору в сфере связи, информационных технологий и массовых коммуникаций.
Адрес редакции: 603111, г. Нижний Новгород, ул. Раевского 15-45
Адрес учредителя: 603111, г. Нижний Новгород, ул. Раевского 15-45
Учредитель, главный редактор: Пашкова Екатерина Ивановна
Контакты: +7-920-0-777-397, info@pedsovet.su
Домен: http://pedsovet.su/
Копирование материалов сайта строго запрещено, регулярно отслеживается и преследуется по закону.

Отправляя материал на сайт, автор безвозмездно, без требования авторского вознаграждения, передает редакции права на использование материалов в коммерческих или некоммерческих целях, в частности, право на воспроизведение, публичный показ, перевод и переработку произведения, доведение до всеобщего сведения — в соотв. с ГК РФ. (ст. 1270 и др.). См. также Правила публикации конкретного типа материала. Мнение редакции может не совпадать с точкой зрения авторов.

Для подтверждения подлинности выданных сайтом документов сделайте запрос в редакцию.

сервис вебинаров

О работе с сайтом

Читать еще:  Как сохранить в эксель в ворд

Мы используем cookie.

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

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

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

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

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

«ТЕКСТ», «РУБЛЬ» и «ЗНАЧЕН».

Рассмотрим отдельно каждую функцию.

Применение функции «ТЕКСТ».

  • Выбираем ячейку, в которую хотим поместить текстовое значение;
  • Вызываем мастер функций;
  • Выбираем функцию «ТЕКСТ»;
  • Указываем ячейку, из которой берем значение;
  • Обязательно указываем формат (формат можно выбрать, открыв меню «формат ячеек», вкладка – (все форматы)), можно поставить формат «Общий»;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в текстовом формате

Применение функции «РУБЛЬ».

  • Выбираем ячейку, в которую хотим поместить текстовое значение;
  • Вызываем мастер функций;
  • Выбираем функцию «РУБЛЬ»;
  • Указываем ячейку, из которой берем значение;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в текстовом формате с обозначением «руб.»

Применение функции «ЗНАЧЕН».

  • Выбираем ячейку, в которую хотим поместить значение в формате числа;
  • Вызываем мастер функций;
  • Выбираем функцию «ЗНАЧЕН»;
  • Указываем ячейку, из которой берем текстовое значение;
  • Нажимаем «ОК»;
  • В указанную ячейку перенесено число в числовом формате.

Часто возникает необходимость в написании чисел прописью (словами), например, записать число «123», как «Сто двадцать три» .

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

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

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

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

1. Выделите столбец

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

2. Нажмите эту кнопку

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

3. Нажмите кнопку «Готово»

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

4. Задайте формат

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.

Примечание: Если вы по-прежнему видите формулы, которые не выводят числовые результаты, возможно, включен параметр Показать формулы. Откройте вкладку Формулы и отключите параметр Показать формулы.

Другие способы преобразования

С помощью функции ЗНАЧЕН можно возвращать числовое значение текста.

1. Вставьте новый столбец

Вставьте столбец рядом с ячейками, содержащими текст. В этом примере столбец E содержит числа, которые хранятся в виде текста. Столбец F является новым столбцом.

2. Примените функцию ЗНАЧЕН

В одной из ячеек нового столбца введите =ЗНАЧЕН() и укажите в скобках ссылку на ячейку, содержащую числа, которые хранятся в виде текста. В данном примере это ячейка E23.

3. Наведите указатель мыши

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

4. Щелкните и перетащите вниз

Щелкните и перетащите вниз, чтобы добавить формулу в другие ячейки. После этого вы можете использовать новый столбец или скопировать и вставить новые значения в исходный столбец. Вот как это сделать: Выделите ячейки с новой формулой. Нажмите клавиши CTRL+C. Щелкните первую ячейку в исходном столбце. На вкладке Главная щелкните стрелку рядом с кнопкой Вставить и выберите пункт Специальная вставка > Значения.

Читать еще:  Как в excel объединить 2 ячейки в одну

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

Выделите пустую ячейку, в которой нет этой проблемы, введите в нее цифру 1 и нажмите клавишу ВВОД.

Нажмите клавиши CTRL+C, чтобы скопировать ячейку.

Выделите ячейки с числами, которые сохранены как текст.

На вкладке Главная щелкните Вставить и выберите пункт Специальная вставка.

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

Нажмите клавиши CTRL+1 (или +1 на Mac). Выберите нужный формат.

Отключение зеленых треугольников

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

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

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

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

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

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

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

  1. Использовать меню кнопки «Ошибка». При выделении любой ячейки с ошибкой слева появляется соответствующий значок. Это и есть кнопка «Ошибка». Если навести на нее курсор, появится знак раскрывающегося меню (черный треугольник). Выделяем столбец с числами в текстовом формате. Раскрываем меню кнопки «Ошибка». Нажимаем «Преобразовать в число».
  2. Применить любые математические действия. Подойдут простейшие операции, которые не изменяют результат (умножение / деление на единицу, прибавление / отнимание нуля, возведение в первую степень и т.д.).
  3. Добавить специальную вставку. Здесь также применяется простое арифметическое действие. Но вспомогательный столбец создавать не нужно. В отдельной ячейке написать цифру 1. Скопировать ячейку в буфер обмена (с помощью кнопки «Копировать» или сочетания клавиш Ctrl + C). Выделить столбец с редактируемыми числами. В контекстном меню кнопки «Вставить» нажать «Специальная вставка». В открывшемся окне установить галочку напротив «Умножить». После нажатия ОК текстовый формат преобразуется в числовой.
  4. Удаление непечатаемых символов. Иногда числовой формат не распознается программой из-за невидимых символов. Удалим их с помощью формулы, которую введем во вспомогательный столбец. Функция ПЕЧСИМВ удаляет непечатаемые знаки. СЖПРОБЕЛЫ – лишние пробелы. Функция ЗНАЧЕН преобразует текстовый формат в числовой.
  5. Применение инструмента «Текст по столбцам». Выделяем столбец с текстовыми аргументами, которые нужно преобразовать в числа. На вкладке «Данные» находим кнопку «Текст по столбцам». Откроется окно «Мастера». Нажимаем «Далее». На третьем шаге обращаем внимание на формат данных столбца.

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

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

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

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

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

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

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

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

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

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

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

Похожие статьи

Ссылка на основную публикацию
Похожие публикации
Adblock
detector