Excel: добавление текста и числа в указанную позицию ячейки

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

Примечание

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

Нажмите, чтобы скачать пример файла

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

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

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

Формула1: Оператор объединения «&»

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

"text"&cell

Формула2: функция CONCATENATE

Функция CONCATENATE используется для объединения текстов.

CONCATENATE("text",cell)

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

Это новая функция, которая появилась только в Excel2019, Office365 и Excel online.

CONCAT("text",cell)

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

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

"&"

=$E$3&B3 или ="Iphone"&B3

Функция CONCATENATE

=CONCATENATE($E$3,B3) или =CONCATENATE("Iphone",B3)

Функция CONCAT

=CONCAT($E$3,B3) или =CONCAT("Iphone",B3)

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

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

Если вы используете Excel2013 или более поздние версии, мощная функция автозаполнения (Flash Fill) может автоматически заполнить ячейки на основе введённых выше данных.

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

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

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

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

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

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

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

4. Нажмите F5 или нажмите Выполнить кнопку в окне для запуска VBA-кода.

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

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

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

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

Формула1: Оператор объединения «&»

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

cell&"text"

Формула2: функция CONCATENATE

Функция CONCATENATE используется для объединения текстов.

CONCATENATE(cell,"text")

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

Это новая функция, которая появилась только в Excel2019, Office365 и Excel online.

CONCAT(cell,"text")

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

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

"&"

=B3&$E$3 или =B3&"Kg"

Функция CONCATENATE

=CONCATENATE(B3, $E$3) или =CONCATENATE(B3, "Kg")

Функция CONCAT

=CONCAT(B3, $E$3) или =CONCAT(B3, "Kg")

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

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

Если вы используете Excel2013 или более поздние версии, мощная функция автозаполнения (Flash Fill) может автоматически заполнить ячейки на основе введённых выше данных.

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

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

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

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

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

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

4. Нажмите F5 или нажмите Выполнить кнопку в окне для запуска VBA-кода.

Теперь текст "Kg" добавлен в конец каждой ячейки.

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

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

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

Формула1: объединить функции LEFT и RIGHT

LEFT(cell, n) & "text" & RIGHT(cell, LEN(cell) -n)

Формула2: объединить CONCATENATE(CONCAT), RIGHT и LEN

CONCATENATE(LEFT(cell, n), "text", RIGHT(cell, LEN(cell) -n))

Или

CONCAT(LEFT(cell, n), "text", RIGHT(cell, LEN(cell) -n))

Формула3: функция REPLACE

REPLACE(cell, n+1,0, "text")

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

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

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

=LEFT(B3,2) & ":" & RIGHT(B3, LEN(B3) -2) или =LEFT(B3,2) & $E$3 & RIGHT(B3, LEN(B3) -2)

Объединить CONCATENATE(CONCAT), RIGHT и LEN

=CONCATENATE(LEFT(B3,2), ":", RIGHT(B3, LEN(B3) -2)) или =CONCAT (LEFT(B3,2), ":", RIGHT(B3, LEN(B3) -2))

Функция REPLACE

=REPLACE(B3,2+1,0, ":") или =REPLACE(B3,2+1,0, $E$3)

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

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

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

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

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

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

Для этой задачи также можно использовать VBA-код.

1. Нажмите Alt + F11 , чтобы открыть окно Microsoft Visual Basic for Applications.

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

4. Нажмите F5 или нажмите Выполнить кнопку в окне для запуска VBA-кода. Появится диалоговое окно для выбора ячеек, в которые нужно добавить текст.

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

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

Функция Добавить текст позволяет:

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

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

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

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

Нажмите Применить или Ок для завершения добавления.

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

Нажмите Применить или Ок для завершения добавления.

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

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

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

4.3 Расширение функции Добавить текст

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

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

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

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

В этом разделе приведены формулы для добавления текста до или после первого вхождения определённого текста в Excel.

5.1 Добавить текст перед первым вхождением определённого текста

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

В Excel функции REPLACE и SUBSTITUTE помогут решить эту задачу.

Функция REPLACE

REPLACE(cell,FIND("find_text",cell),0,"add_text")

Функция SUBSTITUTE

SUBSTITUTE(cell,"find_text","replace_text",1)

В этом случае используйте приведённые выше формулы так:

=REPLACE(B4,FIND("price",B4),0,"(sale)")

Или

=SUBSTITUTE(B4,"price","(sale)price",1)

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

Ячейка: B4,

Find_text: price,

Add_text: (sale),

Replace_text:(sale)price.

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

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

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

В Excel функции REPLACE и SUBSTITUTE помогут решить эту задачу.

Функция REPLACE

REPLACE(cell,FIND("find_text",cell),find_text_length,"replace_text")

Функция SUBSTITUTE

SUBSTITUTE(cell,"find_text","replace_text")

В этом случае используйте приведённые выше формулы так:

=REPLACE(B12,FIND(":",B12),1,":$")

Или

=SUBSTITUTE(B12,":",":$")

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

Ячейка: B12,

Find_text: :,

Replace_text::$.

Find_text_length:1

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

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

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

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

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

Функция SUBSTITUTE поможет решить эту задачу. Общие формулы такие:

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

"символ"&SUBSTITUTE(строка, " "," символ")

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

SUBSTITUTE(строка," ","символ ")&"символ"

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

="+"&SUBSTITUTE(B3, " "," +")

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

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

=SUBSTITUTE(B11," ","($) ")&"($)"

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

6.2 Добавить символ(ы) до или после каждого слова с помощью Поиск и Замена

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

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

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

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

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

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

1. Нажмите Alt + F11 , чтобы открыть окно Microsoft Visual Basic for Applications.

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 для запуска кода (или нажмите Выполнить кнопку ), появится диалоговое окно с просьбой выбрать непрерывные ячейки для работы, нажмите OK.

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

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

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

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

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

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

1. Нажмите Alt + F11 , чтобы открыть окно Microsoft Visual Basic for Applications.

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. Сохраните код и закройте окно, чтобы вернуться к листу, затем введите формулу в нужную ячейку для вывода результата.

=AddText(B3)

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

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

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

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

Допустим, у вас есть список8-значных чисел в диапазоне B10:B12. Выберите ячейку для вывода результата и введите формулу:

=TEXT(B10,"# # # # # # # #")

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

Если длина числа равна5, формула будет =TEXT(B10,"# # # # #")

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

1. Нажмите Alt + F11 , чтобы открыть окно Microsoft Visual Basic for Applications.

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. Сохраните код и закройте окно, чтобы вернуться к листу, затем введите формулу в нужную ячейку для вывода результата.

=AddCharacters(B3)

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

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

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

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 клавишу, чтобы получить первый результат, затем перетащите маркер автозаполнения по другим ячейкам.

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

LEFT(string,SUM(LEN(string)-LEN(SUBSTITUTE(string,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT(string,LEN(B13)- SUM(LEN(string)-LEN(SUBSTITUTE(string, {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)

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

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

И нажмите Ctrl + E для активации Flash Fill.

Расширение

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

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

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

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

Функция REPLACE

=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")

Функция TEXT

=TEXT(B3,"???-???-??? ")

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

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

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

1. Выделите числа, к которым нужно добавить дефисы, затем нажмите Ctrl +1 для открытия окна Установить формат ячейки.

2. В окне Установить формат ячейки на вкладке Число выберите Пользовательский в разделе Категория.

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

Теперь выбранные числа отображаются с дефисами.

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

Если у вас установлен Kutools для Excel, функция Добавить текст также поможет.

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

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

3. Нажмите Ок или Применить. Теперь выбранные числа отображаются с дефисами.

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

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

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

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

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

2. В окне Установить формат ячейки на вкладке Число выберите Пользовательский в разделе Категория.

3. Затем справа Тип раздел, введите 0000 в поле (чтобы задать длину числа4 символа, введите0000; для5 символов —00000), нажмите OK.

Теперь числа отображаются с ведущими нулями и одинаковой длиной.

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

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

Вот три функции, которые помогут:

Формула1: функция TEXT

TEXT(число,"00…")

Формула2: функция RIGHT

RIGHT("00…"&число,длина)

Формула3: функция BASE

BASE(число,10,длина)

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

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

=TEXT(B10,"0000")

=RIGHT("0000"&B10,4)

=BASE(B10,10,4)

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

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

Функция REPT

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

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

Используйте формулу:

=B3&REPT("0",5-LEN(B3))

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

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

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

LEFT(текст & REPT(" ",макс_длина),макс_длина)

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

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

=LEFT(B3 & REPT(" ",10),10)

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

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

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

14.1 Добавить кавычки/скобки с помощью Установить формат ячейки

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

1. Выделите ячейки, к которым нужно добавить кавычки, нажмите Ctrl +1 для открытия окна Установить формат ячейки.

2. В Установить формат ячейки диалоге, на вкладке Число выберите Пользовательский из списка Категория, затем справа введите "''"@"''" в Тип поле. Нажмите OK.

Текст в выбранных ячейках теперь отображается в кавычках.

Если нужно добавить скобки вокруг текста, в поле Тип окна Установить формат ячейки используйте (@).

14.2 Добавить кавычки/скобки с помощью формул

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

Использование оператора &

""""&текст&""""

Или

char(34)&текст&char(34)

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

=""""&B11&""""

Или

=char(34)&B11&char(34)

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

Если нужно добавить скобки вокруг текста, используйте формулу "("&текст&")".

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

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

1. Нажмите Alt + F11 , чтобы открыть окно Microsoft Visual Basic for Applications .

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

4. Нажмите F5 или нажмите Выполнить кнопку в окне для запуска VBA-кода. Появится диалоговое окно для выбора ячеек, в которые нужно добавить кавычки.

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

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

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

"Сегодня "&TEXT(TODAY(),"dddd, mmmm dd.")

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

TEXT(NOW(),"HH:MM:SS")&" — текущее время."

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

"Сегодня продано "&данные&" кг."

---

Лучшие инструменты для повышения производительности Office

Kutools для Excel решает большинство ваших проблем и увеличивает вашу продуктивность на 80%

• Супер строка формул (легкое редактирование нескольких строк текста и формул); Режим чтения (удобное чтение и редактирование большого количества ячеек); Вставка в отфильтрованный диапазон...
• Объединение ячеек/строк/столбцов с сохранением данных; Разделение содержимого ячеек; Объединение дублирующихся строк с подсчетом суммы/среднего значения... Предотвращение дублирования ячеек; Сравнение диапазонов...
• Выбор дублирующихся или уникальных строк; Выбор пустых строк (все ячейки пустые); Супер поиск и нечеткий поиск во многих книгах; Случайный выбор...
• Точное копирование нескольких ячеек без изменения ссылок на формулы; Автоматическое создание ссылок на несколько листов; Вставка маркеров, флажков и многое другое...
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Шифрование ячеек с паролем; Создание списка рассылки и отправка электронных писем...
• Извлечение текста, добавление текста, удаление по позиции, удаление пробелов; Создание и печать статистики страниц; Преобразование между содержимым ячеек и комментариями...
• Супер фильтр (сохранение и применение схем фильтрации к другим листам); Расширенная сортировка по месяцу/неделе/дню, частоте и другим параметрам; Специальный фильтр по жирному шрифту, курсиву...
• Объединение книг и листов; Объединение таблиц на основе ключевых столбцов; Разделение данных на несколько листов; Пакетное преобразование xls, xlsx и PDF...
• Группировка сводной таблицы по номеру недели, дню недели и другим параметрам... Отображение разблокированных, заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы/имена...

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести... 

---

Office Tab - добавляет интерфейс с вкладками в Office и значительно упрощает вашу работу

• Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
• Открывайте и создавайте несколько документов в новых вкладках одного окна, а не в новых окнах.
• Увеличивает вашу продуктивность на 50% и экономит сотни кликов мышью каждый день!

Читать далее... Полные функции 30-дневная бесплатная пробная версия... Приобрести...