Добавить символ перед каждым словом в ячейке Excel можно формулой =ЕСЛИ(СЖПРОБЕЛЫ(A2)="";"";"+"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";" +")): ПОДСТАВИТЬ заменяет каждый пробел на пробел с символом, а «+» в начале добавляется к первому слову. Запятые между словами ставит =ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";", "), а чтобы добавить символ в начало или конец всей ячейки, достаточно =A2&",". Вставить символы до и после каждого слова без формул сразу во всём диапазоне можно процедурой !SEMTools в 2 клика.
Я уже рассматривал похожую задачу — как добавить символ, слово или текст во все ячейки столбца. Там текст вставляется один раз на всю ячейку: в начало, в конец или сразу и в начало, и в конец — одинаковый или разный. Здесь задача звучит иначе — нужно быстро вставить какой-то символ перед каждым словом, т.е. символ вставится не единожды, а столько раз, сколько слов в ячейке. В случаях, когда в ячейке одно слово, задачи равнозначны.
Один из ярких примеров задачи - простановка в ключевых словах для рекламы в Яндекс Директ операторов "!" перед каждым словом. Это одна из полезных практик, позволяющая отключить показы по синонимам. Популярны также запросы о том, как проставить запятые после каждого слова или заключить каждое слово в ячейке в кавычки. Последнее по сути означает две последовательные операции - добавление символа кавычек перед каждым словом, и затем после (или наоборот).
Символ перед каждым словом в ячейке - Excel формула
Для начала определимся с понятиями и будем считать словом любой набор символов в ячейке, границей которого является или начало строки, или пробел, или конец строки. Т.е. если перед буквами уже стоят какие-то символы (например, кавычка или скобка), новый символ встанет перед ними, а не между ними и буквами. В такой конфигурации задача решаема довольно просто. Нам нужно будет учесть 4 момента:
- Если в ячейке слов нет вообще - в ячейку нельзя ничего добавлять;
- Если в ячейке одно слово, достаточно просто вставить перед ним символ, обращаясь к нему как к ячейке;
- Если слов 2 и более - пробел между ними можно заменять на пробел и необходимый символ, это формально и будет добавлением символа перед каждым словом, кроме первого;
- На всякий случай лучше избавиться от лишних пробелов между словами, иначе предыдущий этап создаст и лишние символы в ячейке.
Теперь, когда все нюансы ясны, рассмотрим соответствующие необходимые функции:
- ЕСЛИ поможет нам составить условие проверки и дать Excel дальнейшие инструкции в зависимости от ее результата;
- СЦЕПИТЬ (или просто "&") добавит символ перед значением ячейки;
- ПОДСТАВИТЬ поможет заменить пробелы на пробел и необходимый для вставки перед словами символ;
- СЖПРОБЕЛЫ поможет перед этим избавиться от лишних пробелов.
Смотрите пример создания составной формулы из этих функций:
Итак, итоговая универсальная формула:
=ЕСЛИ(СЖПРОБЕЛЫ(A1)="";"";D1&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";" "&D1))
Здесь D1 — ячейка, содержащая необходимый символ. Можно не обращаться к ячейке, а "захардкодить" его внутрь формулы вручную. Например, вставка восклицательного знака перед каждым словом будет выглядеть так:
=ЕСЛИ(СЖПРОБЕЛЫ(A1)="";"";"!"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";" !"))
Символ после каждого слова в ячейке - формула
Чтобы вставить символ после каждого слова в ячейке (например, запятую или точку с запятой как общепринятые символы-разделители), можно использовать аналогичную формулу в легкой модификации.
=ЕСЛИ(СЖПРОБЕЛЫ(A1)="";"";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";D1&" ")&D1)
Здесь в ячейке D1 указанный символ. Вот как формула работает на примере. Обратите внимание: в примере ссылка на D1 не абсолютная, поэтому символ пришлось протянуть вниз вместе с формулой. Удобнее закрепить ссылку — $D$1, — тогда символ достаточно указать один раз.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Фраза | Плюс перед словами | Запятые между словами | Каждое слово в кавычках |
| 2 | купить диван | +купить +диван=ЕСЛИ(СЖПРОБЕЛЫ(A2)="";"";"+"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";" +")) | купить, диван=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";", ") | "купить" "диван"=ЕСЛИ(СЖПРОБЕЛЫ(A2)="";"";""""&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A2);" ";""" """)&"""") |
| 3 | диван угловой недорого | +диван +угловой +недорого=ЕСЛИ(СЖПРОБЕЛЫ(A3)="";"";"+"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A3);" ";" +")) | диван, угловой, недорого=ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A3);" ";", ") | "диван" "угловой" "недорого"=ЕСЛИ(СЖПРОБЕЛЫ(A3)="";"";""""&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A3);" ";""" """)&"""") |
Кавычка внутри формулы записывается удвоенной: """" — это текст из одной кавычки. Вместо этого можно использовать СИМВОЛ(34).
В английском Excel: =IF(TRIM(A2)="","","+"&SUBSTITUTE(TRIM(A2)," "," +"))
Добавить запятую или кавычки в каждую ячейку
Если символ нужен не у каждого слова, а в конце или начале всей ячейки, формула проще: =A2&"," добавит запятую в конец, =СИМВОЛ(34)&A2&СИМВОЛ(34) заключит ячейку в кавычки. Подробнее — в статье как добавить текст во все ячейки.
Вставить пробелы между словами
Если слова «слиплись» без пробелов (например, «КупитьДиван»), разделить их по заглавным буквам можно регулярным выражением: =REGEXREPLACE(A2;"([а-яёa-z])([А-ЯЁA-Z])";"$1 $2") вставит пробел перед каждой заглавной буквой после строчной. Функция доступна в !SEMTools, а в Excel 365 — встроенная РЕГЗАМЕНИТЬ.
Вставить символы до и после каждого слова в 2 клика
Для тех, кто любит экономить время и не мучить себя запоминанием, вводом или даже копированием длинных формул, было разработано решение в надстройке !SEMTools. Запустить вставку символа до или после всех слов выделенных ячеек можно в 1 клик из меню «Изменить слова» в группе «ИЗМЕНИТЬ».
В примере ниже мы решаем импровизированную задачу — закавычиваем каждое слово в ячейках столбца. Для этого процедура запускается дважды с символом кавычки: сначала «Перед каждым словом», затем «После каждого слова».
А если закавычить нужно ячейку целиком, а не каждое слово, хватит одного запуска: ИЗМЕНИТЬ → Ячейки → Вставить → «В конце и в начале (одно и то же)».
Почему символы добавляются не туда
- Символы появились подряд или в начале ячейки. Между словами было несколько пробелов или пробел в начале. Формулы из статьи используют СЖПРОБЕЛЫ — не убирайте её.
- Символ не добавился в пустые ячейки — так и задумано: проверка ЕСЛИ(СЖПРОБЕЛЫ(A1)="";"") не даёт «одинокому» символу появиться в пустой ячейке.
- Формула с кавычками выдаёт ошибку. Кавычку внутри текста формулы нужно удваивать или заменить на СИМВОЛ(34).
- Перед знаком препинания тоже появился символ. Формула считает словом всё между пробелами, включая отдельно стоящее тире. Сначала удалите лишнюю пунктуацию.
Символы до и после слов — частые вопросы
Как добавить символ перед каждым словом в ячейке?
Формулой =ЕСЛИ(СЖПРОБЕЛЫ(A1)="";"";"+"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";" +")) — замените «+» на нужный символ.
Как поставить запятые между словами?
Формулой =ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";", ") — каждый пробел станет запятой с пробелом.
Как добавить запятую в конец каждой ячейки?
Формулой =A1&"," в соседнем столбце, затем замените формулы значениями. Или процедурой !SEMTools «Вставить в конце» прямо на месте.
Как заключить каждое слово в кавычки?
Формулой с ПОДСТАВИТЬ из примера выше или двумя запусками процедуры !SEMTools: «Перед каждым словом» и «После каждого слова» с символом кавычки.
Вставить символы перед, после или вокруг каждого слова, а также в начало и конец ячеек сразу во всём диапазоне можно в 2 клика с !SEMTools — без формул и замены значениями.
!SEMTools решает эту и сотни других задач в пару кликов!
Итоги: символы до и после каждого слова
Надеюсь, материал был для вас полезен, вы научились вставлять символы перед и после каждого слова с помощью стандартных формул Excel и обратили внимание на простое решение задачи с помощью моей надстройки. Наверняка вас могут заинтересовать другие способы изменить слова в Excel:
Если в ходе добавления символов вы добавили что-то лишнее, почитайте про способы быстро удалить все символы, кроме букв и цифр. А для PPC-специалистов, о которых зашла речь в самом начале, есть еще более интересные и быстрые решения. Примеры здесь: операторы ключевых слов Яндекс.Директа.