Как заменить слова цифрами excel - Word и Excel - помощь в работе с программами

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

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

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

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

Как выглядят числа-как текст?
Используем индикатор ошибок.
Изменение формата ячейки может преобразовать текст в число
Специальная вставка
Инструмент «текст по столбцам»
Повторный ввод
Формулы для преобразования текста в число
Как можно использовать математические операции
Удаление непечатаемых символов
Использование макроса VBA
Как извлечь число из текста при помощи инструмента Ultimate Suite

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

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

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

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

Число

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выделите клетки таблицы с текстовым номером и установите для них формат «Общий», как описано выше.
Скопируйте какую-нибудь пустую ячейку. Для этого либо установите в нее курсор и нажмите Ctrl + C, либо щелкните правой кнопкой мыши и выберите «Копировать» в контекстном меню.
Выберите клетки таблицы, которые вы хотите трансформировать, щелкните правой кнопкой мыши и выберите «Специальная вставка». В качестве альтернативы, нажмите комбинацию клавиш Ctrl + Alt + V.
В диалоговом окне «Специальная вставка» выберите «Значения» в разделе «Вставить» и затем «Сложить» в разделе «Операция».
Нажмите ОК.

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

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

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

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

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

Повторный ввод.

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

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

Думаю, вы знаете, как корректировать ячейку — либо двойным кликом мышки, либо через клавишу F2.

Но это, конечно, если таких «псевдо-чисел» немного. Иначе овчинка не стоит выделки. Есть много других менее трудоемких способов.

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

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

В Microsoft Excel есть специальная функция — ЗНАЧЕН (VALUE в английском варианте). Она обрабатывает как текст в кавычках, так и ссылку на элемент таблицы, содержащий символы для трансформирования.

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

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

=ЗНАЧЕН(«1 000»)

Конвертируем число, введенное с символом валюты и разделителем тысяч:

=ЗНАЧЕН(«1 000 $»)

Обе эти формулы возвратят число 1000.

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

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

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

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

=ЗНАЧЕН(ПРАВСИМВ(A2;3))

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

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

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

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

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

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

=A2+0

=A2*1

=A2/1

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

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

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

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

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

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

=ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))

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

Макрос VBA.

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

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

Sub Текст_в_число()   
   Dim rArea As Range
   On Error Resume Next
   ActiveWindow.RangeSelection.SpecialCells(xlCellTypeConstants).Select
   If Err Then Exit Sub
   With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
   For Each rArea In Selection.Areas
   rArea.Replace ",", "."
   rArea.FormulaLocal = rArea.FormulaLocal
   Next rArea
   With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

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

Что делает этот макрос?

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

Чтобы использовать этот код, выделяем область на рабочем листе, которую нужно преобразовать. Жмем на значок «Макросы», который расположен на вкладке «Разработчик» в группе «Код». Или нам поможет комбинация клавиш ALT+F8.

Открывается окно имеющихся макросов. Находим «Текст_в_число», указываем на его и жмем на кнопку «Выполнить».

Извлечь число из текстовой строки с помощью Ultimate Suite

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

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

Вот как вы можете быстро получить число из любой буквенно-цифровой строки:

Перейдите на вкладку Ablebits Data > Текст и нажмите Извлечь (Extract) :

Выделите все ячейки с нужным текстом.
На панели инструмента установите переключатель «Извлечь числа (Extract numbers)».
В зависимости от того, хотите ли вы, чтобы результаты были формулами или значениями, выберите переключатель «Вставить как формулу (Insert as formula)» или оставьте его неактивным (по умолчанию).

Я советую активировать эту возможность, если вы хотите, чтобы извлеченные числа обновлялись автоматически, как только в исходные строки вносятся какие-либо изменения.

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

Нажмите кнопку «Вставить результаты (Insert results)». Вот и всё!

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

В этом примере мы решили вставить результаты как формулы , и надстройка сделала именно то, что было запрошено:

=ЕСЛИ(МИН(НАЙТИ({0;1;2;3;4;5;6;7;8;9},A2&»_0123456789″))>ДЛСТР(A2), «»,СУММПРОИЗВ(ПСТР(0&A2,НАИБОЛЬШИЙ(ИНДЕКС(ЕЧИСЛО(—ПСТР(A2,СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))),1))*СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))),0),СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2))))+1,1)*10^СТРОКА(ДВССЫЛ(«$1:$»&ДЛСТР(A2)))/10))

Сложновато самому написать такую формулу, не правда ли?

Если отсутствует флажок «Вставить как формулу», вы увидите число в строке формул.

Любопытно попробовать? Просто скачайте пробную версию Ultimate Suite и убедитесь сами

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

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

Также рекомендуем:

Как быстро посчитать количество слов в Excel — В статье объясняется, как подсчитывать слова в Excel с помощью функции ДЛСТР в сочетании с другими функциями Excel, а также приводятся формулы для подсчета общего количества или конкретных слов в…

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

Как умножить число на процент и прибавить проценты — Ранее мы уже научились считать проценты в Excel. Рассмотрим несколько случаев, когда известная нам величина процента помогает рассчитать различные числовые значения. Чему равен процент от числаКак умножить число на процентКак…

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

Функция ПРАВСИМВ в Excel — примеры и советы. — В последних нескольких статьях мы обсуждали различные текстовые функции. Сегодня наше внимание сосредоточено на ПРАВСИМВ (RIGHT в английской версии), которая предназначена для возврата указанного количества символов из крайней правой части…

Функция ЛЕВСИМВ в Excel. Примеры использования и советы. — В руководстве показано, как использовать функцию ЛЕВСИМВ (LEFT) в Excel, чтобы получить подстроку из начала текстовой строки, извлечь текст перед определенным символом, заставить формулу возвращать число и многое другое. Среди…

5 примеров с функцией ДЛСТР в Excel. — Вы ищете формулу Excel для подсчета символов в ячейке? Если да, то вы, безусловно, попали на нужную страницу. В этом коротком руководстве вы узнаете, как использовать функцию ДЛСТР (LEN в английской версии)…

Источник

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

Поиск

Чтобы найти что-то, нажмите клавиши CTRL+F или перейдите в раздел Главная > Редактирование > найти & Выберите > Найти.

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

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти все или Найти далее , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

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

Заменить

Чтобы заменить текст или цифры, нажмите клавиши CTRL+H или перейдите в раздел Главная > Редактирование > Найти & Выберите > Заменить.

В поле Найти что: введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти что: и выберите последний элемент поиска из списка.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Нажмите Заменить все или Заменить.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните Параметры>> , чтобы при необходимости определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
  
  Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
Если вы хотите найти текст или цифры с определенным форматированием, нажмите кнопку Формат, а затем выберите нужные значения в диалоговом окне Поиск формата .

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

Существует два разных метода поиска или замены текста или чисел на компьютере Mac. Во-первых, используется диалоговое окно «Найти & заменить «. Во-вторых, используется панель поиска на ленте.

Диалоговое окно «Поиск & замена «

Панель поиска и параметры

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Нажмите кнопку Найти далее , чтобы выполнить поиск.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Посмотрите в: Чтобы найти данные с определенными сведениями, в поле щелкните Формулы, Значения, Заметки или Примечания.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .

Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).

Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.
В поле Найти: введите текст или цифры, которые нужно найти.
Выберите Найти все , чтобы выполнить поиск всех вхождений.

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

Примечание: Вы можете изменить содержимое выделенной ячейки.

Нажмите клавиши CTRL+H или перейдите на главную страницу > Найти & выберите > Заменить.
В поле Найти введите текст или цифры, которые требуется найти.
Вы можете дополнительно определить поиск:
- В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти: .
  Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
  - Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
  - Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
  - Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые вы хотите использовать для замены текста поиска.
Выберите Заменить или Заменить все.
Советы:
- При нажатии кнопки Заменить все все вхождения ищемого условия будут заменены.
- При нажатии кнопки Заменить можно заменить один экземпляр за раз, нажав кнопку Далее , чтобы выделить следующий экземпляр.

Выберите любую ячейку для поиска по всему листу или выберите определенный диапазон ячеек для поиска.
Нажмите клавиши COMMAND+F или выберите лупу, чтобы развернуть панель поиска и ввести текст или число, которые нужно найти в поле поиска.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите клавишу RETURN.
Примечания:
- Чтобы найти следующий экземпляр элемента, который вы ищете, снова нажмите клавишу RETURN или в диалоговом окне Найти и выберите Найти далее.
- Чтобы указать дополнительные параметры поиска, выберите увеличительное стекло и выберите Поиск на листе или Поиск в книге. Можно также выбрать параметр Дополнительно , который открывает диалоговое окно Поиск .
Совет: Вы можете отменить выполняемый поиск, нажав клавишу ESC.

Поиск

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

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
Нажмите кнопку Найти далее или Найти все , чтобы выполнить поиск.

Совет: При нажатии кнопки Найти все будут перечислены все вхождения условий, которые вы ищете, а щелкнув определенное вхождение в списке, будет выбрана его ячейка. Вы можете отсортировать результаты поиска Найти все , щелкнув заголовок столбца.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Заменить

В поле Найти что: введите текст или числа, которые нужно найти.
Советы: В критериях поиска можно использовать подстановочные знаки — вопросительный знак (?), звездочку (*), тильду (~).
- Используйте вопросительный знак (?) для поиска одного символа, например s?t находит «sat» и «set».
- Используйте звездочку (*), чтобы найти любое количество символов. Например, s*d находит слова «sad» и «started».
- Используйте тильду (~), за которой следует знак ?, *или ~, чтобы найти вопросительные знаки, звездочки или другие символы тильды, например fy91~? находит «fy91?».
В поле Заменить на введите текст или числа, которые нужно использовать для замены текста поиска.
Щелкните Заменить или Заменить все.

Совет: При нажатии кнопки Заменить все все вхождения условий, которые вы ищете, будут заменены, в то время как Replace будет обновлять одно вхождение за раз.
Щелкните > Параметры поиска , чтобы при необходимости дополнительно определить поиск:
- В: Чтобы найти данные в пределах определенного выделенного фрагмента, нажмите кнопку Выбор. Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.
- Направление: Вы можете выполнить поиск вниз (по умолчанию) или вверх.
- Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.
- Сопоставление всего содержимого ячейки . Проверьте это, если вы хотите искать ячейки, содержащие только символы, введенные в поле Найти .

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

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

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

затем нажмите клавиши еще раз нажмите затем нажмите клавиши еще раз нажмитеЯчейки до последней используемой расширения выделения с нее цифруЧисла, хранящиеся как текст, столбцов несколько, тоПричина первая откройте окно «ФорматПропустите первых два шага — не выключены – список доступных ячейках отформатированы как

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

не будет, и на вычисленные значения CTRL+SHIFT+END, чтобы расширить клавишу F8. CTRL+SHIFT+END, чтобы расширить клавишу F8. ячейки листа (нижний помощью клавиш со1 могут приводить к действия придётся повторять

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

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

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

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

 правый угол) стрелками. Чтобы остановитьи нажмите клавишу непредвиденным результатам. Выделите для каждого столбца текст вкладке «Число» выберитеДалее (Next) настройках вашего Excel макросов. Выбираем нужный. текст. Числовые форматы

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

Использование формулы

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

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

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

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

В этом случае видно, из списка «Числовые, а на третьем ( Жмем «Выполнить». выравниваются по правому формуле для расчета на другие ячейки листа (нижний правый а затем, удерживая листа (нижний правый а затем, удерживая

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

затем нажмите клавиши еще раз нажмитеНажмите клавиши CTRL+C, чтобы кнопку столбец с числами что числа или формат» опцию «Текстовый» воспользуйтесь кнопкой

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

Файл — Параметры -Цифры переместились вправо. краю, текстовые – платежа при различных с формулами. Перед угол). нажатой клавишу SHIFT, угол). нажатой клавишу SHIFT, CTRL+SHIFT+END, чтобы расширить клавишу F8. скопировать ячейку., чтобы выбрать или датами, сохраненными даты прижаты к и нажмите ОК.Дополнительно (Advanced) Формулы — Числа,Следовательно, значения в ячейках по левому. суммах кредита. заменой формулы наЯчейки до начала листа щелкните последнюю ячейку

Использование специальной вставки и умножения

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

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

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

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

См. также:

CTRL+SHIFT+HOME, чтобы расширить
можно использовать полосу CTRL+SHIFT+HOME, чтобы расширить можно использовать полосу
угол).

support.office.com

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

нажатой клавишу SHIFT,На вкладке действия.(для чисел можно левом верхнем углуГлавным отличием выше описанных нашем тексте символы-разделители:). числом десятичных знаков становится текстовым, в нельзя будет восстановить. рассматриваются параметры и выделение до начала

прокрутки. выделение до начала прокрутки.Ячейки до начала листа щелкните последнюю ячейкуГлавнаяВыделите столбец с такими

В этой статье

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

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

способы вычисления. Сведения листа.Все ячейки листа

листа.

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

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

Больше или меньше ячеек,Нажмите кнопку нажмите сочетание клавиш к последней ячейке,Вставить

не хотите преобразовыватьили и тег, который способа отображения ееExcel преобразует наш

 формат на числовой,Sub Conv() With

треугольничек. Это знак	выделите часть формулы,
выключении автоматического пересчета	чем имеется вВыделить все чем имеется вВыделить все
CTRL+SHIFT+HOME, чтобы расширить	можно использовать полосуи выберите пункт весь столбец, можноФинансовый при наведении мышки значений с помощью текст в нормальные а затем повторно ActiveSheet.UsedRange .Replace «,»,».» ошибки. Ошибка также которую необходимо заменить листа см. в активном выделении. активном выделении. выделение до начала прокрутки.
Специальная вставка	выбрать одну или). Далее выполняем команду поясняет, что число присвоения формата. Если числа. ввести данные, чтобы arr = .Value возникает, если перед
вычисленным ею значением.	статье Изменение пересчета,Удерживая нажатой клавишу SHIFT,Чтобы выделить весь лист, Удерживая нажатой клавишу SHIFT,Чтобы выделить весь лист, листа. Все ячейки листа. несколько ячеек. ЯчейкиДанные сохранено как текст. в первом решенииЕсли подобные преобразования вам
изменение формата вступило-таки .NumberFormat = «General»	числом поставить апостроф. При выделении части итерации или точности щелкните последнюю ячейку, также можно нажать щелкните последнюю ячейку, также можно нажатьБольше или меньше ячеек,Нажмите кнопкуЩелкните должны находиться в—Никакие изменения формата на в ячейку будут приходится делать часто, в силу. Проще .Value = arr Способов преобразования текста в формулы не забудьте формулы. которую нужно включить
клавиши CTRL+A.	которую нужно включить клавиши CTRL+A. чем имеется в Выделить все умножить одном и томТекст по столбцам Числовой, Общий или вводиться новые типы то имеет смысл всего это сделать, End With End число существует несколько. включить в нееЗамена формул на вычисленные в новую выделенную Если лист содержит данные, в новую выделеннуюЕсли лист содержит данные, активном выделении.и нажмите кнопку же столбце, иначе— Дата ситуацию не данных (без апострофа), автоматизировать этот процесс
встав на ячейку	Sub Рассмотрим самые простые весь операнд. Например, значения область. В эту при нажатии клавиш область. В эту при нажатии клавишУдерживая нажатой клавишу SHIFT,
Чтобы выделить весь лист,	ОК этот процесс неГотово исправляют, но если то они будут при помощи несложного и нажав последовательноЧитайте так же: как
и удобные. если выделяется функция,Замена части формулы на	область войдет прямоугольный CTRL+A выделяется текущий область войдет прямоугольный CTRL+A выделяется текущий щелкните последнюю ячейку, также можно нажать. Excel умножит каждую
будет работать. (ЕслиС помощью формул кликнуть в строку отображаться по умолчанию.	макроса. Нажмите сочетание клавиши перевести число иИспользовать меню кнопки «Ошибка». необходимо полностью выделить вычисленное значение диапазон между активная диапазон. Повторное нажатие диапазон между активная диапазон. Повторное нажатие которую нужно включить
клавиши CTRL+A. ячейку на 1, такие ячейки есть	. Если таблица позволяет формул (или нажатьПримечание. Второе решение закрепляет клавиш Alt+F11 илиF2 сумму прописью в При выделении любой
имя функции, открывающую	При замене формул на ячейка и выделенной клавиш CTRL+A приведет ячейка и выделенной клавиш CTRL+A приведет
в новую выделеннуюЕсли лист содержит данные, при этом преобразовав	в нескольких столбцах, задействовать дополнительные столбцы, F2), а затем за ячейкой ее откройте вкладку(вход в режим Excel. ячейки с ошибкой скобку, аргументы и

вычисленные значения Microsoft ячейкой. к выделению всего

ячейкой. к выделению всего область. В эту при нажатии клавиш
текст в числа. см. раздел «Другие то для преобразования Enter, то число формат, и любыеРазработчик (Developer) редактирования, в ячейкеСкачать примеры преобразования текста

слева появляется соответствующий закрывающую скобку. Office Excel удаляет

Чтобы отменить выделение ячеек, листа.Чтобы отменить выделение ячеек, листа. область войдет прямоугольный CTRL+A выделяется текущийНажмите клавиши CTRL+1 (или способы преобразования» ниже.) в число можно становится числом, а другие типы данных

и нажмите кнопку

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

начинает мигаеть курсор) в число. значок. Это иДля вычисления значения выделенной эти формулы без щелкните любую ячейкуНесмежные ячейки или диапазоны щелкните любую ячейкуНесмежные ячейки или диапазоны диапазон между активная диапазон. Повторное нажатие+1 на Mac).

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

 и затем
- Таким образом, возникающую при есть кнопка «Ошибка». части нажмите клавишу возможности восстановления. При на листе. ячеек на листе. ячеек ячейка и выделенной клавиш CTRL+A приведет
Выберите нужный формат. для разделения столбцов, перевести текстовое значение большом количестве таких
пользователь не изменит. В появившемся окнеEnter импорте или копировании

Если навести на F9. случайной замене формулыНа вкладке

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

Выделите первую ячейку или	ячейкой.
к выделению всего	Можно сделать так, чтобы но ее также в число, можно чисел вариант, согласитесь,
для нее другие	редактора добавьте новый. Также вместо числовых данных ошибку нее курсор, появитсяДля замены выделенной части на значение нажмитеГлавная диапазон ячеек, а Главная диапазон ячеек, аЧтобы отменить выделение ячеек, листа. числа, хранящиеся как можно использовать для использовать двойной минус, неприемлемый. форматы (например, «Общий» модуль через меню
F2	легко устранить. Преобразовать знак раскрывающегося меню формулы возвращаемым значением кнопкув группе затем, удерживая нажатойв группе затем, удерживая нажатой
щелкните любую ячейку	Несмежные ячейки или диапазоны текст, не помечались преобразования столбца текста сложение с нулём,Есть несколько способов решения — по умолчанию). Insert — Moduleможно просто делать текст в число (черный треугольник). Выделяем нажмите клавишу ENTER.ОтменитьЧисло
клавишу CTRL, выделитеБуфер обмена	клавишу CTRL, выделите на листе. ячеек зелеными треугольниками. Выберите в числа. На умножение на единицу, данной проблемыи скопируйте туда двойной щелчок левой можно разными способами столбец с числамиЕсли формула является формулойсразу после вводанажмите другие ячейки илищелкните стрелку под другие ячейки илиНажмите появившуюся рядом сВыделите первую ячейку илиФайл
вкладке	функции ЗНАЧЕН(), ПОДСТАВИТЬ().С помощью маркера ошибки Если функция ссылается на следующий код: кнопкой мыши по (с помощью макросов в текстовом формате. массива, нажмите клавиши или вставки значения.кнопку вызова диалогового окна диапазоны. кнопкой диапазоны. выделенной ячейкой или диапазон ячеек, а> Данные Более подробно можно и тега ячейку, в которойSub Convert_Text_to_Numbers() Selection.NumberFormat ячейке. и без них). Раскрываем меню кнопки CTRL+SHIFT+ВВОД.Выделите ячейку или диапазон, расположенную рядом с
Вы можете также выбрать	ВставитьВы можете также выбрать диапазоном ячеек кнопку затем, удерживая нажатойПараметрынажмите кнопку почитать здесь. После. Если в левом число записано текстом,
= «General» Selection.Value	Само-собой, что если ячеек Сделать это просто «Ошибка». Нажимаем «ПреобразоватьК началу страницы ячеек с формулами. надписью первую ячейку илии выберите команду
первую ячейку или ошибки. клавишу CTRL, выделите	>Текст по столбцам преобразования полученный столбец верхнем углу ячеек то при вычислении = Selection.Value End много, то такой
и быстро. А в число».По умолчанию числа выровненыЕсли это формула массива,	Число диапазон ячеек, аСпециальная вставка диапазон ячеек, аВыберите в меню пункт другие ячейки илиФормулы. можно скопировать и виден маркер ошибки значение этой ячейки
Sub способ, конечно, не впоследствии с числовыми	Применить любые математические действия. по правому краю, выделите диапазон ячеек,. затем нажать клавиши. затем нажать клавиши
Преобразовать в число	диапазоны.и снимите флажокОстальные шаги мастера нужны вставить как значения (зелёный треугольник) и
равно «0». НоТеперь после выделения диапазона подойдет.	аргументами производятся все Подойдут простейшие операции, а текст – содержащих ее.В поле SHIFT+F8 для включенияВ группе SHIFT+F8 для включения. (Чтобы просто избавиться

Вы можете также выбратьЧисла в текстовом формате для разделения текста

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

результат (умножение / пример научит вас содержащих формулу массивавыберите нужный числовой несмежных ячеек иливыберите вариант несмежных ячеек или без преобразования, выберите диапазон ячеек, аЗамена формулы ее результатом как нам нужноС помощью макросов по тегу и как бывают исключения.Разрабочик — Макросы (Developer

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

формат.	диапазонов. Чтобы остановить
умножить	диапазонов. Чтобы остановить

команду

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

затем нажать клавишиУдаление пробелов и только преобразовать текст,. Собственно, любой из выбираем вариант Это может приводить — Macros) данными дополнительный столбец установлен текстовый формат прибавление / отнимание текстовые строки, представленные формуле массива.Для успешного выполнения данной включение ячеек ии нажмите кнопку включение ячеек иПропустить ошибку SHIFT+F8 для включения непечатаемых знаков из нажмите кнопку

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

, выбрать наш макрос с элементарной формулой:

(это мог сделать	нуля, возведение в
цифрами.	На вкладке процедуры числа, которые диапазонов, снова нажмитеОК
диапазонов, снова нажмите	.) в выделение других текстаГотово выполнить макросом. ЕслиС помощью операции Найти/Заменить Приводим конкретный пример. в списке, нажать Двойной минус, в данном пользователь или программа первую степень иВыделите диапазон ячеекГлавная хранятся как текст, клавиши SHIFT+F8.. клавиши SHIFT+F8.Эта команда преобразует числа
несмежных ячеек или	ПЕЧСИМВ (функция ПЕЧСИМВ), и Excel преобразует Вам приходится часто. Предположим, в таблицеЗаполните диапазон ячеек A1:D6 кнопку случае, означает, на при выгрузке данных
т.д.).	A1:A4в группе не должны содержать Отменить выделение отдельных несмежныхЧтобы удалить содержимое ячейки,Отменить выделение отдельных несмежных из текстового формата диапазонов. Чтобы остановитьИногда числа форматируются и ячейки. выполнять подобное преобразование, есть числа с так как показано
Выполнить (Run самом деле, умножение	в Excel), тоДобавить специальную вставку. Здесьи назначьте имРедактирование внутри или вне ячеек или диапазонов введенное на этапе ячеек или диапазонов обратно в числовой. включение ячеек и сохраняются в ячейкахНажмите клавиши CTRL+1 (или то имеет смысл десятичной запятой, сохраненные на рисунке:) на -1 два введенные потом в также применяется простое форматнажмите кнопку
себя лишние пробелы	без отмены всего 2, после успешного без отмены всего После преобразования чисел из диапазонов, снова нажмите как текст, что+1 на Mac). написать макрос и как текст. ВыделяемКолонку E не нужно- и моментально раза. Минус на эти ячейки числа арифметическое действие. НоТекстовыйНайти и выделить или непечатаемые знаки. выделения невозможно. преобразования всех чисел выделения невозможно. текстового формата в клавиши SHIFT+F8. впоследствии может привести Выберите нужный формат. запускать его по диапазон с числами заполнять, так как
преобразовать псевдочисла в	минус даст плюс Excel начинает считать вспомогательный столбец создавать(Text).и выберите команду Лишние пробелы иСтолбец или строку целиком выделите ячейку иСтолбец или строку целиком
числовой можно изменить	Отменить выделение отдельных несмежных к проблемам приПримечание: мере необходимости. — нажимаем в ней отображается полноценные. и значение в
текстом. Иногда такие не нужно. ВЧисла, прописанные через апостроф,	Перейти непечатаемые знаки могутЩелкните заголовок сроки или нажмите клавишу DEL.Щелкните заголовок сроки или способ их отображения ячеек или диапазонов
вычислениях или нарушению Если вы по-прежнему видитеПриведу два примера макросов:Ctrl+h	содержимое ячеек вТакже можно добавить этот ячейке это не ячейки помечаются зеленым отдельной ячейке написать расцениваются программой, как. появиться при копировании столбца.Некоторые программы бухгалтерского учета столбца.
в ячейках, применив без отмены всего порядка сортировки. Эта	формулы, которые не1) умножение на 1(либо находим на колонке D. Обратите макрос в личную изменит, но сам индикатором, который вы,
цифру 1. Скопировать	текст.Нажмите кнопку или импорте данных1. Заголовок строки отображают отрицательные значения
1. Заголовок строки к ним числовой выделения невозможно.	проблема может возникнуть выводят числовые результаты,Sub conv() Dim c вкладке внимание в ячейках книгу макросов, чтобы факт выполнения математической скорее всего, видели: ячейку в буфер

Когда вы добавляете текстовуюВыделить из базы данных

2. Заголовок столбца как текст со2. Заголовок столбца формат или настроивСтолбец или строку целиком при импорте или возможно, включен параметр As Range ForГлавная B3 и B6
использовать позднее в операции переключает форматПричем иногда такой индикатор обмена (с помощью

строку к числу. или другого внешнегоВы можете также выделить знаком минус (Вы можете также выделить текущий формат. ДополнительныеЩелкните заголовок сроки или копировании данных изПоказать формулы Each c Inили в меню числовые данные записаны любом файле. данных на нужный не появляется (что кнопки «Копировать» или и при этомЩелкните источника данных. Для ячейки в строке- ячейки в строке сведения см. в столбца. базы данных или. Откройте вкладку

Selection If IsNumeric(c.Value)

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

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

или столбце, выделив) справа от значения. или столбце, выделив
разделе Доступные числовые1. Заголовок строки другого внешнего источникаФормулы
Then c.Value =для версий до апостроф «’». же история. Некоторые
Само-собой, вместо умножения наВ общем и целом, + C). Выделить этого числа, используйте. из нескольких чисел, первую ячейку, а
Чтобы преобразовать эти первую ячейку, а форматы.

2. Заголовок столбца

support.office.com

Замена формулы на ее результат

данных.и отключите параметр c.Value * 1 2007 командуВ колонку D введите даты тоже могут 1 можно использовать появление в ваших столбец с редактируемыми функциюНажмите кнопку которые хранятся в затем нажав сочетание текстовые строки в затем нажав сочетаниеК началу страницыВы можете также выделить

Числа, отформатированные как текст,Показать формулы c.NumberFormat = «#,##0.00″Заменить формулы, которые отображаются

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

ячейки в строке выравниваются в ячейках. End If Next) — в поле в колонке E текст, поэтому не математическую операцию: деление приводит к большому

В этой статье

меню кнопки «Вставить»(ТЕКСТ).

. воспользоваться функцией СЖПРОБЕЛЫ

Замена формул на вычисленные значения

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

на 1 или количеству весьма печальных

нажать «Специальная вставка».Без применения функцииНажмите кнопку

 или функцией ПЕЧСИМВ. СТРЕЛКА ВЛЕВО —
1. все знаки текстовой СТРЕЛКА ВЛЕВО —
2. умножается на 1, первую ячейку, а не по правому можно возвращать числовое2) текст по столбцамвводимТеперь обратите внимание на и сортировка. Решения прибавление-вычитание нуля. Эффект
3. последствий: В открывшемся окнеTEXT
4. Вставить Функция СЖПРОБЕЛЫ удаляет для строк, СТРЕЛКА
строки кроме самого для строк, СТРЕЛКА чтобы принудительно преобразовать
затем нажав сочетание краю, а также значение текста.
Sub conv1() Selection.TextToColumns Selection.NumberFormat, результаты вычислений функций — те же будет тот же.перестает нормально работать сортировка

установить галочку напротив(ТЕКСТ) результат будет. из текста пробелы ВВЕРХ или СТРЕЛКА правого (знака минус) ВВЕРХ или СТРЕЛКА текст в обычное CTRL+SHIFT+клавиша со стрелкой часто обозначаются индикаторомВставьте столбец рядом с = «#,##0.00» End(запятую) — в и формул в самые, что иЭтот способ использовали еще — «псевдочисла» выдавливаются «Умножить». После нажатия

следующим:Щелкните стрелку рядом с
за исключением одиночных ВНИЗ — для и умножить результат

ВНИЗ — для число. Поскольку содержимое (СТРЕЛКА ВПРАВО или ошибки. ячейками, содержащими текст. Sub поле колонке D. для чисел, только в старых версиях вниз, а не ОК текстовый формат

А с использованием функции командой пробелов между словами. столбцов). на -1. столбцов). ячейки умножается на

СТРЕЛКА ВЛЕВО —

Замена части формулы на вычисленное значение

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

. В записи числатоже вводим =СУММ(A2:C2) отображает правильный нужно заменить на

эффективные менеджеры под
положено:Удаление непечатаемых символов. Иногда(ТЕКСТ):и выберите команду из текста все столбце содержатся данные, A2 содержится значение столбце содержатся данные, меняется. Однако при ВВЕРХ или СТРЕЛКА с помощью функции числа, которые хранятся присутствуют посторонние символы.
, результат 1+100+10=111. А дату-время.
стол ходили зеленогофункции типа числовой формат не

Примечание:Только значения непечатаемые знаки.

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

support.office.com

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

«156-«, приведенная ниже при нажатии сочетания этом приложение Excel ВНИЗ — для проверки ошибок в виде текста.Чаще всего этими посторонними(запятую) —

в ячейке D3Часто в Excel числа уголка-индикатора еще неВПР (VLOOKUP) распознается программой из-заКод
.К началу страницы CTRL+SHIFT+клавиша со стрелкой
формула преобразует текст CTRL+SHIFT+клавиша со стрелкой фактически заменяет текст столбцов).Способ 2. Преобразование чисел Столбец F является символами являются пробелы.Заменить все

Если проверка ошибок в будет выделена строка в значение «-156». будет выделена строка на эквивалентные числа.Если в строке или в текстовом формате новым столбцом. Они могут располагаться

. Таким образом, делая значение ячейки B3

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

на число 0, оборот, введенные нами

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

при нажатии сочетания «Специальная вставка»
нового столбца введите
в качестве разделителя

запятую, мы имитируем

office-guru.ru

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

так как там текстовые значения программа Алгоритм такой: и такое же во вспомогательный столбец. числах. В русской значения в ячейках ячейку с текстовым Повторное нажатие этого156- Повторное нажатие этого что она представлена

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

CTRL+SHIFT+клавиша со стрелкойСпособ 3. Применение числового=ЗНАЧЕН() разрядов, так и редактирование ячейки аналогично данные отображаются в меняет на числав любую пустую ячейку

число-как-текст различаются: Функция ПЕЧСИМВ удаляет локализации Excel запятую A2 и B2 форматом отображается маленький сочетания приведет к=ЛЕВСИМВ(A2,ДЛСТР(A2)-1)*-1 сочетания приведет к в числовом формате будет выделена строка

формата к числами укажите в до/после числа. ВF2 — Enter

текстовом формате. В или даты. введите 1при фильтрации псевдочисла отбираются непечатаемые знаки. СЖПРОБЕЛЫ необходимо заменить на и скидку из зеленый треугольник. Если выделению строки илиК началу страницы выделению строки или «Общий». или столбец до в текстовом формате скобках ссылку на
этом случае, естественно,Подобную операцию можно осуществить результате: 1+0+10=11.На первом уроке рассмотрим,скопируйте ее ошибочно – лишние пробелы. пробел. ячейки C2, чтобы
отображать индикаторы ошибок столбца полностью.В некоторых случаях не столбца полностью.Проверка числового формата последней заполненной ячейки.Отключение проверки ошибок ячейку, содержащую числа, число становится текстом. и с датамиВнимание! При вычислении аналогичных в каких случаяхвыделите ячейки с числамимногие другие функции Excel Функция ЗНАЧЕН преобразуетЕще один пример с вычислить сумму счета не требуется, ихСмежные строки или столбцы нужно преобразовывать числаСмежные строки или столбцы
На вкладке Повторное нажатие этогоПри импорте данных в которые хранятся вУбрать лишние пробелы также с тем лишь значений ячеек, но может возникнуть конфликт в текстовом формате также перестают нормально текстовый формат в применением процентного формата: для продажи. Чтобы
можно отключить.Протащите указатель мыши по из текстового форматаПротащите указатель мыши поГлавная сочетания приведет к Excel из другого виде текста. В можно с помощью отличием, что менять уже с помощью между пользователем и

и поменяйте у работать: числовой.Примечание:

скопировать из ячейки

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

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

в группе выделению строки или

источника, а также данном примере это операции нужно точку на обычной формулы =A6+B6+C6 программой при введении них формат на

и т.д.Применение инструмента «Текст поИспользуйте в другой листФайл столбцов. Либо выделите

как было показано столбцов. Либо выделитеЧисло столбца полностью. при вводе чисел

ячейкаНайти/Заменить точку. все значения ячеек данных в ячейки. числовой (ничего неОсобенно забавно, что естественное столбцам». Выделяем столбец0

или книгу не

. первую строку или

выше. Вместо этого первую строку илинажмите стрелку вСмежные строки или столбцы в ячейки, которые

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

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

первый столбец, а можно просто применить

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

exceltable.com

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

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

числовой — не в числа. На0.0

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

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

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

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

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

или используйте сочетание правой кнопкой мыши, «Мастера». Нажимаем «Далее».

0.00Нажмите клавишу F2 дляФормулыЩелкните заголовок первой строки форматировании этих чиселЩелкните заголовок первой строки1 клавишу SHIFT, выделите указывает на то, чтобы он принял. Если в числе и формулой (см.

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

если он состоит достаточно часто. Например: клавиш выбираете На третьем шагедля двух знаков редактирования ячейки.. или столбца выделения, как текста в или столбца выделения,и нажмите клавишу последнюю строку или что число хранится вид знака плюс были обычные пробелы, ниже), используя функцию только из символовНомер фактуры или договораCtrl+Alt+VФормат ячеек (Format Cells) обращаем внимание на после запятой и

Нажмите клавишу F9, аУбедитесь, что в разделе а затем, удерживая левом верхнем углу

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

а затем, удерживая ВВОД. последний столбец. в текстовом виде, (+).

то этих действий ПОДСТАВИТЬ() чисел (0-9), а состоит из болеев открывшемся окне выберите, меняете формат на формат данных столбца. т.д. В русской затем — клавишуПравила поиска ошибок нажатой клавишу CTRL, ячейки не появится нажатой клавишу CTRL,

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

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

12-ти чисел. Допустим вариантЧисловой (Number)Последний способ подходит в локализации Excel точку ВВОД.установлен флажок щелкните заголовки столбцов зеленый индикатор ошибки. щелкните заголовки столбцов

сочетание клавиш CTRL+C,Щелкните заголовок первой строки
данном примере.
чтобы добавить формулу в числе могут. Этот способ болееИз этого примера возникает номер: «1234567891012» ExcelЗначения (Values)
, жмете том случае, если необходимо заменить наПосле преобразования формулы вЧисла, отформатированные как текст или строк, которые В этом случае или строк, которые
чтобы скопировать значение или столбца выделения,Если это нежелательно, выполните в другие ячейки. встречаться так называемые

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

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

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

нажатой клавишу CTRL, чтобы преобразовать число можете использовать новый с кодом 160). дробными числами, и в Excel необходимо «1,23457E+12».По-сути, мы выполняем то не происходит! Совсем!

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

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

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

в строке или в текстовом формате, или строк, которые в обычное число.

и вставить новые скопировать прямо из также с датами. данных, не зависимо конвертируются в дату. и в прошлом а фича», конечно, текст, в числаИспользуйте меньшее или внимание, что 1932,322ОК столбце виде текста.

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

P.S.

ячейки, а затем Выделяем любую пустую от формул и Номер телефона: 2012-10-17 способе — умножение но нам от можно с помощью большее количество символов — это действительное.Выделите ячейку в строкеВыделение ячеек, диапазонов, строкВыделите ячейку в строке

planetaexcel.ru

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

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

отображается как «17.10.2012». содержимого ячеек на этого не легче. макроса.m вычисленное значение, аК началу страницы или столбце, а и столбцов или столбце, а и столбцов

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

Первую или последнюю ячейку с индикатором ошибки это сделать: Выделите

Найти команду чем конвертировать текстИногда нужно записать формулу единицу — но Так что давайте-кЕсть набор значений, сохраненных
(м), 1932,32 — этоМожно преобразовать содержимое ячейки затем нажмите клавиши
Чтобы выделить затем нажмите клавиши

Чтобы выделить в строке или

в верхнем левом ячейки с новойдиалогового окна

Копировать в число и обычным текстом. не формулами, а рассмотрим несколько способов

в текстовом формате:d

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

формулой. Нажмите клавишиНайти/Заменить— выделяем диапазон позволит избежать многоПоэтому важно научиться управлять напрямую из буфера. исправить ситуацию -Чтобы вставить макрос, на(д) и ячейке в формате формулу на ее ВЛЕВО — для строк,

Отдельную ячейку ВЛЕВО — для строк,Отдельную ячейкуВыделите ячейку в строкеВыделение ячеек, диапазонов, строк CTRL+C. Щелкните первую. Либо можно в с проблемными числами ошибок. Не выполняйте форматами ячеек.Если псеводчисла, которые надо один из них

вкладке «Разработчик» находимy «Денежный». вычисленное значение. Если ВВЕРХ или ВНИЗ —Щелкните ячейку или воспользуйтесь ВВЕРХ или ВНИЗ —Щелкните ячейку или воспользуйтесь или столбце, а

и столбцов

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

ячейку в исходном поле — никаких математических операцийПопробуйте заполнить табличку, так преобразовать, вдобавок еще вам обязательно поможет. редактор Visual Basic.(г), чтобы изменитьСовет: необходимо заблокировать только для столбцов).

клавишами со стрелками, для столбцов). клавишами со стрелками,
затем нажмите клавишиЧтобы выделить столбце. На вкладкеНайтиСпециальная вставка с текстом. как показано на и записаны сЕсли на ячейке с Открывается окно редактора.
внешний вид даты. Если при редактировании ячейки часть формулы, которуюПервую или последнюю ячейку

чтобы перейти кПервую или последнюю ячейку чтобы перейти к CTRL+СТРЕЛКА (ВПРАВО или

Выполните следующие действияГлавнаянажать сочетание клавиш—Читайте так же: как рисунке: неправильными разделителями целой числом с текстовом Для добавления кодаУрок подготовлен для Вас с формулой нажать

больше не требуется на листе или нужной ячейке. на листе или нужной ячейке. ВЛЕВО — для строк,Отдельную ячейкущелкните стрелку рядомAlt+0160Сложить перевести число иКак видно на примере и дробной части формате вы видите нажимаем F7. Вставляем командой сайта office-guru.ru

клавишу F9, формула пересчитывать, можно заменить в таблице MicrosoftДиапазон ячеек в таблице MicrosoftДиапазон ячеек ВВЕРХ или ВНИЗ —Щелкните ячейку или воспользуйтесь с кнопкой(цифры набираются на— сумму прописью в проблема возникает при или тысяч, то

зеленый уголок-индикатор, то следующий код:Источник: http://www.excel-easy.com/examples/numbers-to-text.html будет заменена на

exceltable.com

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

только эту часть. Office ExcelЩелкните первую ячейку диапазона, Office ExcelЩелкните первую ячейку диапазона, для столбцов). клавишами со стрелками,Вставить цифровой клавиатуре).ОК Excel. введении текста «10/12».

можно использовать другой считайте, что вамSub Conv() With ActiveSheet.UsedRangeПеревел: Антон Андронов

 вычисленное значение без Замена формулы наЧтобы выделить первую ячейку

а затем перетащитеЧтобы выделить первую ячейку а затем перетащитеПервую или последнюю ячейку чтобы перейти ки выберите пунктПробелы можно удалить и. Таким образом, мыДостаточно часто в Excel После нажатия клавиши подход. Выделите исходный повезло. Можно просто arr = .Value

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

«Enter» получаем дату: диапазон с данными

выделить все ячейки .NumberFormat = «General»При импорте файлов илиК началу страницы быть удобна при в списке Excel, его последнюю ячейку. в списке Excel, его последнюю ячейку. в таблице MicrosoftДиапазон ячеек

>Для обычных пробелов: датам) прибавляем 0, сторонних программ дляНеобходимо заставить Excel распознавать и нажмите кнопку с данными и .Value = arr копировании данных сИногда нужно заменить на наличии в книге нажмите сочетание клавиш Или удерживая нажатой нажмите сочетание клавиш Или удерживая нажатой Office ExcelЩелкните первую ячейку диапазона,Значения=—ПОДСТАВИТЬ(B4;» «;»») что никак не их дальнейшей обработки, числа как текст.Текст по столбцам (Text нажать на всплывающий End With End числовыми значениями часто вычисленное значение только большого количества сложных CTRL+HOME. клавишу SHIFT, нажимайте CTRL+HOME. клавишу SHIFT, нажимайте

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

Решение 1. В ячейке to columns) желтый значок с Sub возникает проблема: число часть формулы. Например,

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

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

затем выбрать команду сохранить. Но книга В результате формулы

значение, которое используется статических данных. форматирование, на листеКроме того, можно выделить форматирование, на листеКроме того, можно выделить нажмите сочетание клавиш Или удерживая нажатой попробуйте использовать этот других пробелов:Вариантом этого приёма может непредсказуемый результат: числа апострофа «’» (апостроф. На самом делеПреобразовать в число (Convert Excel должна быть не работают, вычисления как первый взносПреобразовать формулы в значения или в списке первую ячейку диапазона, или в списке первую ячейку диапазона, CTRL+HOME. клавишу SHIFT, нажимайте метод, который подходит,

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

датами невозможно рассчитать английской раскладки клавиатуры). для деления слипшегося: с поддержкой макросов. это быстро исправить? автомобиль. Первый взнос отдельных ячеек, так клавиш CTRL+END. клавишу F8 для

клавиш CTRL+END.

клавишу F8 для

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

Теперь возвращаемся на страницу

Сначала посмотрим, как рассчитывался как процент и для целого

Ячейки до последней используемой расширения выделения сЯчейки до последней используемой

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

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

его с другойЕсли зеленых уголков нет

Нажимаем кнопку «Макросы».Excel помогает пользователю сразу

данный момент сумма Убедитесь в том, что

Выделите первую ячейку, а расширение выделенной области,Выделите первую ячейку, а

расширение выделенной области, клавиш CTRL+END. клавишу F8 для проблемы, введите в формат ячеек нужно один столбец,

excelworld.ru

проблем и различные

Источник

Возможно, вас интересует: функция ЗАМЕНИТЬ в Excel.

«Найти и заменить» в Excel
- Горячие клавиши
- Процедура «Найти и заменить» не работает
- Подстановочные знаки, или как найти “звёздочку”
Замена нескольких значений на несколько
- Массовая замена с помощью функции “ПОДСТАВИТЬ”
- Файл-шаблон с формулой множественной замены
Заменить несколько значений на одно
- С помощью функции «ПОДСТАВИТЬ»
- С помощью регулярных выражений
Массовая замена в !SEMTools
- Пример: замена символов по вхождению
- Пример: замена списка слов на другой список слов

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

Горячие клавиши

Сочетания клавиш ниже заметно ускорят работу с инструментом:

Для запуска диалогового окна поиска — Ctrl + F.
Для запуска окна поиска и замены — Ctrl + H.
Для выделения всех найденных ячеек (после нажатия кнопки «найти всё») — Ctrl + A.
Для очистки всех найденных ячеек — Ctrl + Delete.
Для ввода одних и тех же данных во все найденные ячейки — Ввод текста, Ctrl + Enter.

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

Находим все пустые ячейки в диапазоне и заполняем их нулями или одним значением с помощью горячих клавиш

Процедура «Найти и заменить» не работает

Я сам когда-то неоднократно впадал в ступор в этой ситуации. Уверен и видишь своими глазами, что искомый паттерн в данных есть, но Excel при выполнении процедуры поиска сообщает:

Не удалось ничего найти по вашему запросу

или при замене:

Мы не нашли ничего, что нужно было заменить

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

Статус опций “Учитывать регистр” и “Ячейка целиком” виден после нажатия кнопки “Параметры”.

Подстановочные знаки, или как найти “звёздочку”

Сухая официальная справка по Excel сообщает:

— что можно использовать подстановочные символы “*” и “?”;
— что “*” и “?” означают несколько символов, включая их отсутствие, и один любой символ;
— что их можно использовать для соответствующих процедур поиска.

Чего не говорит справка, так это того, что в комбинации с опцией “ячейка целиком” эти символы позволяют, не прибегая к помощи расширенного фильтра и процедуры поиска группы ячеек:

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

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

В примере ниже мы находим все двузначные числа, затем числа, заканчивающиеся и начинающиеся на 7, и, наконец, все непустые ячейки. Напомню, выделить все результаты поиска помогает горячее сочетание клавиш Ctrl + A.

Так а как найти звёздочку?

Действительно, забыл. Чтобы найти «звёздочку», нужно в окошке поиска ставить перед ней знак ~ (тильда), он находится обычно под клавишей Esc. Это позволяет экранировать “звездочку”, как и вопросительный знак, и не воспринимать их как служебные символы.

Замена нескольких значений на несколько

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

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

Эта задача более сложная, чем замена на одно значение. Как ни странно, функция «ЗАМЕНИТЬ» здесь не подходит — она требует явного указания позиции заменяемого текста. Зато может помочь функция «ПОДСТАВИТЬ».

Массовая замена с помощью функции “ПОДСТАВИТЬ”

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

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

Но у решения есть и свои недостатки:

Функция ПОДСТАВИТЬ регистрозависимая, что заставляет при замене одного символа использовать два его варианта — в верхнем и нижнем регистрах. Хотя в некоторых случаях, как, например, на картинке выше, это и преимущество.
Максимум 64 замены — хоть и много, но все же ограничение.
Формально процедура замены таким способом будет происходить массово и моментально, однако длительность написания таких формул сводит на нет это преимущество. За исключением случаев, когда они будут использоваться многократно.

Файл-шаблон с формулой множественной замены

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

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

Формула в файле-шаблоне для множественной замены на примере транслитерации

А вот и она сама (тройной клик по любой части текста = выделить всю формулу): обращается к ячейке D1, делая 64 замены по правилам, указанным в ячейках A1-B64. При этом в столбцах можно удалять значения — это не нарушит её работу.

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(D1;$A$1;$B$1);$A$2;$B$2);$A$3;$B$3);$A$4;$B$4);$A$5;$B$5);$A$6;$B$6);$A$7;$B$7);$A$8;$B$8);$A$9;$B$9);$A$10;$B$10);$A$11;$B$11);$A$12;$B$12);$A$13;$B$13);$A$14;$B$14);$A$15;$B$15);$A$16;$B$16);$A$17;$B$17);$A$18;$B$18);$A$19;$B$19);$A$20;$B$20);$A$21;$B$21);$A$22;$B$22);$A$23;$B$23);$A$24;$B$24);$A$25;$B$25);$A$26;$B$26);$A$27;$B$27);$A$28;$B$28);$A$29;$B$29);$A$30;$B$30);$A$31;$B$31);$A$32;$B$32);$A$33;$B$33);$A$34;$B$34);$A$35;$B$35);$A$36;$B$36);$A$37;$B$37);$A$38;$B$38);$A$39;$B$39);$A$40;$B$40);$A$41;$B$41);$A$42;$B$42);$A$43;$B$43);$A$44;$B$44);$A$45;$B$45);$A$46;$B$46);$A$47;$B$47);$A$48;$B$48);$A$49;$B$49);$A$50;$B$50);$A$51;$B$51);$A$52;$B$52);$A$53;$B$53);$A$54;$B$54);$A$55;$B$55);$A$56;$B$56);$A$57;$B$57);$A$58;$B$58);$A$59;$B$59);$A$60;$B$60);$A$61;$B$61);$A$62;$B$62);$A$63;$B$63);$A$64;$B$64)

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ»

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

=ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(A1;"1";"");"2";"");"3";"");"4";"");"5";"");"6";"");"7";"");"8";"");"9";"");"0";"")

С помощью регулярных выражений

Важно: регулярные выражения не поставляются в Excel “из коробки”, но формулы ниже доступны бесплатно, если установить надстройку !SEMTools.

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

=regexreplace(A1;"d";"#")
=regexreplace(A1;"w";"#")
=regexreplace(A1;"а-яА-Я";"#")
=regexreplace(A1;"s";"_")

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

Массовая замена символов регулярными выражениями при установленной надстройке !SEMTools

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

Массовая замена в !SEMTools

Надстройка для Excel !SEMTools позволяет в пару кликов производить замены на всех уровнях:

символов и их сочетаний,
паттернов регулярных выражений,
слов,
целых ячеек (в некоторой степени аналог ВПР).

Меню инструментов массовой замены в !SEMTools

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

Пример: замена символов по вхождению

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

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

Массовая замена символов по вхождению на примере Leet Language (некоторые английские буквы заменяются на похожие цифры)

Пример: замена списка слов на другой список слов

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

замена списка слов на одно и то же слово

Замена списка слов на другой список в !SEMTools

С версии !SEMTools 9.18.18 появилась опция: при замене списка слов не учитывать пунктуацию в исходных предложениях, а регистр слов теперь сохраняется.

замена списка слов на другой список в Excel

Инструменты находятся в группе макросов «ИЗМЕНИТЬ» в отдельном меню и для удобства продублированы в меню «Изменить символы», «Изменить слова» и «Изменить ячейки».

Скачивайте надстройку !SEMTools и делайте массовую замену символов, слов или целых списков в Excel!

Смотрите также по теме поиска и замены данных в Excel:

Найти заглавные/строчные буквы в ячейке;
Найти латиницу или кириллицу в тексте;
Найти числа в текстовых ячейках;
Обнаружить текстовые символы;
Функция НАЙТИ в Excel;
Функция ПОИСК в Excel;
Функция ЗАМЕНИТЬ в Excel;
Найти определенные символы в ячейках Excel.

Источник

Содержание

Конвертация числа в текстовый вид
- Способ 1: форматирование через контекстное меню
- Способ 2: инструменты на ленте
- Способ 3: использование функции
Конвертация текста в число
- Способ 1: преобразование с помощью значка об ошибке
- Способ 2: конвертация при помощи окна форматирования
- Способ 3: конвертация посредством инструментов на ленте
- Способ 4: применение формулы
- Способ 5: применение специальной вставки
- Способ 6: использование инструмента «Текст столбцами»
- Способ 7: применение макросов
Вопросы и ответы

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

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

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

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

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

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

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

Кликаем правой кнопкой мыши по выделению и в открывшемся меню выбираем позицию «Формат ячеек…».

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

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

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

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

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

Урок: Как изменить формат в Excel

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

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

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

В открывшемся перечне вариантов форматирования выбираем пункт «Текстовый».

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

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

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

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

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

Запускается окно Мастера функций. В категории «Текстовые» выделяем пункт «ТЕКСТ». После этого кликаем по кнопке «OK».

Открывается окно аргументов оператора ТЕКСТ. Данная функция имеет следующий синтаксис:
=ТЕКСТ(значение;формат)

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

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

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

После того, как все требуемые параметры введены, щелкаем по кнопке «OK».

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

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

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

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

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

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

Урок: Мастер функций в Excel

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

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

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

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

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

Открывается список действий. Выбираем в нем значение «Преобразовать в число».

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

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

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

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

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

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

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

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

Выполняется запуск окна форматирования. Как и в предыдущий раз, переходим во вкладку «Число». В группе «Числовые форматы» нам нужно выбрать значения, которые позволят преобразовать текст в число. К ним относится пункты «Общий» и «Числовой». Какой бы из них вы не выбрали, программа будет расценивать цифры, введенные в ячейку, как числа. Производим выбор и жмем на кнопку. Если вы выбрали значение «Числовой», то в правой части окна появится возможность отрегулировать представление числа: выставить количество десятичных знаков после запятой, установить разделителями между разрядами. После того, как настройка выполнена, жмем на кнопку «OK».

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

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

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

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

Выделяем диапазон, который должен подвергнуться трансформации. Переходим во вкладку «Главная» на ленте. Кликаем по полю с выбором формата в группе «Число». Выбираем пункт «Числовой» или «Общий».

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

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

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

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

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

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

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

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

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

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

Урок: Как сделать автозаполнение в Excel

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

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

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

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

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

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

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

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

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

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

Во втором окне также оставляем все без изменений и жмем на кнопку «Далее».

А вот после открытия третьего окна Мастера текстов нужно нажать на кнопку «Подробнее».

Открывается окно дополнительной настройки импорта текста. В поле «Разделитель целой и дробной части» устанавливаем точку, а в поле «Разделитель разрядов» — апостроф. Затем делаем один щелчок по кнопке «OK».

Возвращаемся в третье окно Мастера текстов и жмем на кнопку «Готово».

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

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

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

Переходим во вкладку «Разработчик». Жмем на значок на ленте «Visual Basic», который размещен в группе «Код».

Запускается стандартный редактор макросов. Вбиваем или копируем в него следующее выражение:
Sub Текст_в_число() Selection.NumberFormat = "General" Selection.Value = Selection.Value End Sub

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

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

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

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

Урок: Как создать макрос в Экселе

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

Источник

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

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

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

Является ли текст данных ячейки или числа?

Есть несколько способов узнать, отформатирован ли номер или набор чисел в столбце или строке как текст в Excel.

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

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

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

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

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

1. Преобразовать в число

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

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

Выберите этот символ. В раскрывающемся списке выберите «Преобразовать в число».

Это обновит все выбранные вами текстовые числа до общего числового формата данных.

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

2. Использование текста в столбце

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

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

Выберите «Данные» в меню, а затем выберите «Текст в столбцы» в разделе «Инструменты данных» на ленте.

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

На следующей странице мастера оставьте выбранную вкладку по умолчанию и снова выберите Далее.

Наконец, на последней странице мастера убедитесь, что в разделе Формат данных столбца выбрано Общее. В поле «Место назначения» вы можете выбрать новый столбец, в который нужно перенести числовые данные, или просто оставить текущий выбранный столбец без изменений. Выберите Готово.

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

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

3. Изменение формата ячейки

Самый простой и быстрый способ преобразования текста в числа в Excel — это просто изменить форматирование ячейки из главного меню.

Сделать это:

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

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

4. Использование значений вставки

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

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

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

Выберите весь столбец ячеек, который вы хотите преобразовать из текста в числа, щелкните правой кнопкой мыши и выберите «Копировать».

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

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

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

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

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

= ЗНАЧЕНИЕ (G2)

Замените «G2» выше на ячейку с номером, который вы хотите преобразовать. Если вы конвертируете целый столбец чисел, начните только с первой ячейки.

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

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

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

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

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

Источник

Довольно частая трудность, с которой сталкиваются пользователи Excel, когда пытаются скопировать числовую информацию или импортировать файлы – это когда числовой формат становится текстовым. Из-за этого формулы теряют работоспособность, что делает нереальными какие-либо вычисления. Что можно сделать, чтобы быстро исправить эту проблему?

Содержание

Как преобразовать текст в число в Эксель?
Зеленый уголок-индикатор
Повторный ввод
Формула преобразования текста в число
Специальная вставка
Инструмент «Текст по столбцам»
Макрос «Текст – число»
Как определить числа, записанные в Экселе, как текст?
Как воспользоваться индикатором ошибок?
Удаление непечатаемых символов
Извлечение числа из текста

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

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

Зеленый уголок-индикатор

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

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

Повторный ввод

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

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

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

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

Здесь двойной минус заменяет операцию умножения на -1 дважды. Зачем это делается? Дело в том, что минус на минус дает положительный результат, поэтому результат не изменится. но поскольку Excel выполнял арифметическую операцию, то значение не может быть другим, кроме как числовым.

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

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

Это очень старый метод, который использовался в первых версиях Excel (поскольку зеленый индикатор добавили лишь в 2003-й версии). Наши действия следующие:

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

3

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

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

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

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

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

Осталось только кликнуть на «Готово», как текстовое значение немедленно превратится в полноценное число.

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

Если часто нужно совершать такие операции, то рекомендуется этот процесс сделать автоматическим. Для этого существуют специальные исполняемые модули – макросы. Чтобы открыть редактор, существует комбинация Alt+F11. Также к нему можно получить доступ через вкладку «Разработчик». Там вы найдете кнопку «Visual Basic», которую и нужно нажать.

Наша следующая задача – вставить новый модуль. Чтобы это сделать, нужно открыть меню Insert – Module. Далее нужно скопировать этот фрагмент кода и вставить в редактор стандартным способом (Ctrl + C и Ctrl + V).

Sub Convert_Text_to_Numbers()

Selection.NumberFormat = «General»

Selection.Value = Selection.Value

End Sub

Для выполнения каких-либо действий с диапазоном, его следует предварительно выделить. После этого надо запустить макрос. Делается это через вкладку «Разработчик – Макросы». Появится перечень подпрограмм, которые можно выполнять в документе. Нужно выбрать ту, которая надо нам и нажать на «Выполнить». Далее программа все сделает за вас.

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

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

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

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

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

Как воспользоваться индикатором ошибок?

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

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

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

Чтобы избавиться от этой проблемы, можно воспользоваться формулой =ЗНАЧЕН(СЖПРОБЕЛЫ(ПЕЧСИМВ(A2)))

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

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

Эта возможность будет полезной, если из строки нужно извлечь число. Для этого ее нужно применить в комбинации с функциями ПРАВСИМВ, ЛЕВСИМВ, ПСТР.

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

=ЗНАЧЕН(ПРАВСИМВ(A2;3))

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

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

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

Источник

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

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

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

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

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

Повторный ввод.

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

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

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

Макрос VBA.

Извлечь число из текстовой строки с помощью Ultimate Suite

Поиск

Заменить

Поиск

Заменить

Дополнительные сведения

Рекомендуемые статьи

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

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

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

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

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

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

Использование формулы

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

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

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

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

Использование специальной вставки и умножения

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

См. также:

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

В этой статье

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

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

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

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

Замена формулы на ее результат

В этой статье

Замена формул на вычисленные значения

Замена части формулы на вычисленное значение

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

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

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

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

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

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

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

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

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

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

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

P.S.

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

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

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

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

Горячие клавиши

Процедура «Найти и заменить» не работает

Подстановочные знаки, или как найти “звёздочку”

Замена нескольких значений на несколько

Массовая замена с помощью функции “ПОДСТАВИТЬ”

Файл-шаблон с формулой множественной замены

Заменить несколько значений на одно

С помощью функции «ПОДСТАВИТЬ»

С помощью регулярных выражений

Массовая замена в !SEMTools

Пример: замена символов по вхождению

Пример: замена списка слов на другой список слов

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

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

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

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

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

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

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

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

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

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

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