Каковы характеристики ячейки в Excel

Обновлено: 20.11.2024

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

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

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

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

Оператор конкатенации

Самый простой способ добавить текстовую строку в ячейку — использовать символ амперсанда (&), который в Excel является оператором конкатенации.

Это работает во всех версиях Excel 2007–Excel 365.

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

Того же результата можно добиться с помощью функции СЦЕПИТЬ:

Эта функция доступна в Excel для Microsoft 365, Excel 2019–2007.

Функция СЦЕП

Чтобы добавить текст в ячейки в Excel 365, Excel 2019 и Excel Online, вы можете использовать функцию СЦЕП, которая является современной заменой СЦЕПИТЬ:

Это общие подходы, и приведенные ниже примеры показывают, как применять их на практике.

Как добавить текст в начало ячеек

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

В ячейке, в которую вы хотите вывести результат, введите знак равенства (=).
Введите нужный текст в кавычках.
Введите символ амперсанда (&).
Выберите ячейку, в которую нужно добавить текст, и нажмите Enter .

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

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

Во всех версиях Excel:

В Excel 365 и Excel 2019:

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

Совет. Приведенные выше формулы соединяют две строки без пробелов. Чтобы разделить значения пробелом, введите пробел в конце предваряемого текста (например, "Проект: ").

Для удобства вы можете ввести целевой текст в предопределенную ячейку (E2) и добавить две текстовые ячейки вместе:

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

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

Как добавить текст в конец ячеек в Excel

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

Например, чтобы добавить строку "-US" в конец ячейки A2, используйте следующие формулы:

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

Не забудьте использовать абсолютную ссылку для добавленного текста ($D$2), чтобы формула правильно копировалась по всему столбцу.

Добавить символы в начало и конец строки

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

Для примера добавим строку "Project:" в начало и "-US" в конец существующего текста в A2.

=CONCATENATE("Проект:", A2, "-US")

=CONCAT("Проект:", A2, "-US")

Если строки вводятся в отдельные ячейки, это работает одинаково хорошо:

Объединить текст из двух или более ячеек

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

Например, чтобы объединить значения из столбцов A и B, используя запятую и пробел (",") в качестве разделителя, введите одну из приведенных ниже формул в ячейке B2, а затем перетащите ее вниз по столбцу.

Добавить текст из двух ячеек с помощью амперсанда:

Объедините текст из двух ячеек с помощью CONCAT или CONCATENATE:

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

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

Например, чтобы добавить строки из трех столбцов (A, B и C), разделив значения запятой и пробелом, используйте следующую формулу:

=TEXTJOIN(", ", ИСТИНА, A2, B2, C2)

Как добавить специальный символ в ячейку в Excel

Чтобы вставить специальный символ в ячейку Excel, необходимо знать его код в системе ASCII. Как только код установлен, передайте его функции CHAR, чтобы вернуть соответствующий символ. Функция CHAR принимает любое число от 1 до 255. Список печатных кодов символов (значения от 32 до 255) можно найти здесь.

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

Например, чтобы добавить символ товарного знака (™) в текст формата A2, подойдет любая из следующих формул:

Как добавить текст в формулу в Excel

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

Допустим, вы используете эту формулу для возврата текущего времени:

=ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")

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

Вставить текст перед формулой:

="Текущее время: "&ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")

=СЦЕПИТЬ("Текущее время: ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"))

=CONCAT("Текущее время: ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"))

Добавить текст после формулы:

=TEXT(NOW(), "h:mm AM/PM")&" - текущее время"

=СЦЕПИТЬ(ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " - текущее время")

=CONCAT(ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " - текущее время")

Добавить текст в формулу с обеих сторон:

="Это " &ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM")& "здесь, в Гомеле"

=СЦЕПИТЬ("Это ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " здесь, в Гомеле")

=CONCAT("Это ", ТЕКСТ(СЕЙЧАС(), "ч:мм AM/PM"), " здесь, в Гомеле")

Как вставить текст после N-го символа

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

Извлеките подстроку, предшествующую вставленному тексту, с помощью функции LEFT:
LEFT(cell, n)
Извлеките подстроку после текста, используя комбинацию RIGHT и LEN:
RIGHT(cell, LEN(cell) -n)
Объедините две подстроки и текст/символ, используя символ амперсанда.

Полная формула имеет следующий вид:

Одни и те же части можно соединить вместе с помощью функции СЦЕПИТЬ или СЦЕПИТЬ:

Эту задачу также можно выполнить с помощью функции ЗАМЕНИТЬ:

Хитрость заключается в том, что аргумент num_chars, определяющий количество заменяемых символов, имеет значение 0, поэтому формула фактически вставляет текст в указанную позицию в ячейке без замена чего либо. Позиция (аргумент start_num) вычисляется с использованием следующего выражения: n+1.Мы добавляем 1 к позиции n-го символа, потому что текст должен быть вставлен после него.

Например, чтобы вставить дефис (-) после второго символа в A2, формула в B2 будет следующей:

=ВЛЕВО(A2, 2) &"-"& ВПРАВО(A2, ДЛСТР(A2) -2)

=СЦЕПИТЬ(ЛЕВО(A2, 2), "-", ПРАВО(A2, ДЛСТР(A2) -2))

Перетащите формулу вниз, и во все ячейки будет вставлен один и тот же символ:

Как добавить текст до/после определенного символа

Чтобы вставить определенный текст до или после определенного символа, необходимо определить положение этого символа в строке. Это можно сделать с помощью функции ПОИСК:

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

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

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

Например, чтобы вставить текст (US) после дефиса в A2, используйте следующую формулу:

=ВЛЕВО(A2, ПОИСК("-", A2)) &"(США)"& ВПРАВО(A2, ДЛИН(A2) - ПОИСК("-", A2))

=СЦЕПИТЬ(ЛЕВАЯ(A2, ПОИСК("-", A2)), "(США)", ПРАВАЯ(A2, ДЛСТР(A2) -ПОИСК("-", A2)))

Вставить текст перед определенным символом

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

Как видите, формулы очень похожи на те, которые вставляют текст после символа. Разница в том, что мы вычитаем 1 из результата первого ПОИСКА, чтобы заставить функцию ВЛЕВО пропустить символ, после которого добавляется текст. К результату второго ПОИСКА мы добавляем 1, чтобы функция ПРАВИЛЬНО нашла этот символ.

Например, чтобы поместить текст (US) перед дефисом в A2, используйте следующую формулу:

=ВЛЕВО(A2, ПОИСК("-", A2) -1) &"(США)"& ВПРАВО(A2, ДЛСТР(A2) -ПОИСК("-", A2) +1)

=СЦЕПИТЬ(ЛЕВАЯ(A2, ПОИСК("-", A2) -1), "(США)", ПРАВАЯ(A2, ДЛСТР(A2) -ПОИСК("-", A2) +1))< /p>

Если исходная ячейка содержит несколько вхождений символа, текст будет вставлен до/после первого вхождения.
Функция ПОИСК нечувствительна к регистру и не может различать строчные и прописные буквы. Если вы хотите добавить текст до или после строчной или прописной буквы, используйте функцию НАЙТИ с учетом регистра, чтобы найти эту букву.

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

На самом деле это всего лишь частный случай двух предыдущих примеров.

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

Например, чтобы вставить пробел после 10-го символа в ячейках A2:A7, введите приведенную ниже формулу в ячейке B2 и перетащите ее через ячейку B7:

=ВЛЕВО(A2, 10) &" "& ВПРАВО(A2, ДЛСТР(A2) -10)

=СЦЕПИТЬ(ЛЕВО(A2, 10), " ", ПРАВО(A2, ДЛСТР(A2) -10))

Во всех исходных ячейках 10-й символ — двоеточие (:), поэтому пробел вставляется именно там, где он нужен:

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

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

=ВЛЕВО(A2, ПОИСК(":", A2)) &" "& ВПРАВО(A2, ДЛСТР(A2)-ПОИСК(":", A2))

=СЦЕПИТЬ(СЛЕВА(A2, ПОИСК(":", A2)), " ", СПРАВА(A2, ДЛСТР(A2)-ПОИСК(":", A2)))

Как добавить тот же текст в существующие ячейки с помощью VBA

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

Добавить текст в начало

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

Если у вас мало опыта работы с VBA, это пошаговое руководство проведет вас через весь процесс: Как вставить и запустить код VBA в Excel.

Макрос 1: добавляет текст в исходные ячейки

Этот код вставляет подстроку "PR-" слева от существующего текста. Прежде чем использовать код на своем рабочем листе, обязательно замените наш образец текста тем, который вам действительно нужен.

Макрос 2: помещает результаты в соседний столбец

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

Добавить текст в конец

Если вы хотите добавить определенную строку/символ в конец всех выделенных ячеек, эти коды помогут вам быстро выполнить работу.

Макрос 1: добавляет текст в исходные ячейки

В нашем примере кода подстрока "-PR" вставляется справа от существующего текста. Естественно, вы можете изменить его на любой текст/символ, который вам нужен.

Макрос 2: помещает результаты в другой столбец

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

Добавьте текст или символ в несколько ячеек с помощью Ultimate Suite

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

Установив Ultimate Suite в Excel, выполните следующие действия:

Выберите исходные данные.
На вкладке Ablebits в группе Текст нажмите "Добавить".
На панели Добавить текст введите символ/текст, который вы хотите добавить в выбранные ячейки, и укажите, куда его следует вставить:
- В начале
- В конце
- Перед определенным текстом/символом
- После определенного текста/символа
- После N-го символа с начала или с конца
Нажмите кнопку Добавить текст. Готово!

В качестве примера давайте вставим строку "PR-" после символа "-" в ячейки A2:A7. Для этого настраиваем следующие параметры:

Мгновение спустя мы получаем желаемый результат:

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel Starter 2010 Еще. Меньше

Функция ЯЧЕЙКА возвращает информацию о форматировании, расположении или содержимом ячейки. Например, если вы хотите убедиться, что ячейка содержит числовое значение вместо текста, прежде чем выполнять над ней вычисления, вы можете использовать следующую формулу:

Эта формула вычисляет A1*2, только если ячейка A1 содержит числовое значение, и возвращает 0, если ячейка A1 содержит текст или пуста.

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

Синтаксис

CELL(info_type, [ссылка])

Синтаксис функции CELL имеет следующие аргументы:

Описание

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

Ячейка, о которой вы хотите получить информацию.

Если он опущен, информация, указанная в аргументе info_type, возвращается для ячейки, выбранной во время вычисления. Если ссылочным аргументом является диапазон ячеек, функция ЯЧЕЙКА возвращает информацию об активной ячейке в выбранном диапазоне.

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

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

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

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

значения info_type

В следующем списке описаны текстовые значения, которые можно использовать для аргумента info_type. Эти значения должны быть введены в функцию ЯЧЕЙКА с кавычками (" ").

Ссылка на первую ячейку в ссылке в виде текста.

Номер столбца ячейки в ссылке.

Значение 1, если ячейка отформатирована в цвете для отрицательных значений; в противном случае возвращает 0 (ноль).