Excel добавить символ после

• Символ перед каждым словом в ячейке – Excel формула
• Символ после каждого слова в ячейке – формула
• Вставить символы до и после каждого слова в 2 клика
• Заключение

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

Один из ярких примеров задачи – простановка в ключевых словах для рекламы в Яндекс Директ операторов “!” перед каждым словом. Это одна из полезных практик, позволяющая отключить показы по синонимам. Популярны также запросы о том, как проставить запятые после каждого слова или заключить каждое слово в ячейке в кавычки. Последнее по сути означает две последовательные операции – добавление символа кавычек перед каждым словом, и затем после (или наоборот).

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

1. Если в ячейке слов нет вообще – в ячейку нельзя ничего добавлять;
2. Если в ячейке одно слово, достаточно просто вставить перед ним символ, обращаясь к нему как к ячейке;
3. Если слов 2 и более – пробел между ними можно заменять на пробел и необходимый символ, это формально и будет добавлением символа перед каждым словом, кроме первого;
4. На всякий случай лучше избавиться от лишних пробелов между словами, иначе предыдущий этап создаст и лишние символы в ячейке.

Теперь, когда все нюансы ясны, рассмотрим соответствующие необходимые функции:

1. ЕСЛИ поможет нам составить условие проверки и дать Excel дальнейшие инструкции в зависимости от ее результата;
2. СЦЕПИТЬ (или просто “&”) добавит символ перед значением ячейки;
3. ПОДСТАВИТЬ поможет заменить пробелы на пробел и необходимый для вставки перед словами символ;
4. СЖПРОБЕЛЫ поможет перед этим избавиться от лишних пробелов.

Смотрите пример создания составной формулы из этих функций:

Итак, итоговая универсальная формула:

=ЕСЛИ(A1="";"";D1&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";" "&D1))

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

=ЕСЛИ(A1="";"";"!"&ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";" !"))

Символ после каждого слова в ячейке – формула

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

=ЕСЛИ(A1="";"";ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";D1&" ")&D1)

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

Вставить символы до и после каждого слова в 2 клика

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

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

Заключение

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

• Каждое слово с заглавной буквы в Excel;
• Склонение по падежам в Excel.

Если в ходе добавления символов вы добавили что-то лишнее, почитайте про способы быстро удалить все символы, кроме букв и цифр. А для PPC-специалистов, о которых зашла речь в самом начале, есть еще более интересные и быстрые решения. Примеры здесь: операторы ключевых слов Яндекс.Директа.

Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
С !SEMTools нет необходимости прописывать сложные формулы. Решайте рутинные рабочие задачи за пару кликов!

Уважаемые форумчане. есть некоторая проблемка (задача) которую я самостоятельно никак не могу решить вот уже третьи сутки бороздя по просторам всемогущего интернета   суть проблемы: в каждую ячейку одного столбца «В» (а их 40 000) после пятого знака нужно вставить тире «-» (вот как выглядет 454587а253, а нужно так 45458-7а253).. есть одно маленькое «Но» в некоторых ячейках меньше 5 букв (цифр). те готовые решения которые видел в интернете из-за своей криворукости не смог прилепить (зависали макросы и выдавали ошибки). прошу помощи господа )))) Большое вам человеческое спасибо. С Уважением Димон )

V Пользователь Сообщений: 5018 Регистрация: 22.12.2012	#2 29.04.2013 17:20:17 формула подойдет? Код =ЕСЛИ(ДЛСТР(A1)<5;A1;ПСТР(A1;1;5)&"-"&ПСТР(A1;6;99))

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

V Пользователь Сообщений: 5018 Регистрация: 22.12.2012	1. например значение в A1 формулу вставляем в любую ячейку первой строки. 2. становимся на ячейку с формулой, наводим курсор на правый нижний угол ячейки — курсор изменит вид на крестик- жмем ЛКМ и тянем вниз на нужное к-во строк. п.с. в формуле замените ДЛСТР(A1)<5 на ДЛСТР(A1)<6 в формуле A1 это ячейка где находится значение. Изменено: V — 29.04.2013 17:36:38

	Большущее человеческое спасибо. разобрался. тему можно закрывать )

Sklad Пользователь Сообщений: 10 Регистрация: 02.09.2016	Доброго времени суток. У меня похожая ситуация. Вот такой столбец мне нужен: 21-6329-6894 21-3597-8108 21-3597-8119 21-3597-8130 мне нужно что бы «-» проставлялось после 2го и 6го знака. Как я поняла такая вставка возможна автоматически, но к сожалению знания не позволяют сделать самостоятельно, поэтому обращаюсь за помощью.

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

Владимир Пользователь Сообщений: 8196 Регистрация: 21.12.2012	=левсимв(а1;2)&»-«&пстр(а1;3;4)&»-«&правсимв(а1;4) «..Сладку ягоду рвали вместе, горьку ягоду я одна.»

Сергей Пользователь Сообщений: 11251 Регистрация: 01.01.1970	#9 02.09.2016 12:53:11 еще вариант фактически поменять Код =ЗАМЕНИТЬ(ЗАМЕНИТЬ(A1;3;;"-");8;;"-") визуально изменить форматом ячейки «00-0000-0000» Лень двигатель прогресса, доказано!!!

Sklad Пользователь Сообщений: 10 Регистрация: 02.09.2016	Сергей, Не совсем вас поняла, поэтому уточню свой вопрос. У меня есть «квитанции» номера их в формате 12-3456-7891 (две цифры тире четыре цифры тире четыре цифры) эти номера нужно набирать в большом количестве. И если бы была возможность вносить только цифры, а тире»-» exsel вносил бы автоматически, это бы ускорило процесс и свело бы на минимум ошибки.

Сергей Пользователь Сообщений: 11251 Регистрация: 01.01.1970	сделать форму ввода но это не ко мне Изменено: Сергей — 02.09.2016 12:56:33 (очепятки) Лень двигатель прогресса, доказано!!!

Кладовщик Пользователь Сообщений: 10 Регистрация: 02.09.2016	Надеюсь мне кто-нибудь поможет…

Сергей Пользователь Сообщений: 11251 Регистрация: 01.01.1970	все делается просто забиваете в поисковик «как сделать форму ввода в эксель» Лень двигатель прогресса, доказано!!!

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	#14 02.09.2016 13:09:21 Цитата Sklad написал: И если бы была возможность вносить только цифры, а тире»-» exsel вносил бы автоматически, Ввод в один столбец, формула в другом столбце

Кладовщик Пользователь Сообщений: 10 Регистрация: 02.09.2016	#15 02.09.2016 13:55:39 Цитата vikttur написал: Ввод в один столбец, формула в другом столбце ??? Вообще что-то не понятное для меня написали

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	#16 02.09.2016 13:58:49 Цитата в каждую ячейку одного столбца «В» … … внесены Ваши коды. В столбце С вписана формула, предложенная в этой теме. В столбце В без разделителей, в другом столбце получаете данные в нужном виде. Заменить формулы в столбце С на значения и вставить эти данные вместо исходных данных.

Кладовщик Пользователь Сообщений: 10 Регистрация: 02.09.2016	Нет тут все таки другое что-то надо,  т. к. такая процедура займет доп. время. Я думала упростить работу я вношу в табицу цифры, а так как их формат всегда одинаков Exsel подставлял бы один и тот же символ «-» через каждые 2 и 6 знаков

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	#18 02.09.2016 14:12:21 Цитата Кладовщик написал: я вношу в табицу цифры Цитата decebel2005 написал: в каждую ячейку одного столбца «В» (а их 40 000)… Вы уж определитесь… Или вручную 40000?

Кладовщик Пользователь Сообщений: 10 Регистрация: 02.09.2016	#19 02.09.2016 14:14:20 Нет извините вы не поняли, я не стала создавать отдельную тему Цитата Sklad написал: Доброго времени суток. У меня похожая ситуация. и ниже

vikttur Пользователь Сообщений: 47199 Регистрация: 15.09.2012	Если похожая, то формула в сообщении №8 и мой совет о доп. столбце поможет решить проблему. Если не поможет: значит, Вы всех запутали и нужно было свою тему создать.

decebel2005 Пользователь Сообщений: 20 Регистрация: 29.04.2013	#21 02.09.2016 15:58:11 Цитата vikttur написал: Вы уж определитесь… Или вручную 40000? Если вопрос мне — определился ) сделал заготовочку под свои параметры, благодаря помощи форумчан, и использую

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

kuklp Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	#23 02.09.2016 16:34:34 Цитата Кладовщик написал: я вношу в табицу цифры, а так как их формат всегда одинаков Exsel подставлял бы один и тот же символ «-» через каждые 2 и 6 знаков Это то, что Вам надо. Цитата Сергей написал: визуально изменить форматом ячейки «00-0000-0000» Я сам — дурнее всякого примера! …

tsv1981 Пользователь Сообщений: 7 Регистрация: 18.01.2018	Добрый день! Есть две колонки с большим количеством ячеек координат в формате (.N…. .E….). В идеале мне их нужно перевести в десятичный формат. Или путем добавления и заменой символов перевести их сначала в градусы, минуты, секунды. Подскажите пожалуйста как это сделать? Файл с некоторыми значениями приложил.

Апострофф Пользователь Сообщений: 720 Регистрация: 06.04.2015	tsv1981, делайте свою тему с внятным названием.

tsv1981 Пользователь Сообщений: 7 Регистрация: 18.01.2018

Sonetka2005 Пользователь Сообщений: 2 Регистрация: 20.02.2018

Добрый день! В исходных данных следующие значения: 1. Иванов Иван Иванович, Петров Петр Петрович, Сидоров Николай Степанович 2. Иванова Александра Ивановна, Голубев Макар Леонидович 3. Соколов Роман Викторович, Попова Алла Григорьевна и тд Необходимо в каждую строчку после первого ФИО вставить символ “#”, с удалением запятой: 1. Иванов Иван Иванович# Петров Петр Петрович, Сидоров Николай Степанович 2. Иванова Александра Ивановна# Голубев Макар Леонидович 3. Соколов Роман Викторович# Попова Алла Григорьевна В работе тысячи и тысячи строк, вручную естественно нет смысла проставлять, замена не помогает, т.к. при ней исчезает условие про первое ФИО. Подскажите решение вопроса с формулами, макросами. Заранее спасибо!!!!

Александр П. Пользователь Сообщений: 1147 Регистрация: 16.03.2016	#28 20.02.2018 04:03:56 Sonetka2005,Доброе утро Код =ПОДСТАВИТЬ(A1;",";"#";1) А1 — ячейка в которой расположены данные

Sonetka2005 Пользователь Сообщений: 2 Регистрация: 20.02.2018	Александр П., спасибо, но не работает. Я упустила, что между запятыми может быть различное количество данных: Иванов Петр Степанович#, 1953 г.р., Москва, не работает, пенсионер, состав семьи: сын Иванов Александр Петрович#, 1973 г.р, внучка — Алиса Александровна#, 2000 г.р.

Bema Пользователь Сообщений: 6750 Регистрация: 15.02.2016	#30 20.02.2018 20:22:01 Цитата Sonetka2005 написал: Я упустила Вот чтобы не упускать, нужно показывать файл-пример.   Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл

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

Навигация по этому руководству

1. Добавить в начало каждой ячейки 1.1 Добавить в начало ячеек с помощью формулы 1.2 Добавить в начало ячеек с помощью Flash Fill 1.3 Добавить в начало ячеек с помощью кода VBA

2. Добавить в конец каждой ячейки 2.1 Добавить в конец ячеек с помощью формулы 2.2 Добавить в конец ячеек с помощью Flash Fill 2.3 Добавить в конец ячеек с помощью кода VBA

3. Добавить в середину строки 3.1 Добавить после n-го символа строки по формуле 3.2 Добавить после n-го символа строки с помощью Flash Fill 3.3 Добавить после n-го символа строки с помощью кода VBA

4. Добавьте текст с помощью Kutools for Excel (добавьте символы в определенную позицию по мере необходимости) 4.1 Добавить текст перед первым символом или после последнего символа 4.2 Добавить текст в указанные позиции 4.3 Расширение утилиты «Добавить текст»

5. Добавьте текст до или после первого определенного текста 5.1 Добавить текст перед первым конкретным текстом 5.2 Добавить текст после первого определенного текста

6. Добавляйте символы до или после каждого слова. 6.1 Добавить символ(ы) до или после каждого слова по формуле 6.2 Добавляйте символы до или после каждого слова с помощью функции «Найти и заменить». 6.3 Добавить символ(ы) до или после каждого слова по коду VBA

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

8. Добавляйте символы между каждым словом

9. Добавьте символ между текстом и цифрой 9.1 Добавить символ между текстом и цифрой с помощью формулы 9.2 Добавляйте символы между текстом и цифрами с помощью Flash Fill

10. Добавьте тире к номерам телефонов/социальным номерам 10.1 Добавление тире к числам по формуле 10.2 Добавление тире к числам с помощью форматирования ячеек 10.3 Добавьте тире к номеру телефона с помощью команды Добавить текст Kutools for Excel

11. Добавьте ведущие нули в ячейки, чтобы зафиксировать длину 11.1 Добавление ведущих нулей в ячейки с помощью функции «Формат ячеек» 11.2 Добавление ведущих нулей к ячейкам с формулой

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

13. Добавьте пробел в конце текста

14. Добавьте кавычки/скобки вокруг текста или чисел 14.1 Добавление кавычек/скобок по формату ячеек 14.2 Добавьте кавычки/скобки вокруг формул 14.3 Добавление кавычек/скобок с помощью VBA

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

Внимание

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

Нажмите, чтобы загрузить образец файла

---

В этой части перечислены различные методы добавления символов в начало каждой ячейки, как показано ниже:

1.1 Добавить в начало ячеек с помощью формулы

---

Здесь вы можете выбрать одну из трех следующих формул:

Формула 1 Оператор конкатенации «&»

Соедините несколько текстов вместе с помощью символа амперсанда «&».

«текст»&ячейка

Формула2 СЦЕПИТЬ функцию

Функция СЦЕПИТЬ используется для объединения текстов.

СЦЕПИТЬ(«текст»,ячейка)

Формула3 Функция CONCAT

Это новая функция, которая появляется только в Excel 2019, Office 365 и Excel в Интернете.

СЦЕП («текст», ячейка)

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

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

«&»

=$E$3&B3 or =»Айфон»&B3

СЦЕПИТЬ функция

=СЦЕПИТЬ($E$3,B3) or = СЦЕПИТЬ («Айфон», B3)

Функция CONCAT

=СЦЕП($E$3;B3) or = СЦЕП («Айфон», B3)

Нажмите Enter чтобы получить результат, затем перетащите маркер автозаполнения вниз, чтобы добавить текст в каждую ячейку диапазона B3:B6.

 Внимание: текст должен быть заключен в двойные кавычки, иначе формула возвращает значение ошибки #ИМЯ?. Если вы используете ссылку на ячейку для текста, не забудьте использовать абсолютную ссылку, вы можете нажать клавишу F4, чтобы изменить относительную ссылку на абсолютную ссылку.

1.2 Добавить в начало ячеек с помощью Flash Fill

---

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

Выберите ячейку рядом с первыми исходными данными, вручную введите первые данные с текстом префикса, как показано ниже:

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

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

1.3 Добавить в начало ячеек с помощью кода VBA

---

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

1. Выберите диапазон ячеек, в который вы хотите добавить текст префикса, затем нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Затем во всплывающем окне нажмите Вставить > Модули чтобы вставить пустой новый модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: добавить в начало ячеек

Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub

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

4. Нажмите F5 ключ или щелкните Run кнопка  в окне для активации кода VBA.

Теперь во все выделенные ячейки был добавлен текст «Iphone» в начале.

---

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

2.1 Добавить в конец ячеек с помощью формулы

---

Здесь вы можете выбрать одну из трех следующих формул:

Формула 1 Оператор конкатенации «&»

Соедините несколько текстов вместе с помощью символа амперсанда «&».

Ячейка&»текст»

Формула2 СЦЕПИТЬ функцию

Функция СЦЕПИТЬ используется для объединения текстов.

СЦЕПИТЬ(ячейка,»текст»)

Формула3 Функция CONCAT

Это новая функция, которая появляется только в Excel 2019, Office 365 и Excel онлайн.

СЦЕП(ячейка,»текст»)

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

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

«&»

=B3&$E$3 or =B3&»кг»

СЦЕПИТЬ функция

= СЦЕПИТЬ (B3, $E$3) or =СЦЕПИТЬ(B3, «кг»)

Функция CONCAT

=СЦЕП(B3, $E$3) or =СЦЕП(B3, «кг»)

Нажмите Enter чтобы получить результат, затем перетащите маркер автозаполнения вниз, чтобы добавить текст в конец каждой ячейки диапазона B3:B6.

 Внимание: текст должен быть заключен в двойные кавычки, иначе формула возвращает значение ошибки #ИМЯ?. Если вы используете ссылку на ячейку для текста, не забудьте использовать абсолютную ссылку, вы можете нажать клавишу F4, чтобы изменить относительную ссылку на абсолютную ссылку.

2.2 Добавить в конец ячеек с помощью Flash Fill

---

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

Выберите ячейку рядом с первыми исходными данными, вручную введите первые данные с текстом суффикса, как показано ниже:

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

2.3 Добавить в конец ячеек с помощью кода VBA

---

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

1. Выберите диапазон ячеек, в которые вы хотите добавить текст суффикса, затем нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Затем во всплывающем окне нажмите Вставить > Модули чтобы вставить пустой новый модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: добавить в конец ячеек

Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub

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

4. Нажмите F5 ключ или щелкните Run кнопка  в окне для активации кода VBA.

Теперь в конце каждой ячейки добавлен текст «Кг».

---

Для большинства из вас легко добавить текст в начало или конец ячейки, но добавить текст в середину ячейки может быть довольно сложно. Возьмем пример, как показано ниже, добавив разделитель, например двоеточие: в конце второго символа каждой ячейки в диапазоне B3:B7.

3.1 Добавить после n-го символа строки по формуле

---

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

Formula1 Объедините функции LEFT и RIGHT

ВЛЕВО(ячейка, n) & «текст» & ВПРАВО(ячейка, ДЛСТР(ячейка) -n)

Formula2 COMBINE CONCATENATE(CONCAT), RIGHT и LEN функции

СЦЕПИТЬ(ВЛЕВО(ячейка, n), «текст», ВПРАВО(ячейка, ДЛСТР(ячейка) -n))

Or

СЦЕП(ЛЕВАЯ(ячейка, n), «текст», ПРАВАЯ(ячейка, ДЛСТР(ячейка) -n))

Формула3 ЗАМЕНИТЬ функцию

ЗАМЕНИТЬ(ячейка, n+1, 0, «текст»)

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

Для решения задачи, упомянутой выше, используйте следующие формулы:

Объедините функции ВЛЕВО и ВПРАВО

=ВЛЕВО(B3, 2) & «:» & ВПРАВО(B3, ДЛСТР(B3) -2) or =ВЛЕВО(B3, 2) & $E$3 & ВПРАВО(B3, ДЛСТР(B3) -2)

ОБЪЕДИНИТЬ СЦЕПИТЬ (СЦЕПИТЬ), ПРАВО и ДЛСТР функции

= СЦЕПИТЬ (ЛЕВО (B3, 2), «:», ПРАВО (B3, ДЛСТР (B3) -2)) or =СЦЕП (ЛЕВОЕ(B3, 2), «:», ПРАВОЕ(B3, ДЛСТР(B3) -2))

ЗАМЕНИТЬ функцию

=ЗАМЕНИТЬ(B3, 2+1, 0, «:») or =ЗАМЕНИТЬ(B3, 2+1, 0, $E$3)

Нажмите Enter чтобы получить результат, затем перетащите маркер автозаполнения вниз, чтобы добавить текст после второго символа каждой ячейки диапазона B3:B7.

 Внимание: текст должен быть заключен в двойные кавычки, иначе формула возвращает значение ошибки #ИМЯ?. Если вы используете ссылку на ячейку для текста, не забудьте использовать абсолютную ссылку, вы можете нажать клавишу F4, чтобы изменить относительную ссылку на абсолютную ссылку.

3.2 Добавить после n-го символа строки с помощью Flash Fill

---

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

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

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

Внимание: Если вы находитесь в системе MAC, нажмите клавиши Command + E. 

3.3 Добавить после n-го символа строки с помощью кода VBA

---

Код VBA также может выполнять эту работу.

1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Затем во всплывающем окне нажмите Вставить > Модули чтобы вставить пустой новый модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: добавить в середину ячеек

Sub AddToMidduleOfString()
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    For Each Rng In WorkRng
        Rng.Value = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
    Next
End Sub

Внимание: в скрипте кода, 2 это число определяет позицию, после которой вы хотите добавить текст, : это текст, который вы хотите добавить, 3 это сумма 2 и длины текста, который вы добавляете. Возьмем другой пример, чтобы добавить «и» после первого символа строки «AB», сценарий должен быть Rng.Value = VBA.Left(Rng.Value, 1) и «и» & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) — 1) 

4. Нажмите F5 ключ или щелкните Run кнопка  в окне для активации кода VBA. Появится диалоговое окно для выбора ячеек для добавления текста.

5. Нажмите OK. Теперь после второго символа каждой ячейки в диапазоне B19:B23 добавлен текст «:».

---

Если вы устали от формул и VBA, вы можете попробовать мощный и удобный инструмент, Kutools for Excel, который может помочь вам с его Добавить текст функцию.

Эта функция «Добавить текст» может:

• Добавить текст перед первым символом
• Добавить текст после последнего символа
• Добавить текст в указанные позиции
• Расширение
  Добавить текст перед заглавными буквами
  Добавить текст перед строчными буквами
  Добавить текст перед прописными/строчными буквами
  Добавить текст перед числовыми символами

Нажмите, чтобы получить 30-дневную бесплатную пробную версию прямо сейчас.

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

4.1 Добавить текст перед первым символом или после последнего символа

---

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

Нажмите Применить or Ok чтобы завершить добавление.

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

Нажмите Применить or Ok чтобы завершить добавление.

4.2 Добавить текст в указанные позиции

---

Иногда вам может понадобиться добавить текст в середину ячейки или добавить текст в несколько позиций ячейки. Предположим, добавлен разделитель «-» после третьей и седьмой цифр социального номера, как показано ниже:

В разделе Добавить текст диалоговое окно, введите текст в Текст текстовое поле и отметьте Указывать вариант, затем введите позиции, используя запятую для разделения в текстовое поле, а затем нажмите Ok or Применить.

4.3 Расширение утилиты «Добавить текст»

---

Помимо добавления текста в нормальные позиции (начало, конец и указанная позиция), Добавить текст утилита также поддерживает добавление текста

• Перед заглавными буквами
• Перед строчными буквами
• Перед прописными/строчными буквами
• Перед числовыми символами

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

Кроме функции «Добавить текст», Kutools for Excel имеет более 300 других полезных функций, которые могут помочь вам в различных работах Excel, скачайте бесплатно прямо сейчас.

---

В этой части представлены формулы для добавления текста до или после первого появившегося определенного текста в Excel.

5.1 Добавить текст перед первым конкретным текстом

---

Например, вы хотите добавить текст «(продажа)» перед первым текстом «цена» в ячейке, как показано ниже:

В Excel эту задачу могут решить функции ЗАМЕНИТЬ и ПОДСТАВИТЬ.

ЗАМЕНИТЬ функцию

ЗАМЕНИТЬ(ячейка,НАЙТИ(«найти_текст»,ячейка),0,»добавить_текст»)

ЗАМЕНА функция

ПОДСТАВИТЬ(ячейка,»найти_текст»,»заменить_текст»,1)

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

=ЗАМЕНИТЬ(B4,НАЙТИ(«цена»,B4),0,»(продажа)»)

Or

=ПОДСТАВИТЬ(B4,»цена»,»(продажная)цена»,1)

Аргументы формулы в этом случае

Ячейка: B4,

Find_text: цена,

Add_text: (продажа),

Replace_text:(распродажа)цена.

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

5.2 Добавить текст после первого определенного текста

---

Например, вы хотите добавить символ валюты «$» после первого двоеточия «:» в ячейке, как показано ниже:

В Excel эту задачу могут решить функции ЗАМЕНИТЬ и ПОДСТАВИТЬ.

ЗАМЕНИТЬ функцию

ЗАМЕНИТЬ(ячейка,НАЙТИ(«найти_текст»,ячейка),найти_длину_текста,»заменить_текст»)

ЗАМЕНА функция

ПОДСТАВИТЬ(ячейка,»найти_текст»,»заменить_текст»)

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

=ЗАМЕНИТЬ(B12,НАЙТИ(«:»,B12),1,»:$»)

Or

=ПОДСТАВИТЬ(B12,»:»,»:$»)

Аргументы формулы в этом случае

Ячейка: B12,

Найти_текст: :,

Заменить_текст::$.

Find_text_length:1

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

---

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

6.1 Добавить символ(ы) до или после каждого слова по формуле

---

Для решения этой задачи можно использовать функцию ПОДСТАВИТЬ, общие формулы такие:

Добавлять перед каждым словом

«символ»&ПОДСТАВИТЬ(строка, » «,» символ»)

Добавлять после каждого слова

ПОДСТАВИТЬ(строка,» «,»символ»)&»символ»

Предположим, вы добавляете «+» перед каждым словом в списке B3:B6, используйте следующую формулу:

=»+»&ПОДСТАВИТЬ(B3, » «,» +»)

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

Если добавить ($) после каждого слова списка B11:B14, используйте следующую формулу:

=ПОДСТАВИТЬ(B11,» «,»($) «)&»($)»

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

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

---

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

Добавлять перед каждым словом, исключая первое

Добавлять после каждого слова, исключая последнее

1. Выберите ячейки, в которые вы хотите добавить символы до или после каждого слова, затем нажмите Ctrl + H ключи для включения Найти и заменить Диалог.

2. Введите пробел в Найти то, что текстовое поле, затем введите новые символы с пробелом в Заменить текстовое поле и, наконец, щелкните Заменить все кнопку.

Эксплуатация	Общие	Пример
Добавляйте символы перед каждым словом	Добавить новые символы после пробела	Добавлять (продажа) перед каждым словом

Эксплуатация	Общие	Пример
Добавляйте символы после каждого слова	Новые символы с пробелом	Добавить — после каждого слова

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

1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне нажмите Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте приведенный ниже код VBA в модуль.

Добавлять перед каждым словом

Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
   
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = xInStr & Trim(xStr)
                Else
                    xValue = xValue & " " & xInStr & Trim(xStr)
                    End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

Добавлять после каждого слова

Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
    On Error Resume Next
 
    Set xSRg = Application.Selection
    Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
    If xRg Is Nothing Then Exit Sub
   
    xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
    If StrPtr(xInStr) = 0 Then Exit Sub
   
    Application.ScreenUpdating = False
    For Each xCell In xRg
        xArr = Split(xCell.Text, " ")
        xValue = ""
        For Each xStr In xArr
            If Trim(xStr) <> "" Then
                If xValue = "" Then
                    xValue = Trim(xStr) & xInStr
                Else
                    xValue = xValue & " " & Trim(xStr) & xInStr
                End If
            End If
        Next
        xCell.Value = xValue
    Next
    Application.ScreenUpdating = True
End Sub

4. Нажмите F5 ключ для запуска кода (или вы можете нажать Run кнопка   ), появится диалоговое окно с просьбой выбрать непрерывные ячейки для работы, щелкните OK.

5. Во втором всплывающем диалоговом окне введите символы, которые вы хотите добавить, нажмите OK.

Затем символы будут добавлены в начале или в конце каждого слова.

---

Иногда вам может понадобиться добавить текст между каждым символом. Здесь, в этой части, перечислены два сценария: один — добавить текст между каждым символом, другой — добавить пробел между каждым числом.

7.1 Добавьте текст между каждым символом/цифрой с помощью определяемой пользователем функции

---

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

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

1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне нажмите Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте приведенный ниже код VBA в модуль.

Добавить текст между символами

Function AddText(Str As String) As String
    Dim i As Long
    For i = 1 To Len(Str)
        AddText = AddText & Mid(Str, i, 1) & " "
    Next i
    AddSpace = Trim(AddSpace)
End Function

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

=Добавитьтекст(B3)

B3 — это ячейка, содержащая строку, в которую вы хотите добавить пробел между символами.

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

7.2 Добавьте пробел между каждым числом с помощью формулы

---

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

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

Предположим, что это список 8-значных чисел в диапазоне B10:B12, выберите ячейку, в которую будет помещен результат, введите ниже формулу:

=ТЕКСТ(B10;»# # # # # # # #»)

Нажмите Enter , затем перетащите маркер автозаполнения на другие ячейки.

Если длина чисел равна 5, формула будет изменена как =ТЕКСТ(B10;»# # # # #»).

---

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

1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Во всплывающем окне нажмите Вставить > Модули чтобы вставить новый пустой модуль.

3. Скопируйте и вставьте приведенный ниже код VBA в модуль.

Добавляйте символы между словами

Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function

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

=Добавитьсимволов(B3)

B3 — это ячейка, содержащая строку, в которую вы хотите добавить пробел между словами.

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

 Внимание: в коде VBA вы можете изменить «» на другие тексты, как вам нужно, предположим, изменить его на «-« добавить — между словами.

---

Чтобы добавить определенные символы между текстом и цифрами, как показано на снимке экрана ниже, здесь представлены два метода.

9.1 Добавить символ между текстом и цифрой с помощью формулы

---

Если текст находится перед числами, общая формула

TRIM(REPLACE(string,MIN(FIND({1,2,3,4,5,6,7,8,9,0},string&»1234567890″)),0,»-«))

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

=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&»1234567890″)),0,»-«))

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

Если цифры стоят перед текстом, общая формула

ВЛЕВО(строка,СУММ(ДЛСТР(строка)-ДЛСТР(ПОДСТАВИТЬ(строка,{0,1,2,3,4,5,6,7,8,9},»»))))&».»&ПРАВО (строка,ДЛСТР(B13)-СУММ(ДЛСТР(строка)-ДЛСТР(ПОДСТАВИТЬ(строка, {0,1,2,3,4,5,6,7,8,9}, «»))))

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

=LEFT(B13,SUM(LEN(B13)-LEN(SUBSTITUTE(B13,{0,1,2,3,4,5,6,7,8,9},»»))))&».»&RIGHT(B13,LEN(B13)- SUM(LEN(B13)-LEN(SUBSTITUTE(B13, {0,1,2,3,4,5,6,7,8,9}, «»))))

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

9.2 Добавляйте символы между текстом и цифрами с помощью Flash Fill

---

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

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

И нажмите Ctrl + E клавиши, чтобы включить мгновенное заполнение.

Расширение

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

---

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

10.1 Добавление тире к числам по формуле

---

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

ЗАМЕНИТЬ функцию

=ЗАМЕНИТЬ(ЗАМЕНИТЬ(B3,4,0,»-«),8,0,»-«)

ТЕКСТ функция

=ТЕКСТ(B3,»???-???-???»)

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

10.2 Добавление тире к числам с помощью форматирования ячеек

---

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

1. Выберите числа, к которым вы хотите добавить дефисы, затем нажмите Ctrl + 1 ключи для включения Формат ячеек Диалог.

2. в Формат ячеек диалог под Число , выберите На заказ из Категория .

3. Затем в правом Тип раздел, тип ###-###-### в текстовое поле щелкните OK.

Теперь к выделенным числам добавлены прочерки.

10.3 Добавьте тире к номеру телефона с помощью команды Добавить текст Kutools for Excel

---

Если у вас есть Kutools for Excel установленный в Excel, Добавить текст функция также может оказать вам услугу.

1. Выберите ячейки, содержащие числа, необходимые для добавления прочерков, нажмите Кутулс > Текст > Добавить текст.

2. в Добавить текст введите тире или другой разделитель в Текст текстовое поле, выберите Указывать , затем введите позиции, после которых вы хотите добавить тире, и разделите их запятыми в текстовое поле ниже.

3. Нажмите Ok или Применить. Затем к выбранным числам были добавлены прочерки.

Бесплатно скачать Добавить текст для 30-дневной пробной версии.

---

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

11.1 Добавление ведущих нулей в ячейки с помощью функции «Формат ячеек»

---

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

1. Выберите ячейки, в которые вы хотите добавить ведущие нули, затем нажмите Ctrl + 1 ключи для включения Формат ячеек Диалог.

2. в Формат ячеек диалог под Число , выберите На заказ из Категория .

3. Затем в правом Тип раздел, тип 0000 в текстовое поле (чтобы зафиксировать длину числа до 4 цифр, введите 0000, вы можете изменить ее на другую длину, например 5 цифр, введите 00000), щелкните OK.

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

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

11.2 Добавление ведущих нулей к ячейкам с формулой

---

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

Вот три функции могут помочь вам.

Формула1 ТЕКСТ функция

ТЕКСТ(число,»00…»)

Формула2 ПРАВАЯ функция

ВПРАВО(«00…»&число,длина)

Базовая функция Formula3

ОСНОВАНИЕ(число,10,длина)

Количество нулей в формуле равно длине числа.

Здесь вы устанавливаете 4 цифры в качестве длины чисел, пожалуйста, используйте формулы, как показано ниже:

= ТЕКСТ (B10; «0000»)

=ВПРАВО(«0000″&B10,4)

= ОСНОВА (B10,10,4)

Нажмите Enter и перетащите маркер автозаполнения вниз.

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

---

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

Функция ПОВТОР

число&REPT(«0»,длина-LEN(число))

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

Пожалуйста, используйте следующую формулу:

=B3&ПОВТОР(«0»,5-ДЛСТР(B3))

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

---

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

Для решения этой задачи функция REPT может оказать вам услугу.

ВЛЕВО(текст и ПОВТОР(» «,max_length),max_length)

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

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

=ВЛЕВО(B3 & ПОВТОР(» «,10),10)

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

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

---

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

14.1 Добавление кавычек/скобок по формату ячеек

---

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

1. Выберите ячейки, в которые вы хотите добавить кавычки, нажмите Ctrl + 1 ключи для включения Формат ячеек Диалог.

2. в Формат ячеек диалога под Число , выберите На заказ из списка Категория, затем перейдите в правый раздел, чтобы ввести «»» @ «»» в Тип текстовое окно. Нажмите OK.

Текст в выбранных ячейках был заключен в кавычки.

Если вы хотите добавить скобки вокруг текста, в текстовом поле «Тип» диалогового окна «Формат ячеек» используйте (@).

14.2 Добавьте кавычки/скобки вокруг формул

---

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

Использование и разъем

«»»»&текст&»»»»

Or

символ(34)&текст&символ(34)

В этом случае текст в ячейке B11, пожалуйста, используйте следующие формулы:

=»»»»&B11&»»»»

Or

=знак(34)&B11&символ(34)

Нажмите Enter и перетащите маркер автозаполнения вниз, чтобы заполнить ячейки.

Если вы хотите добавить скобки вокруг текста, используя формулу как «(«&текст&»)».

14.3 Добавление кавычек/скобок с помощью VBA

---

Если вам нужен код VBA для решения этой задачи, вот VBA.

1. Нажмите другой + F11 ключи для включения Microsoft Visual Basic для приложений окно.

2. Затем во всплывающем окне нажмите Вставить > Модули чтобы вставить пустой новый модуль.

3. Скопируйте и вставьте ниже код VBA в новый модуль.

VBA: добавьте кавычки вокруг текстов

Sub addquotationmarksorbrackets()
'UpdatebyExtendOffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = """" & Rng.Value & """"
Next
End Sub

 Внимание: в скрипте кода, «»»» & Rng.Value & «»»» указывает на добавление «» вокруг текста, если вы хотите добавить скобки () вокруг текста, измените его на «(» & Rng.Value & «)».

4. Нажмите F5 ключ или щелкните Run кнопка  в окне для активации кода VBA. Появится диалоговое окно для выбора ячеек для добавления кавычек.

5. Нажмите OK. Теперь вокруг текста в выделенных ячейках добавлены кавычки.

---

Иногда, чтобы помочь другим пользователям лучше понять данные, вам может потребоваться добавить текст в результаты формулы, как показано ниже. Чтобы включить текст в формулу, вы можете использовать кавычки, окружающие текст, и добавить соединитель &, чтобы объединить текст и формулу вместе.

Случай 1 добавить текст перед формулой

«Сегодня «&ТЕКСТ(СЕГОДНЯ(),»дддд, мммм дд»)

Случай 2 добавить текст после формула

ТЕКСТ(СЕЙЧАС(),»ЧЧ:ММ:СС»)&» — текущее время.»

Случай 3 добавить тексты с двух сторон формулы

«Сегодня продано &data&» кг.»

---

Дополнительные учебники по Excel:

Объединение нескольких рабочих книг/листов в одну Это руководство, в котором перечислены почти все сценарии объединения, с которыми вы можете столкнуться, и предоставлены относительно профессиональные решения для вас. Разделить текст, число и ячейки даты (разделить на несколько столбцов) Этот учебник разделен на три части: разделенные текстовые ячейки, разделенные числовые ячейки и разделенные ячейки даты. В каждой части представлены разные примеры, которые помогут вам понять, как справиться с задачей разделения при возникновении одной и той же проблемы.

Добавить текст в ячейки с текстом Excel.

​Смотрите также​​bob3​ знак дроби (/)​ ​ ТЕКСТОВОМ ФОРМАТЕ. Подскажите,какую​​ к исходному значению​ которая обрезает каждое​ формулы:​ применении. Если, например,​Очень часто данные набранные​
​ форматирование». Выбираем функцию​
​ подстановочных знаков читайте​ ​символ «Знак вопроса» в​ ​Excel​
​ молоку, т.д.), то​ способами можно посчитать​Excel​
​ пишем такую формулу.​Можно быстро​: Здравствуйте!​ и​ использовать формулу?​
​ ячейки A4. После​ исходное значение до​В результате каждый идентификатор​ каждое значение в​
​ в электронных таблицах​ «Правила выделенных ячеек»​ в статье «Примеры​Excel​(​ в пустой ячейке​ такие результаты, смотрите​.​ =A1&» «&»№» Получится​добавить слово в каждую​Подскажите, а как​число равное (Х+10)?​Юрий М​ чего обрезали каждый​
​ первых 10-ти чисел​
​ получил необходимое ему​​ поле «ID клиента»​
​ Excel используются для​ -> «Текст содержит».​ ​ функции «СУММЕСЛИМН» в​​(​
​*​ пишем такую формулу.​ в статье «Как​Вместо знака «&»​ так.​
​ ячейку​ можно добавить в​Должно получится что-то​: Файл посмотреть не​ идентификатор оставляя только​ от начала строки.​ количество нулей, чтобы​ должно иметь по​ заполнения файлов баз​
​ Заполняем так.​ Excel».​?​) означает любой текст​ Мы написали формулу​ объединить данные теста​ можно применить функцию​Чтобы быстро заполнить​Excel​ значение дополнительный символ,​ типа того:​ могу. Формула в​

excel-office.ru

Подстановочные знаки в Excel.

​ лишь по 10​​ Для этого во​ соответствовать правилу для​​ 10 чисел, то​ данных. Файлы такого​Нажимаем «ОК». Получилось так.​Как найти все слова​​). В формуле он​ ​ после слова, букв,​​ в ячейке В5.​ в ячейках в​ сцепления. Смотрите статью​
​ столбец B формулой​. Например, в каждую​ например ноль.​изначально в ячейке​ B1​ числовых знаков справой​ втором аргументе функции​ дальнейшего запыления поля​ для реализации данной​
​ типа часто требуют​Выделились все слова, в​ с разными окончаниями​ означает один любой​ которое мы написали​=СЧЁТЕСЛИ(A1:A10;»молок*») Как написать​
​ Excel».​ «Функция «СЦЕПИТЬ» в​ (скопировать вниз по​ заполненную ячейку нужно​В файле есть​ — «20»​=»0″&A1​ стороны номеров. Мы​ ЛЕВСИМВ указано число​ «ID клиента» при​
​ задачи нужно для​ ​ соблюдения правил заполнения.​​ которых есть слово​​ в​​ знак. Например, нам​ в формуле перед​ формулу с функцией​Можно в формулу​ Excel».​
​ столбцу), нажимаем левой​ добавить ещё какое-то​ ячейки, в которых​результат — «20/30».​maribel1​ добавили недостающие символы​
​ 10.​ импорте таблицы в​ каждого числа дописать​ Например, чтобы определенные​ «шуруп».​Excel.​​ нужно найти все​ звездочкой (например, «молок*»).​​ «СЧЕТЕСЛИ», читайте в​ ​Excel вставить символ, подстановочные​​Можно вставлять слова,​​ мышкой два раза​​ значение, слова, числа,​ номер отделения выглядит,​Nic70y​: не получается. так​ сначала строки необходимого​Если необходимо сделать так​ базу данных.​ соответственное количество нулей.​ поля данных имели​О других способах​Эти подстановочные знаки​ слова, которые начинаются​
​Можно сделать по-другому.​ статье «Функция «СЧЁТЕСЛИ»​ знаки​ символы, коды, номера​ по квадратику в​​ т.д.​ например «9042/012», а​: без файла не​ и остается в​ нам количества. Так​
​ чтобы нули были​​ Например, для идентификатора​ ​ определенную длину количества​
​ поиска в таблице​ можно вставлять не​ с буквы «с»​ В ячейке В2​ в Excеl». Нашлось​. Эти символы можно​ в начале ячейки,​
​ нижнем правом углу​ ​Первый способ.​
​ должен «9042/0012», вот​ очень понятно,​ ячейке=»0″&A1​ работает функция ПРАВСИМВ,​ автоматически добавлены не​Формула, которая изображена выше​ с номером 1234567​ символов. Поэтому часто​ Excel, читайте в​ только в формулы,​​ и заканчиваются на​
​ написать «молок*». А​ таких 4 слова.​ применить для поиска,​
​ перед имеющимся текстом.​ ячейки B1. Формула​Добавить текст в ячейки​ как добавить нолик​пишем рядом формулу:​
​kim​ обратно пропорционально для​ с правой стороны,​ на рисунке сначала​ нужно дописать три​
​ техника заполнения полей​ статье «Поиск в​ но и в​ буквы «ла» (сЕла,​ в ячейке В5​

excel-office.ru

Пример формулы для добавления символов в строке ячейки Excel

​В формуле написали слово​ в условном форматировании,​ Смотрите такой способ​ скопируется по всему​c текстом​ перед «/», причем​ =a1&»/30″​: Нужно установить сначала​ предыдущей функции ЛЕВСИМВ.​ а с левой​ добавляет к значению​ нуля и в​ данных числовыми значениями​ Excel» тут.​ строку поиска в​ сИла). В пустой​ написать такую формулу.​ «молок» и поставили​ др. Существую​

Автоматическое добавление количества символов в ячейке Excel

​ в статье «Объединить​ столбцу на расстояние​Excel.​ только там, где​потом если это​ формат «Общий», а​maribel1​ (например, так: 0001234567).​ ячейки A4 ряд​ результате получим корректную​ требует ввода дополнительных​Про других символы,​ диалоговом окне «Найти»​ ячейке (В4) напишем​ =СЧЁТЕСЛИ(A1:A10;B2) Получится так.​ звездочку (*) –​подстановочные знаки в​ слова из разных​ заполненных ячеек столбца.​У нас такая​ три символа после​ надо, копипастим -​

​ потом вводить формулу…​: Прикреплен файл с​ Тогда следует немного​ символов из 10-ти​ запись 1234567000 для​ нулей, чтобы во​

​ что они означают,​ на закладке «Главная».​ такую формулу. =СЧЁТЕСЛИ(A1:A10;»с?ла»)​Такой формулой можно выбрать​ это значит, что​Excel​ ячеек в текст​ О таком способе​

​ таблица с адресами​

​ «/».​ вставляем значения​maribel1​ примером. Например, есть​ изменить формулу, а​ нулей и в​ поля «ID клиента»​ всех значениях было​ где применяются, читайте​Как выделить ячейки в​ Получится так.​ весь товар из​

​ Excel будет считать​– это символы,​ Excel» здесь.​ копирования, смотрите статью​ в столбце А.​Заранее спасибо!​Pelena​: Огромное спасибо. Получилось!!!​ 10 строк в​

​ вместо функции ЛЕВСИМВ​ результате получаем новый​ в файле базы​ одинаковое число символов​ в статье «Символы​Excel с похожими словами.​Или напишем искомое слово​ таблицы с одним​ все ячейки со​ которые обозначают определенные​

​В Excel можно​ «Копирование в Excel».​Нам нужно дополнить в​vikttur​: или​ Доброй ночи!!!​ текстовом формате, состоящих​ использовать функцию ПРАВСИМВ:​ идентификатор. Каждый из​ данных.​ не зависимо от​ в формулах Excel».​Использовать такие знаки​ в ячейке В2​ названием и разными​ словами, которые начинаются​ действия.​ посчитать результат, если​

exceltable.com

к текстовой строке добавить символ

​Получится так.​​ эти ячейки столбца​:​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=A1&»/»&A1+10​noobik​ из 10 цифр.​Как видено на рисунке​ них теперь в​Ниже на рисунке изображено​ величины числа.​Какими способами можно​ можно и в​

​ «с?ла», а в​​ кодами (шуруп А1,​ на «молок», а​Например, когда нужно​
​ в ячейках анкеты,​

​Второй способ​​ A дополнительные слова,​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИОШИБКА(ПСТР(A3;НАЙТИ(«№»;A3)+2;5)&ТЕКСТ(ПСТР(A3;НАЙТИ(«/»;A3)+1;4);»0000″);»»)​gling​

​: Здравствуйте! Подскажите, пожалуйста,​​ Нужно в каждой​ на этот раз,​ любом случае имеет​

​ автоматическое дозаполнение недостающего​​В Excel подготовка и​ сравнить данные с​

planetaexcel.ru

Добавление символа к числу в ячейке (Формулы/Formulas)

​ условном форматировании. Выделим​​ ячейке В5 напишем​ шуруп 123, т.д.).​ дальше имеют разные​ найти в столбце​ теста, стоят не​.​
​ числа, т.д. Мы​
​bob3​: Наверно так =A1&»/»&A1+10​
​ как «глобально» добавить,​ строке к началу​
​ используя символ амперсант,​

​ не менее 10-ти​​ количества символов нулями​ заполнение данных дополнительными​
​ помощью диаграммы, смотрите​ диапазон. На закладке​
​ такую формулу. =СЧЁТЕСЛИ(A1:A10;B2)​Ещё один подстановочный​ окончания.​

​ слово в разных​​ цифры, а слова,​
​Добавить слово в каждую​

​ вставим знак «№».​​: Огромное Вам спасибо!!!​

​noobik​​ во множество ячеек,​

excelworld.ru

Добавить доп.символ в значение ячейки (Формулы/Formulas)

​ текста добавить цифру​​ мы добавили по​
​ чисел.​ в конец строки​ нулями являются достаточно​ в статье «Диаграмма​
​ «Главная» нажимаем на​О других способах применения​ знак – это​Символ «звездочка» в​ падежах (молоко, молоком,​ буквы, знаки. Какими​ ячейку​В ячейке B1​ Всё работает!!!​: Всем огромное спасибо)​
​ к числу (Х)​

​ 0. ЯЧЕЙКИ В​​ 10 нулей слева​​Далее применяется функция ЛЕВСИМВ,​

​ с помощью простой​​ простым способом в​ в Excel план-факт».​

excelworld.ru

​ кнопку функции «Условное​

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

Процедура вставки текста около формулы

Если просто попробовать вставить текст в одну ячейку с функцией, то при такой попытке Excel выдаст сообщение об ошибке в формуле и не позволит совершить такую вставку. Но существует два способа все-таки вставить текст рядом с формульным выражением. Первый из них заключается в применении амперсанда, а второй – в использовании функции СЦЕПИТЬ.

Способ 1: использование амперсанда

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

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

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



2. Сразу после формулы ставим знак амперсанд (&). Далее в кавычках записываем слово «рублей». При этом кавычки не будут отображаться в ячейке после числа выводимого формулой. Они просто служат указателем для программы, что это текст. Для того, чтобы вывести результат в ячейку, щелкаем по кнопке Enter на клавиатуре.



3. Как видим, после этого действия, вслед за числом, которое выводит формула, находится пояснительная надпись «рублей». Но у этого варианта есть один видимый недостаток: число и текстовое пояснение слились воедино без пробела.
   

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



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



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

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

При написании текста перед формулой придерживаемся следующего синтаксиса. Сразу после знака «=» открываем кавычки и записываем текст. После этого закрываем кавычки. Ставим знак амперсанда. Затем, в случае если нужно внести пробел, открываем кавычки, ставим пробел и закрываем кавычки. Щелкаем по клавише Enter.

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

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

Способ 2: применение функции СЦЕПИТЬ

Также для вставки текста вместе с результатом подсчета формулы можно использовать функцию СЦЕПИТЬ. Данный оператор предназначен для того, чтобы соединять в одной ячейке значения, выводимые в нескольких элементах листа. Он относится к категории текстовых функций. Его синтаксис следующий:

=СЦЕПИТЬ(текст1;текст2;…)

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

Посмотрим, как работает данная функция на практике. Для примера возьмем все ту же таблицу, только добавим в неё ещё один столбец «Общая сумма затрат» с пустой ячейкой.

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



2. Производится активация Мастера функций. Перемещаемся в категорию «Текстовые». Далее выделяем наименование «СЦЕПИТЬ» и жмем на кнопку «OK».



3. Запускается окошко аргументов оператора СЦЕПИТЬ. Данное окно состоит из полей под наименованием «Текст». Их количество достигает 255, но для нашего примера понадобится всего три поля. В первом мы разместим текст, во втором – ссылку на ячейку, в которой содержится формула, и в третьем опять разместим текст.

   Устанавливаем курсор в поле «Текст1». Вписываем туда слово «Итого». Писать текстовые выражения можно без кавычек, так как программа проставит их сама.

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

   В поле «Текст3» вписываем слово «рублей».

   После этого щелкаем по кнопке «OK».



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



5. Для того, чтобы решить данную проблему, снова выделяем ячейку, содержащую оператор СЦЕПИТЬ и переходим в строку формул. Там после каждого аргумента, то есть, после каждой точки с запятой добавляем следующее выражение:

   " ";

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

   =СЦЕПИТЬ("Итого";" ";D2;" ";"рублей")

   Щелкаем по клавише ENTER. Теперь наши значения разделены пробелами.



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



7. После этого, как видим, ненужный нам столбец скрыт, но при этом данные в ячейке, в которой расположена функция СЦЕПИТЬ отображаются корректно.

Читайте также: Функция СЦЕПИТЬ в Экселе
Как скрыть столбцы в Экселе

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