Как найти второй пробел в строке excel
Argument ‘Topic id’ is null or empty
Сейчас на форуме
© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru
Использование любых материалов сайта допускается строго с указанием прямой ссылки на источник, упоминанием названия сайта, имени автора и неизменности исходного текста и иллюстраций.
| ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949 |
ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071 |
Трюк №78. Как в Excel строить мегаформулы
Мегаформулы — формулы внутри формул внутри формулы — могут свести сума даже бывалого ветерана Excel. Однако, имея немного предусмотрительности и шаг за шагом продвигаясь к желаемой формуле, вы сможете приручить эти сложные мегаформулы и обращаться с ними без страха.
Наполняет ли вас ужасом одна мысль о том, чтобы в одиночку разобраться и составить вложенную формулу? Иногда такие ячейки, заполненные сложной функциональной белибердой, вызывают дрожь в коленях и у нас. Однако, не забывая о предусмотрительности и пошаговом подходе, вы сможете без страха создавать мегаформулы. И, возможно, только возможно, когда-нибудь вы сможете снова прочитать и понять их.
Трюк заключается в том, чтобы строить формулы по кусочкам, используя стандартные функции Excel. Используя по одной функции в каждой ячейке, получая индивидуальные управляемые результаты, вы сможете вложить функции друг в друга и получить желаемый конечный результат. Вот пример такого процесса в действии.
Предположим, есть длинный список имен людей, каждое из которых состоит из имени, отчества и фамилии, то есть по одному полному имени на ячейку. Задача состоит в том, чтобы написать формулу в соседнем столбце, которая будет извлекать только фамилию человека, который позвонил в отель Скандинавия и забронировал шикарный номер люкс.
Следовательно, надо найти начало фамилии, третьего слова в ячейке. Фактически, это означает поиск позиции второго пробела в ячейке. В Excel нет стандартной встроенной функции для автоматического поиска второго пробела в ячейке, но можно воспользоваться функцией НАЙТИ (FIND), применив ее так, чтобы получить желаемый результат.
В ячейку А1 введите имя David John Hawley (или любое другое имя, состоящее из трех слов). В ячейку С1 введите следующую функцию: =FIND(» «;A1) , в русской версии Excel =НАЙТИ(» «;A1) . Функция НАЙТИ (FIND) ищет одну текстовую строку искомый_текст (find_text) внутри другой текстовой строки текст_для_поиска (within_text) и возвращает число, обозначающее начальную позицию искомый_текст (find_text), если отсчет начинается с первого символа текст_для_поиска (within_text). Синтаксис функции =FIND(find_text;within_text,;start_num) , в русской версии Excel =НАЙТИ(искомый_текст;текст_для_поиска;нач_позиция) .
Эта функция вернет начальную позицию первого символа пробела в ячейке А1, так как приказали ей найти «» (пробел) в ячейке А1. Если в ячейке находится текст David John Hawley, то функция вернет значение 6. Но вы ищете второй пробел, а не первый. Теперь нужно использовать число, возвращенное формулой в ячейке С1, как начальную позицию для другой функции НАЙТИ (FIND), чтобы найти второй символ пробела. Так, в ячейке С2 введите формулу =FIND(» «;A1;C1+1) , в русской версии Excel =НАЙТИ(» «;A1;C1+1) . Обратите внимание, что в этот раз вы передали функции НАЙТИ (FIND) третий аргумент, начальную позицию, найденную С1 (в данном примере равную 6), к которой прибавлена единица; она будет служить начальной точкой для функции НАЙТИ (FIND), начиная с которой она будет искать пробел. Вторая функция возвратит значение, обозначающее позицию второго символа пробела.
Вооружившись этими функциями, вы хотите, чтобы следующая функция выбрала все символы до конца строки текста. Воспользуйтесь функцией ПСТР (MID), которая предназначена для выделения диапазона символов из строки. В ячейке СЗ введите следующую формулу: =MID(A1;C2+1;256) , в русской версии Excel =ПСТР(А1;С2+1;256) . Функция ПСТР (MID) возвращает указанное количество символов текстовой строки, начиная с указанной позиции. Вот ее синтаксис: MID(text;start_num;num_chars) , в русской версии Excel ПСТР(текст;начальная_позиция;количество_символов) . Вы приказали функции ПСТР (MID) выбрать 256 символов из ячейки А1, начиная с первого символа после второго пробела в текстовой строке. Число 256 используется только чтобы гарантировать, что, независимо от длины фамилии (предполагая, что она все же короче 256 символов), вы получите фамилию человека полностью.
При наличии всех составляющих настало время построить вложенную формулу, которой вы так боялись всего несколько минут назад, целиком. Все, что нужно сделать, — это заменить все ссылки на ячейки (кроме А1) внутри функций на формулы в этих ячейках. Это делается путем копирования и вставки в строке формул.
Щелкните ячейку С2, в строке формул выделите функцию и скопируйте ее целиком, кроме знака =, вот так: FIND(» «.A1;C1+1) , в русской версии Excel НАЙТИ(» «;A1;C1+1) . Нажмите клавишу Enter, чтобы покинуть ячейку, и вы окажетесь в ячейке С3. Выделив ячейку С3, в строке формул выделите ссылку на ячейку С2 и вставьте (сочетание клавиш Ctrl+V) функцию НАЙТИ (FIND), которую только что скопировали. Нажмите клавишу Enter. Функция в ячейке СЗ должна выглядеть так: =MID(A1;FIND(» «;А1;С1+1)+1;256) , в русской версии Excel =ПСТР(А1;НАЙТИ(» «;А1;С1+1)+1;256) .
Теперь необходимо заменить ссылку на ячейку С1 функцией, которая находится в ячейке С1. Выделите ячейку С1, выделите в строке формул формулу, опустив символ =, щелкните на кнопке Копировать (Сору), а затем дважды нажмите клавишу Enter, чтобы попасть обратно в ячейку С3. Находясь в ячейке С3, выделите С1 в строке формул и вставьте только что скопированную функцию НАЙТИ (FIND). Нажмите клавишу Enter.
Теперь осталось вырезать ячейку С3 и вставить ее в ячейку В1, а затем удалить формулы, оставшиеся в ячейках С1 и С2. Вы должны получить итоговую формулу, которая выглядит так: =MID(A1;FIND(» «;A1;FIND(» «;А1)+1)+1;256) , в русской версии Excel =ПСТР(А1;НАЙТИ(» «;А1;НАЙТИ(» «;A1)+1)+1;256) .
Следуя этой концепции, вы должны понять, как конструировать мегаформулы, используя разнообразные функции Excel. Сначала необходимо запланировать путь, по которому вы собираетесь идти к цели, а затем в индивидуальных ячейках получать необходимые результаты. Наконец, нужно заменить все ссылки на ячейки функциями, которые находятся в этих ячейках. Если получилось больше семи уровней вложенности, понадобится функция ДВССЫЛ (INDIRECT), описанная в разделе «Трюк №74. Отображение отрицательных значений времени».
ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
В этой статье описаны синтаксис формулы и использование функций ПОИСК и ПОИСКБ в Microsoft Excel.
Описание
Функции ПОИСК И ПОИСКБ находят одну текстовую строку в другой и возвращают начальную позицию первой текстовой строки (считая от первого символа второй текстовой строки). Например, чтобы найти позицию буквы «n» в слове «printer», можно использовать следующую функцию:
Эта функция возвращает 4, так как «н» является четвертым символом в слове «принтер».
Можно также находить слова в других словах. Например, функция
возвращает 5, так как слово «base» начинается с пятого символа слова «database». Можно использовать функции ПОИСК и ПОИСКБ для определения положения символа или текстовой строки в другой текстовой строке, а затем вернуть текст с помощью функций ПСТР и ПСТРБ или заменить его с помощью функций ЗАМЕНИТЬ и ЗАМЕНИТЬБ. Эти функции показаны в примере 1 данной статьи.
- Эти функции могут быть доступны не на всех языках.
- Функция ПОИСКБ отсчитывает по два байта на каждый символ, только если языком по умолчанию является язык с поддержкой БДЦС. В противном случае функция ПОИСКБ работает так же, как функция ПОИСК, и отсчитывает по одному байту на каждый символ.
К языкам, поддерживающим БДЦС, относятся японский, китайский (упрощенное письмо), китайский (традиционное письмо) и корейский.
Синтаксис
Аргументы функций ПОИСК и ПОИСКБ описаны ниже.
- Искомый_текст Обязательный. Текст, который требуется найти.
- Просматриваемый_текст Обязательный. Текст, в котором нужно найти значение аргумента искомый_текст.
- Начальная_позиция Необязательный. Номер знака в аргументе просматриваемый_текст, с которого следует начать поиск.
Замечание
- Функции ПОИСК и ПОИСКБ не учитывают регистр. Если требуется учитывать регистр, используйте функции НАЙТИ и НАЙТИБ.
- В аргументе искомый_текст можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому знаку, звездочка — любой последовательности знаков. Если требуется найти вопросительный знак или звездочку, введите перед ним тильду (~).
- Если значение find_text не найдено, #VALUE! возвращается значение ошибки.
- Если аргумент начальная_позиция опущен, то он полагается равным 1.
- Если start_num больше нуля или больше, чем длина аргумента within_text, #VALUE! возвращается значение ошибки.
- Аргумент начальная_позиция можно использовать, чтобы пропустить определенное количество знаков. Допустим, что функцию ПОИСК нужно использовать для работы с текстовой строкой «МДС0093.МужскаяОдежда». Чтобы найти первое вхождение «М» в описательной части текстовой строки, задайте для аргумента начальная_позиция значение 8, чтобы поиск не выполнялся в той части текста, которая является серийным номером (в данном случае — «МДС0093»). Функция ПОИСК начинает поиск с восьмого символа, находит знак, указанный в аргументе искомый_текст, в следующей позиции, и возвращает число 9. Функция ПОИСК всегда возвращает номер знака, считая от начала просматриваемого текста, включая символы, которые пропускаются, если значение аргумента начальная_позиция больше 1.
Примеры
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Поиск второго пробела в строке, функции =ПОИСК или =НАЙТИ
Всем доброго времени суток. Есть строка ул. Северная д15 и в ней необходимо определить позицию второго пробела, того, который после названия улицы. Функции =ПОИСК и =НАЙТИ почему-то находят только позицию первого пробела. Пробовал дописывать «+1» к начальной позиции поиска, всё равно не помогает.
Заранее благодарю за помощь!
94731 / 64177 / 26122
Регистрация: 12.04.2006
Сообщений: 116,782
Ответы с готовыми решениями:
Поиск в строке Мемо до пробела и далее до пробела
В принципе тема баян ,но что то не нашел, так что извиняюсь если что. Смысл такой: есть строка в.
Поиск в строке двойного пробела
Доброго времени суток! Нужно в строке SQL-запросом найти номер позиции где встречаются два.
Поиск символа а и А в строке после пробела
нужно найти количество слов начинающихся с а или А. #include <stdio.h> #include <string.h> .
Поиск второго слова в строке
Добрый день. Подскажите как в строке найти второе слово.
92 / 69 / 16
Регистрация: 22.01.2010
Сообщений: 311
Сообщение от Testingtest 
. необходимо определить позицию второго пробела
Цель какая — что? для чего? почему? А если (по правилам) пробелу будет предшествовать запятая.
772 / 615 / 294
Регистрация: 05.01.2014
Сообщений: 1,338
=ПОИСК(" ";A1;ПОИСК(" ";A1)+1)
Регистрация: 18.07.2018
Сообщений: 29
AleksSid, да так, но я забыл добавить, что эта строка ул. Северная д.15 находится внутри другого текста. То есть на самом деле это не первый и не второй пробелы. Вот с этим не знаю как поступить.