Как найти число в ячейке excel

Какой формулой Excel можно выбрать из ячейки число?

Какой формулой извлечь из строки текста подстроку с цифрами?

Как в Экселе составить формулу чтобы выбрать из ячейки число?

Как составить формулу чтобы извлечь из текста лицевой счёт или сумму платежа?

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

Алгоритм формулы позволяющей выбрать из ячейки число такой:

• находим в тексте положение первой цифры (берём формулу из вопроса Как в Excel найти первую цифру ячейки?)
• Берём остаток строки с числом (функцией ПРАВСИМВ)
• и подставляем в формулу из вопроса Excel: как извлечь число если строка начинается с числа и содержит текст?
• Обрабатываем ошибку при отсутствии числа в ячейке (функция ЕСЛИОШИБКА)

=ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)

остаток исходной строки

=ПРАВСИМВ(A1; ДЛСТР(A1)- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)

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

=—ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1­ )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)

с учетом возможности обработки строк без цифр:

=ЕСЛИОШИБКА( —ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1­ )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); "нет цифр")

Чтобы выбрать из ячейки число как текст надо убрать двойной минус(—):

=ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1­ )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1)

с учетом возможности обработки строк без цифр:

=ЕСЛИОШИБКА( ЛЕВСИМВ( ПРАВСИМВ(A1; ДЛСТР(A1)-ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); ПОИСКПОЗ(0; ЕЧИСЛО( —ПСТР( ПРАВСИМВ(A1;ДЛСТР(A1­ )- ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН( ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1); 1;СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( ПРАВСИМВ(A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1) )))))*СТРОКА( ДВССЫЛ( "1:"&ДЛСТР(ПРАВСИМВ( A1; ДЛСТР(A1) — ПОИСКПОЗ( ИСТИНА; ЕЧИСЛО( ЗНАЧЕН(ПСТР(A1; СТРОКА( ДВССЫЛ( "1:"&ДЛСТР( A1))); 1))); 0)+1)) ));0)-1); "нет цифр")

Как в Excel определить содержит ли ячейка число

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

Основная формула

Описание

Чтобы проверить, содержит ли ячейка (или любая текстовая строка) число, вы можете использовать функцию НАЙТИ вместе с функцией СЧЁТ. В основной форме формулы (см. выше) A1 представляет ячейку, которую вы тестируете. Проверяемые числа (числа от 0 до 9) предоставляются в виде массива.
В этом примере формула в C5 имеет вид:

Пояснение

Функция НАЙТИ возвращает положение текста в ячейке и ошибку #ЗНАЧЕН!, если нет. В этом примере ячейка B5 содержит число «4» в 5 позиции. Итак, если бы мы просто использовали эту формулу:

В результате она вернет число 5. Однако, поскольку мы даем функции НАЙТИ массив элементов для проверки, она вернет массив результатов, который выглядит следующим образом:
<#ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!,4, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!, #ЗНАЧЕН!>
Другими словами, НАЙТИ проверяет содержимое B5 для каждого числа и возвращает результат каждой проверки как элемент массива.
После того, как НАЙТИ вернет массив, СЧЁТ считает элементы в массиве. СЧЁТ считает только числовые значения, поэтому любой элемент #ЗНАЧЕН! в массиве обрабатывается как ноль. Если в массиве есть числа СЧЁТ вернет число больше нуля, если нет то вернет ноль.
Последний шаг в формуле — сравнение результата функций НАЙТИ и СЧЁТ с нулем. Если были найдены какие-либо числа, формула вернет ИСТИНА. В противном случае формула вернет ЛОЖЬ.
Если вы хотите сделать что-то большее, чем просто проверить, содержит ли ячейка текст, вы можете заключить формулу в оператор ЕСЛИ следующим образом:

Вместо того, чтобы возвращать ИСТИНА или ЛОЖЬ, приведенная выше формула возвращает «Да», если B5 содержит какие-либо числа, и «Нет», если не содержит.

Содержит ли ячейка числовое значение?

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

Поиск в программе Microsoft Excel

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

Поисковая функция в Excel

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

Способ 1: простой поиск

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

Находясь во вкладке «Главная», кликаем по кнопке «Найти и выделить», которая расположена на ленте в блоке инструментов «Редактирование». В появившемся меню выбираем пункт «Найти…». Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

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

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

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

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

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

1. Выделяем область ячеек, в которой хотим произвести поиск.
2. Набираем на клавиатуре комбинацию клавиш Ctrl+F, после чего запуститься знакомое нам уже окно «Найти и заменить». Дальнейшие действия точно такие же, что и при предыдущем способе. Единственное отличие будет состоять в том, что поиск выполняется только в указанном интервале ячеек.

Способ 3: Расширенный поиск

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

К тому же, в выдачу может попасть не только содержимое конкретной ячейки, но и адрес элемента, на который она ссылается. Например, в ячейке E2 содержится формула, которая представляет собой сумму ячеек A4 и C3. Эта сумма равна 10, и именно это число отображается в ячейке E2. Но, если мы зададим в поиске цифру «4», то среди результатов выдачи будет все та же ячейка E2. Как такое могло получиться? Просто в ячейке E2 в качестве формулы содержится адрес на ячейку A4, который как раз включает в себя искомую цифру 4.

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

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

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

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

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

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

Ещё более точно поиск можно задать, нажав на кнопку «Формат».

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

Если вы хотите использовать формат какой-то конкретной ячейки, то в нижней части окна нажмите на кнопку «Использовать формат этой ячейки…».

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

После того, как формат поиска настроен, жмем на кнопку «OK».

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

Как найти число в ячейке excel

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

Совет: Для замены текста можно также использовать формулы. Чтобы узнать больше, проверка функции REPLACE или REPLACE, REPLACEB.

Поиск

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

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

В поле Найти введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти, а затем выберите последний элемент поиска из списка.

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

Используйте вопросительный знак (?), чтобы найти любой один символ, например s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например s*d находит «sad» и «started».

), за которой следует знак ?, *или

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

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

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

Выберите Параметры>> , чтобы при необходимости определить поиск:

В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.

Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.

Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите Формулы, Значения, Заметки или Примечания.

Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.

Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.

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

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

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

Заменить

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

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

В поле Найти введите текст или цифры, которые вы хотите найти, или щелкните стрелку в поле Найти, а затем выберите последний элемент поиска из списка.

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

Используйте вопросительный знак (?), чтобы найти любой один символ, например s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например s*d находит «sad» и «started».

), за которой следует знак ?, *или

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

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

Нажмите Заменить все или Заменить.

Совет: При нажатии кнопки Заменить все каждое вхождение критерия, которое вы ищете, будет заменено, в то время как функция Replace обновляет одно вхождение за раз.

Выберите Параметры>> , чтобы при необходимости определить поиск:

В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.

Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.

Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите Формулы, Значения, Заметки или Примечания.

Примечание: Формулы, значения, заметки и примечания доступны только на вкладке Найти . На вкладкеЗаменить доступны только формулы.

Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.

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

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

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

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

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

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

Нажмите клавиши CTRL+F или выберите Главная > Найти & Выберите > Найти.

В поле Поиск текста или чисел, которые вы хотите найти.

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

Вы можете дополнительно определить поиск:

В: Чтобы найти данные на листе или во всей книге, выберите Лист или Книга.

Поиск: Вы можете выполнить поиск по строкам (по умолчанию) или по столбцам.

Посмотрите в: Чтобы найти данные с определенными сведениями, в поле выберите Формулы, Значения, Заметки или Примечания.

Вариант соответствия . Проверьте это, если вы хотите искать данные с учетом регистра.

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

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

Используйте вопросительный знак (?), чтобы найти любой один символ, например s?t находит «sat» и «set».

Используйте звездочку (*), чтобы найти любое количество символов, например s*d находит «sad» и «started».

), за которой следует знак ?, *или

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