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

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

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

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

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

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

Метод 1: форматирование с помощью контекстного меню

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

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

  3. Щелкните выделение правой кнопкой мыши и выберите элемент в появившемся меню. "Форматирование ячеек …" .
  4. Как преобразовать число в текст и обратно в Excel

  5. В открывшемся окне формы перейдите на вкладку "Число" если открыт в другом месте. В блоке настроек "Цифровые форматы" выберите элемент "Текст" . Чтобы сохранить изменения, нажмите кнопку " в порядке" внизу окна.
  6. Как преобразовать число в текст и обратно в Excel

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

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

  11. Чтобы завершить преобразование, необходимо дважды щелкнуть левой кнопкой мыши, чтобы поместить курсор в каждый элемент диапазона отдельно, и нажать клавишу Enter. Чтобы упростить задачу, вы можете использовать функциональную клавишу F2 вместо двойного щелчка.
  12. Как преобразовать число в текст и обратно в Excel

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

Метод 2: ленточные инструменты

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

  1. Выберите элементы, данные которых вы хотите преобразовать в текст. В разделе "начало" щелкните значок треугольника справа от поля, в котором отображается формат. Он в поле "Число" .
  2. Как преобразовать число в текст и обратно в Excel

  3. В открывшемся списке параметров форматирования выберите элемент "Текст" .
  4. Как преобразовать число в текст и обратно в Excel

  5. Затем, как и в предыдущем методе, последовательно поместите курсор на каждый элемент в диапазоне, дважды щелкнув левой кнопкой мыши или нажав клавишу F2, а затем нажав клавишу Enter.

Метод 3: используйте функцию

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

  1. Установите курсор на первый элемент диапазона, в котором будет отображаться результат преобразования. Щелкните значок "Вставить функцию" , что близко к формуле.
  2. Как преобразовать число в текст и обратно в Excel

  3. Откроется окно мастеров. В категории "Текст" выберите элемент "ТЕКСТ" . Затем нажмите кнопку "в порядке" .
  4. Как преобразовать число в текст и обратно в Excel

  5. Открывает окно аргументов оператора ТЕКСТ. Эта функция имеет следующий синтаксис:

    = ТЕКСТ (значение; формат)

    В открытом окне есть два поля, которые соответствуют этим аргументам: "Ценность" а также "Формат" .

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

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

    После ввода всех необходимых параметров нажмите на кнопку "в порядке" .

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

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

  9. Теперь вся серия наполнена необходимыми данными. Но это не все. Фактически, все элементы нового диапазона содержат формулы. Выделите эту область и щелкните значок "Копирование" , который находится в разделе "начало" на панели инструментов в группе "Буфер обмена" .
  10. Как преобразовать число в текст и обратно в Excel

  11. Кроме того, если вы хотите сохранить оба диапазона (исходный и преобразованный), не снимайте выделение с области, содержащей формулы. Щелкните его правой кнопкой мыши. Список контекстных действий начинается. Выбираем позицию "Специальная вставка" в ее . Из списка опций в открытом списке выберите "Значения и числовые форматы" .

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

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

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

  13. В каждом случае выбранный диапазон будет введен в текстовой форме. Если вы все же решите вставить в исходную область, тогда ячейки, содержащие формулы, можно очистить. Для этого выделите их, щелкните правой кнопкой мыши и выберите элемент "Очистить контент" .
Читать также:  Как обновить Навител на карту памяти

Метод 1. Преобразование с помощью значка ошибки

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

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

  3. Отображается список действий. Выберите значение " Преобразовать в число" .
  4. Как преобразовать число в текст и обратно в Excel

  5. В выбранном элементе данные будут немедленно преобразованы в цифровой формат.
Читать также:  Групповая политика в Windows 7

Метод 2: преобразование с помощью окна форматирования

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

  1. Выберите диапазон, содержащий числа в текстовой версии. Щелкните правой кнопкой мыши. В контекстном меню выберите пункт "Форматирование ячеек …" .
  2. Как преобразовать число в текст и обратно в Excel

  3. Окно форматирования запускается. Как и в прошлый раз, откройте вкладку "Число" . В группе "Цифровые форматы" вы должны выбрать значения, которые позволят нам преобразовать текст в число. Они включают элементы "Общее" а также "Числовой" . Что бы вы ни выбрали, программа будет рассматривать числа, введенные в ячейку, как числа. Сделайте выбор и нажмите кнопку. Если вы выбрали значение "Числовой" , в правой части окна позволит вам настроить числовое представление: установить количество десятичных знаков после десятичной точки, установить разделители между цифрами. После завершения настройки нажмите кнопку "в порядке" .
  4. Как преобразовать число в текст и обратно в Excel

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

Метод 3: преобразование с помощью панели инструментов панели инструментов

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

  1. Выберите диапазон, который нужно преобразовать. Откройте вкладку Главная на ленте. Щелкаем на поле с выбором формата в группе "Число" . Выберите элемент "Цифровой" или "Общее" .
  2. Как преобразовать число в текст и обратно в Excel

  3. Затем щелкните каждую ячейку в области, которая будет преобразована более одного раза, с помощью клавиш F2 и Enter.

Метод 4: примените формулу

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

  1. В пустой ячейке, параллельной первому элементу преобразуемого диапазона, поместите знак равенства (=) и двойной знак минус (-). Затем мы указываем адрес первого элемента преобразованного диапазона. Это дает двойное умножение на значение "-1" . Как известно, умножение "минус" s "минус" дает "плюс". Это означает, что в целевой ячейке мы получаем то же значение, что было изначально, но теперь в цифровом виде. Эта процедура называется двойным двоичным отрицанием.
  2. Как преобразовать число в текст и обратно в Excel

  3. Нажимаем клавишу Enter, получаем готовое значение. Чтобы применить эту формулу ко всем другим ячейкам в диапазоне, используйте маркер заполнения, который мы ранее применили к функции ТЕКСТ.
  4. Как преобразовать число в текст и обратно в Excel

  5. Теперь у нас есть диапазон, заполненный значениями формулы. Выберите его и нажмите кнопку "Копирование" в разделе "начало" или воспользуйтесь сочетанием клавиш Ctrl + C.
  6. Как преобразовать число в текст и обратно в Excel

  7. Выберите исходную область и щелкните ее правой кнопкой мыши. В активированном списке контекста перейдите к пунктам "Специальная вставка" а также "Значения и числовые форматы" .
  8. Как преобразовать число в текст и обратно в Excel

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

Способ 5: используйте специальную вставку

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

  1. Введите число в каждую пустую ячейку листа "1" . Затем выберите его и нажмите на знакомый значок "Копировать" на ленте.
  2. Как преобразовать число в текст и обратно в Excel

  3. Выберите область листа, которую нужно преобразовать. Щелкните его правой кнопкой мыши. В открывшемся меню дважды щелкните элемент "Специальное размещение" .
  4. Как преобразовать число в текст и обратно в Excel

  5. В специальном окошке вставки поместите переключатель в блок "Работа" на позиции "Умножение" . Затем нажмите кнопку "в порядке" .
  6. Как преобразовать число в текст и обратно в Excel

  7. После этого действия все значения выбранной области будут преобразованы в числовые значения. Теперь, если хотите, можете удалить номер "1" которые мы использовали для конверсии.
Читать также:  Ошибка исключение EFCreateError в модуле DSOUND dll на 000116C5

Метод 6: используйте инструмент "Текст столбца"

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

  1. Выберите фрагмент листа, содержимое которого вы хотите преобразовать. Откройте вкладку "Данные" . На панели инструментов в блоке "Работа с данными" нажмите на значок "Текст по столбцам" .
  2. Как преобразовать число в текст и обратно в Excel

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

  5. Во втором окне оставьте все остальное и нажмите на кнопку "Вперед".
  6. Как преобразовать число в текст и обратно в Excel

  7. Но после открытия третьего окна текстового мастера нужно нажать на кнопку "Подробнее" .
  8. Как преобразовать число в текст и обратно в Excel

  9. Отображается дополнительное окно для импорта текста. В поле "Цифровой и частичный сепаратор" установить точку, и в "Ограничитель делителя" — апостроф. Затем сделайте один щелчок по кнопке «ОК».
  10. Как преобразовать число в текст и обратно в Excel

  11. Вернитесь в третье окно мастера текста и нажмите «Готово».
  12. Как преобразовать число в текст и обратно в Excel

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

Метод 7: используйте макросы

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

  1. Переходим во вкладку «Разработчик». Щелкните значок на ленте «Visual Basic», которая находится в группе «Код».
  2. Как преобразовать число в текст и обратно в Excel

  3. Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:

    Sub Text_in_number ()
    Selection.NumberFormat = "Общее"
    Selection.Value = Selection.Value
    Конец подписки

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

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

  5. Выделите на листе фрагмент, который хотите преобразовать. Щелкните значок «Макросы», который находится на вкладке «Разработчик» в группе «Код».
  6. Как преобразовать число в текст и обратно в Excel

  7. Откроется окно макросов, записанных в вашей версии программы. Найдите макрос с именем «Text_in_number», выберите его и нажмите кнопку «Run».
  8. Как преобразовать число в текст и обратно в Excel

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

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

Урок: Как создать макрос в Excel

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

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?