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

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

Примечание

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

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

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

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

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

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

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

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

Формула 2: функция СЦЕПИТЬ (CONCATENATE)

Функция СЦЕПИТЬ (CONCATENATE) объединяет текстовые фрагменты в единое целое.

CONCATENATE(«text»,cell)

Формула 3: функция ОБЪЕДИНИТЬ (CONCAT)

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

CONCAT(«text»,cell)

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

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

«&»

=$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 Добавление в начало ячеек с помощью функции «Быстрое заполнение»

Если у вас установлена версия Excel 2013 или новее, воспользуйтесь мощной функцией «Быстрое заполнение» — она автоматически заполняет ячейки на основе данных, введённых вами в предыдущих ячейках!

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

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

Если список подсказок не появляется, перейдите на вкладку «Главная» и нажмите «Заполнить» → «Быстрое заполнение», чтобы запустить функцию вручную.

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: оператор объединения «&»

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

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

Формула 2: функция СЦЕПИТЬ (CONCATENATE)

Функция СЦЕПИТЬ (CONCATENATE) объединяет текстовые фрагменты в одну строку.

CONCATENATE(cell,«text»)

Формула 3: функция ОБЪЕДИНИТЬ (CONCAT)

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

CONCAT(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 Добавление в конец ячеек с помощью функции «Быстрое заполнение»

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

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

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

2,3 Добавление в конец ячеек с помощью кода VBA

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

1. Выделите диапазон ячеек, к которым нужно добавить суффикс, и нажмите клавиши Alt+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

4. Нажмите клавишу F5или кнопку Выполнитьв окне, чтобы активировать код VBA.

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

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

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

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

Формула 1: комбинация функций ЛЕВСИМВ и ПРАВСИМВ

LEFT(cell, n) & «текст» & RIGHT(cell, LEN(cell) -n)

Formula2 COMBINE CONCATENATE(CONCAT), функции ПРАВСИМВ и ДЛСТР

CONCATENATE(LEFT(cell, n), «text», RIGHT(cell, LEN(cell) -n))

Или

CONCAT(LEFT(cell, n), «text», RIGHT(cell, LEN(cell) -n))

Формула 3: функция ПОДСТАВИТЬ

REPLACE(cell, n+1, 0, «text»)

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

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

Комбинация функций ЛЕВСИМВ и ПРАВСИМВ

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

COMBINE CONCATENATE(CONCAT), функции ПРАВСИМВ и ДЛСТР

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

Функция ПОДСТАВИТЬ

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

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

3,2 Добавление после n-го символа строки с помощью функции «Быстрое заполнение»

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

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

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

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

Эту задачу можно решить и с помощью кода VBA.

1. Нажмите клавиши Alt+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

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

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

Надоели формулы и VBA? Попробуйте мощный и удобный инструмент Kutools для Excel — его функция Добавить текст легко решит ваши задачи!

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

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

Выделите ячейки, к которым нужно добавить текст, затем примените функцию «Добавить текст», выбрав: Kutools > Текст > Добавить текст.

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

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

Нажмите кнопку Применить или ОК, чтобы завершить добавление.

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

Нажмите кнопку Применитьили ОК, чтобы завершить добавление.

4,2 Добавить текст в указанную позицию(ции)

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

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

Расширение утилиты Добавить текст — 4,3

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

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

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

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

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

5,1 Добавление Добавить текст перед первым конкретным текстом

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

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

Функция ПОДСТАВИТЬ

REPLACE(cell,FIND(«find_text»,cell),0,«add_text»)

Функция SUBSTITUTE

SUBSTITUTE(ячейка;«искомый_текст»;«заменяющий_текст»;1)

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

=REPLACE(B4,FIND(«price»,B4),0,«(sale)»)

Или

=SUBSTITUTE(B4,«price»,«(sale)price»,1)

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

Ячейка: B4,

Искомый_текст: price,

Добавляемый_текст: (sale),

Заменяющий текст: (sale)price.

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

5,2 Добавление Добавить текст после первого конкретного текста

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

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

Функция ПОДСТАВИТЬ

REPLACE(cell,FIND(«find_text»,cell),find_text_length,«replace_text»)

Функция SUBSTITUTE

SUBSTITUTE(ячейка;«искомый_текст»;«заменяющий_текст»)

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

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

Или

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

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

Ячейка: B12,

Искомый_текст: :,

Альтернативный текст: $.

Длина_искомого_текста: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. Введите пробел в поле Find what, затем укажите нужные новые символы с пробелом в поле Replace with и нажмите кнопку Replace All.

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

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

2. В открывшемся окне выберите пункт Insert > Module, чтобы вставить новый пустой модуль.

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. Нажмите комбинацию клавиш 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 — это ячейка, содержащая строку, между символами которой нужно вставить пробелы.

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

7,2 Добавление пробела между каждой цифрой с помощью формулы

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

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

=TEXT(B10,«# # # # # # # #»)

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

Если длина чисел составляет 5, формула примет следующий вид:=TEXT(B10,«# # # # #»).

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

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

2. В открывшемся окне выберите пункт Insert>Module, чтобы вставить новый пустой модуль.

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

Нажмите клавишу 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 Добавление символа между Разделить по тексту и числу с помощью функции «Быстрое заполнение»

Если вы используете Excel 2013 или более позднюю версию, функция «Быстрое заполнение» также поможет вам добавить символ(ы) между текстом и числом.

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

Затем нажмите комбинацию клавиш Ctrl+E, чтобы активировать «Быстрое заполнение».

Дополнительно

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

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

10,1 Добавление дефисов к числам с помощью формулы

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

Функция ПОДСТАВИТЬ

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

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

=TEXT(B3,«???-???-??? »)

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

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

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

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

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

3. Затем в поле Тип справа введите ###-###-###### и нажмите кнопку ОК.

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

10,3 Добавление дефисов к Номер телефона с помощью функции Добавить текст из набора Kutools для Excel

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

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

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

3. Нажмите кнопку ОК или «Применить». После этого к выделенным числам будут добавлены дефисы.

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

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

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

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

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

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

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

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

11,2 Добавление ведущих нулей к Формула

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

В этом помогут три функции.

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

TEXT(number,«00…»)

Формула 2: функция ПРАВСИМВ

RIGHT(«00…»&number,length)

Формула 3: функция ОСНОВАНИЕ

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

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

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

=TEXT(B10,"0000")

=RIGHT("0000"&,B10,4)

=BASE(B10,10,4)

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

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

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

number&REPT("0",length-LEN(number))

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

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

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

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

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

Функция ПОВТОР (REPT) поможет вам решить эту задачу.

LEFT(text & REPT(« »,max_length),max_length)

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

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

=LEFT(B3 &, REPT(« »,10),10)

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

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

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

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

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

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

2. В диалоговом окне Установить формат ячейки перейдите на вкладку Число, выберите в списке категорий (все форматы) элемент Числовые форматы, затем в правой части введите в поле «''»@«''» тип . Нажмите кнопку OK .

К тексту в выделенных ячейках были добавлены кавычки по краям.

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

14,2 Добавление кавычек/скобок с помощью формул

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

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

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

Или

СИМВОЛ(34)&текст&СИМВОЛ(34)

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

=«»""&B11&""""

Или

=char(34)&B11&СИМВОЛ(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 Добавить текст с обеих сторон формулы

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

---

Лучшие инструменты для повышения продуктивности в офисе

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

• Супер строка формул (удобное редактирование многострочного текста и формул); Режим чтения (удобный просмотр и редактирование большого количества ячеек); Вставка в диапазон фильтрации…
• Объединение ячеек, строк или столбцов с сохранением данных; разделение содержимого ячеек;Объединение дублирующихся строк с суммированием или усреднением… предотвращение дублирования записей в ячейках;Сравнение диапазонов…
• Выбор дубликатов или уникальных строк; выбор пустых строк (все ячейки пустые); улучшенный и нечёткий поиск по множеству книг; случайный выбор…
• Точная копия нескольких ячеек без изменения ссылок в формулах; автоматическое создание ссылок на несколько листов; вставка маркеров, флажков и многое другое…
• Избранные и быстрая вставка формул, диапазонов, диаграмм и изображений; Защита ячеек паролем; Создание списка рассылкии отправка электронных писем…
• Извлечение текста, добавление текста, удаление символов в определённой позиции, удаление пробелов; создание и печать статистики по странице данных; преобразование между содержимым ячеек и примечаниями…
• Суперфильтр (сохранение и применение схем фильтрации к другим листам); расширенная сортировка по месяцам, неделям, дням, частоте и другим параметрам; специальный фильтр по полужирному, курсиву…
• Объединение книг и листов; объединение таблиц на основе ключевого столбца; разделение данных на несколько листов; пакетное преобразование XLS, XLSX и PDF…
• Сводная таблица: группировка по номеру недели, дню недели и многому другому… Показ разблокированных и заблокированных ячеек разными цветами; Выделение ячеек, содержащих формулы или имена…

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить… 

---

Office Tab — добавляет вкладки в Office и значительно упрощает вашу работу

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

Подробнее…Полный функционал, бесплатная пробная версия на 30 дней…Купить…