Как найти второй пробел в строке excel
Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста. Для поиска вхождений одной текстовой строки в другую текстовую строку можно использовать также функцию ПОИСК, но в отличие от функции ПОИСК функция НАЙТИ учитывает регистр и не допускает использования подстановочных знаков.
Синтаксис
НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)
ПОИСК(искомый_текст;просматриваемый_текст;нач_позиция)
Искомый_текст — это искомый текст.
Просматриваемый_текст — это текст, включающий искомый текст. Для функции ПОИСК можно использовать знаки шаблона: знак вопроса (?) и звездочка (*). Знак вопроса соответствует любому знаку; звездочка соответствует любой последовательности знаков. Если нужно найти один из этих знаков, то следует поставить перед ними знак тильда (~).
Нач_позиция — это позиция знака, с которой следует начинать поиск. Первый знак в аргументе просматриваемый_текст имеет номер 1. Если аргумент нач_позиция опущен, то он полагается равным 1.
Функции НАЙТИ и ПОИСК можно использовать, например, в качестве аргументов функций ЛЕВСИМВ и ПРАВСИМВ.
Пример:
В столбце А мы имеем перечень сотрудников. Каждая запись включает Фамилию, имя и отчество, разделенные пробелами. Необходимо получить в отдельном столбце имена сотрудников.
Использование мастера разделения текста по столбцам не всегда удобно, иногда нужно получить ответ именно с помощью функций.
Для выделения имени необходимо с помощью функции НАЙТИ определить порядковый номер первого пробела в тексте, затем номер второго пробела и выделить подстроку между пробелами.
Для обработки текста в ячейке А2 формула будет выглядеть так:
=ПСТР(A2;НАЙТИ(" ";A2;1)+1;НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1)-НАЙТИ(" ";A2;1)-1)
Не пугайтесь. Функция НАЙТИ использована здесь 4 раза для вычисления местоположения нужногой подстроки. Первый пробел в ячейке А2 имеет порядковый номер НАЙТИ(" ";A2;1). Следовательно имя начинается с символа номер НАЙТИ(" ";A2;1)+1.
Второй пробел идет сразу за именем. Чтобы найти его, нужно искать пробел не с начала строки, а начиная с символа, следующего за первым пробелом. Получается конструкция НАЙТИ(" ";A2;НАЙТИ(" ";A2;1)+1). Если из полученного числа вычесть место первого символа имени, мы получим длину имени.
Иногда могут оказаться полезными функции ЗАМЕНИТЬ и ПОДСТАВИТЬ.
Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке; функция ЗАМЕНИТЬ используется, когда нужно заменить любой текст, начиная с определенной позиции.
Синтаксис
ПОДСТАВИТЬ(текст;стар_текст;нов_текст;номер_вхождения)
Текст — это либо текст, либо ссылка на ячейку, содержащую текст, в котором подставляются знаки.
Стар_текст — заменяемый текст.
Нов_текст — текст, на который заменяется стар_текст.
Номер_вхождения — определяет, какое вхождение текста стар_текст нужно заменить на нов_текст. Если номер_вхождения определен, то заменяется только это вхождение текста стар_текст. В противном случае, каждое вхождение текста стар_текст в текстовой строке заменяется на текст нов_текст.
Функция ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.
Синтаксис
ЗАМЕНИТЬ(старый_текст;нач_поз;число_знаков;новый_текст)
Старый_текст — текст, в котором желательно заменить некоторые знаки.
Нач_поз — позиция знака в тексте старый_текст, начиная с которой знаки заменяются текстом нов_текст.
Число_знаков — число знаков в тексте старый_текст, которые заменяются текстом новый_текст.
Новый_текст — текст, который заменяет знаки в тексте старый_текст.
Пример:
Формула =ПОДСТАВИТЬ(A2;"(095)";"(495)") заменит старый телефонный код Москвы на новый.
Функция СЖПРОБЕЛЫ удаляет из текста все пробелы, за исключением одиночных пробелов между словами. Функция СЖПРОБЕЛЫ используется для обработки текстов, полученных из других прикладных программ, если эти тексты могут содержать избыточные пробелы.
Как найти позицию n-го пробела в строке Excel?
Например, есть список строк, и каждая ячейка содержит несколько слов, разделенных пробелами, есть ли у вас какие-либо методы, чтобы быстро найти положение первого пробела из этих строк, как показано ниже? В этой статье я расскажу, как легко найти положение n-го пространства в Excel.
Удалить начальные / конечные / дополнительные / все пробелы из строки
Найдите положение n-го пространства по формуле
Чтобы найти положение n-го пробела, вы можете применить эти формулы.
Найдите позицию первого пробела | = НАЙТИ (""; A1) |
Найдите положение второго пробела | = НАЙТИ (""; A1; НАЙТИ (""; A1) +1) |
Найдите положение третьего пробела | = НАЙТИ (""; A1; НАЙТИ (""; A1; НАЙТИ (""; A1) +1) +1) |
Найдите положение четвертого пространства | = НАЙТИ ("", A1, НАЙТИ ("", A1, НАЙТИ ("", A1, НАЙТИ ("", A1) +1) + 1) +1) |
Возьмем, к примеру, положение второго пробела в строках.
Выберите пустую ячейку C2, введите эту формулу = НАЙТИ (""; A2; НАЙТИ (""; A2) +1), затем перетащите дескриптор автозаполнения вниз к ячейке, для которой нужна эта формула. Смотрите скриншот:
Удалить начальные / конечные / дополнительные / все пробелы из строки
Но в большинстве случаев вам может потребоваться удалить начальные, конечные, дополнительные или все пробелы из списка строк. В Excel нет встроенной функции, которая может ее решить, но с Kutools for Excel - удобный и мощный инструмент для добавления в Excel, его утилита Remove Spaces может быстро выполнять указанные выше операции по мере необходимости.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите строки, в которых нужно удалить пробелы, и нажмите Кутулс > Текст > Удалить пробелы. Смотрите скриншот:
2. в Удалить пробелы диалог, отметьте опцию в Тип пространства как вам нужно, и вы можете просмотреть удаленный результат в предварительный просмотр раздел. Смотрите скриншот:
3. Нажмите Ok or Применить, теперь из выбранных строк удаляются пробелы.
Извлечь текст до или после второго пробела или запятой
Если вам нужно извлечь текстовые строки, которые находятся до или после второго или n-го определенного символа (пробела или запятой) из списка ячеек, в этой статье будут представлены некоторые формулы для решения этой задачи в Excel.
Извлечь текст после второго или n-го пробела или запятой в Excel
- text : Текстовая строка или ссылка на ячейку, из которой вы хотите извлечь текст.
- Nth : Представляет собой пробел или номер экземпляра через запятую, на основе которого вы хотите извлечь текст.
Введите или скопируйте следующую формулу в пустую ячейку, в которую вы хотите вывести результат:
Пояснение к формуле:
ОТДЕЛКА(): Эта функция TRIM удалит все лишние пробелы и оставит только один между словами.
1. Если ваши тексты разделены запятой или другими разделителями, вам просто нужно заменить строку пробела другими разделителями по мере необходимости. Такие как:
2. Чтобы извлечь текст после n-го пробела, измените число 2 на число, которое вам нужно в формуле, например, чтобы извлечь текст после третьего пробела, вам просто нужно применить следующую формулу:
Извлечь текст перед вторым или n-м пробелом или запятой в Excel
Чтобы извлечь текст перед вторым или n-м пробелом или запятой, функции LEFT, SUBSTITUTE и FIND могут оказать вам услугу. Общий синтаксис:
- text : Текстовая строка или ссылка на ячейку, из которой вы хотите извлечь текст.
- Nth : Представляет собой пробел или номер экземпляра через запятую, на основе которого вы хотите извлечь текст.
Скопируйте или введите приведенную ниже формулу в пустую ячейку:
Затем перетащите дескриптор заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, и все тексты до второго пробела были извлечены, как показано на следующем снимке экрана.
Пояснение к формуле:
1. Если ваши тексты разделены запятой или другими разделителями, вам просто нужно заменить строку пробела другими разделителями по мере необходимости. Такие как:
2. Чтобы извлечь текст перед n-м пробелом, измените число 2 на число, которое вам нужно в формуле, например, чтобы извлечь текст перед третьим пробелом, вам просто нужно применить следующую формулу:
Используемые относительные функции:
- MID :
- Функция MID используется для поиска и возврата определенного количества символов из середины данной текстовой строки.
- LEFT :
- Функция LEFT извлекает заданное количество символов из левой части предоставленной строки.
- FIND :
- Функция НАЙТИ используется для поиска строки в другой строке и возвращает начальную позицию строки внутри другой.
- SUBSTITUTE :
- Функция Microsoft Excel SUBSTITUTE заменяет текст или символы в текстовой строке другим текстом или символами.
- TRIM :
- Функция TRIM удаляет все лишние пробелы из текстовой строки и сохраняет только отдельные пробелы между словами.
Другие статьи:
- Извлечь несколько строк из ячейки
- Если у вас есть список текстовых строк, разделенных разрывами строк (это происходит при нажатии клавиш Alt + Enter при вводе текста), и теперь вы хотите извлечь эти строки текста в несколько ячеек, как показано ниже. Как бы вы могли решить это с помощью формулы в Excel?
- Извлечь N-е слово из текстовой строки в Excel
- Если у вас есть список текстовых строк или предложений, теперь вы хотите извлечь конкретное n-е слово из списка, как показано ниже. В этой статье я расскажу о некоторых методах решения этой задачи в Excel.
- Извлечь текст между круглыми скобками из текстовой строки
- Если в текстовой строке есть часть текста, заключенная в круглые скобки, теперь вам нужно извлечь все текстовые строки между скобками, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?
- Извлечь подстроку из текстовой строки в Excel
- Для вас может быть обычной задачей извлекать подстроки из текстовых строк, в Excel нет прямой функции для этого, но с помощью функций LEFT, RIGHT, MID и SEARCH вы можете извлечь виды подстрок по мере необходимости.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.
Помогите пожалуйста с формулой (именно формула, а не макрос ) что бы посчитать количество знаков до второго пробела в ячейке?
Тесть есть символы в ячейке разделенные одним пробелом g1ht* kdjf1R 265kllzS T6xklcjxTQ
Нужна формула которая выдаст количество знаков по R то есть в данном случае 12. Пробел между словами всегда один.
Определить количество вхождений строки из n знаков в строку из k знаков
Всем доброй ночи ребят. Вы последняя надежда:( помогите пожалуйста 2 задачи решить последняя ночь(.
Определить длину второго слова и количество слов длиннее второго
Ввести предложение длиной не более 80 символов. Определить длину его второго слова и количество.
Подсчитать количество слов, в тексте от пробела до пробела, и количество букв
нужно создать програму считает количество слов, в тексте от пробела до пробела(если два пробела.
К сожалению не работает.
Добавлено через 6 минут
=ПОИСК(" ",A1,ПОИСК(" ",A1)+1)-1
Все подправил, сейчас работает. Serge 007 и Аналитика спасибо большое.
Yulia_eL, выложите книгу Excel и поясните, где и какая должна быть формула.
Всю книгу не выкладывайте, а оставьте столько данных, чтобы можно было понять вашу задачу.
Спасибо, но я уже разобралась. У меня была такая же проблема, что и у автора темы и предложенная ему формула у меня не работала, а заработала вот эта: =НАЙТИ(" ";A1;НАЙТИ(" ";A1)+1)
Добавлено через 9 минут
Но работает она только тогда, когда её копируешь из вёрда например и вставляешь в строку для формул. Если же я пишу её от руки или пользуюсь Аргументом функций, то снова выдаёт ошибку.
Вот моё задание в файле. Всё работает, только тогда, когда я копирую эту формулу откуда-то, а когда набираю вручную, то ошибка!
Pelena, Не в первый раз замечаю, как Вы с первого раза "угадываете" решение проблемы пользователя. Экстрасенс? (вопрос риторический, отвечать не нужно)
У меня ещё 1 вопрос. Хотела создать новую тему по этому вопросу, но не получается.
Вопрос следующий: У меня такое задание: Проставить в столбцах В и С значения 1 при условии, что в столбце А есть соответствие названия столбцов. Если же такого соответствия нет, то ставим 0.
Я формулу написала, но оно проставляет "1" и "0" только напротив тех слов, где они стоят самостоятельно в строке. Если же в строке с нужным словом ещё какое-либо слово, то просто выставляются нули. Что я не правильно делаю?)
Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов - в начале, в конце или внутри между словами.
Лечится эта проблема очень легко - специальной функцией СЖПРОБЕЛЫ (TRIM) . Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:
Просто и красиво. Но есть пара нюансов.
Неразрывные пробелы не удаляются
Чайной ложкой дегтя тут представляется только невозможность удалять таким способом неразрывные пробелы. Это особый вариант пробела (внешне неотличимый от обычного), на котором не бывает переноса строки. Обычно его используют в случаях типа "г. Москва" или "Иванов И.И.", чтобы эти фразы не разбивались между строчками. В Microsoft Word такой спецсимвол вводится сочетанием клавиш Ctrl+Shift+Пробел и отображается в виде кружка, а не точки:
К сожалению, функция СЖПРОБЕЛЫ (TRIM) удаляет только обычные пробелы. Для удаления неразрывных нужно использовать другие функции или макросы.
Формулы для удаления начальных и концевых пробелов
Если необходимо удалить только начальные пробелы (т.е. не трогать концевые и пробелы между словами), то придется делать это хитрыми формулами, т.к. готовой функции (по аналогии с функцией LTrim в VBA) в Microsoft Excel, к сожалению, нет.
Формула для удаления лишних пробелов только слева будет выглядеть так:
В английской версии =MID(A1;FIND(LEFT(TRIM(A1));A1);LEN(A1))
Формула для удаления лишних пробелов только справа будет чуть посложнее и должна вводиться уже как формула массива (с Ctrl+Shift+Enter):
В английском варианте это будет =LEFT(A1;MAX((MID(A1&REPT(" ";99);ROW(A1:A99),1);" ")*ROW(A1:A99)))
Ссылки по теме
На эту тему могу порекомендовать еще функцию ПЕЧСИМВ – она удаляет из текста всякие непечатные символы (символы которых нет на клавиатуре), а главное символ переноса строки, который вызывает много проблем при обработке данных в Excel.
Как правило, непечатные символы попадают в текст при ручном копировании данных из различных учетных систем, например 1С..
Для удаления пробелов в начале и в конце строки можно использовать условный оператор ЕСЛИ.
До этого желательно несколько раз с помощью поиска и замены убрать двойные пробелы.
Пробел в начале строки: =ЕСЛИ(ЛЕВСИМВ(А1;1)=" ";ПРАВСИМВ(А1;ДЛСТР(А1)-1))
Пробел в конце строки: =ЕСЛИ(ПРАВСИМВ(А1;1)=" ";ЛЕВСИМВ(А1;ДЛСТР(А1)-1))
А почему бы во всех примерах не добавить формулу в виде текста, а не в виде рисунка?
Т.е. как английский вариант, чтоб можно было удобно скопировать.
А еще так можно .
Для удаления пробелов в начале и в конце строки
можно использовать функцию =СЦЕПИТЬ(). Допустим, ячейка A1 проблемная ,у неё пробел вначале и в конце, нам это не надо.
Вставляем новый столбец и обрабатываем формулой =СЖПРОБЕЛЫ(A1), далее Выделить - Вст а вить зн а чения, теперь повторяющихся пробелов не будет, но в начале и в конце они остались.
Вставим еще один столбец, а в столбец формулу =СЦЕПИТЬ("&&";A1;"&&") , тут главное чтобы && не встречался в тексте. Опять Выделить - Вст а вить зн а чения . Теперь у нас в начале и конце строки не пробел, а дважды амперсанд+ пробел.
Далее Ctrl+F ищем &&[пробел] - заменить ничем, опять ищем [пробел]&& - заменить ничем, там где были пробелы они будут удалены вместе с амперсандами, там где пробелов не было остались наши &&
Проводим последний Поиск -замену Ctrl+F На й ти && Заменит ь ничем.
Не удобно искать лишние пробелы в чистом виде, а в сочетании с оригинальными символами это просто, мы их как бы помечаем,так можно выделять ненужные символы не только в одной ячейке, но и в массиве данных и разных столбцах. Иногда редактируемые данные не в одном столбце , а в разных. Вместо сложных вычислений первой/последней непустой ячейки, данные сначала объединяем в одну ячейку, с использованием хорошего разделителя, обрабатываем, а потом снова разделяем через "Текст по столбцам".
Читайте также: