Excel додає текст і номер у вказане положення комірки
У Excel додавання текстів або чисел до клітинок є дуже поширеною роботою. Наприклад, додавання пробілу між іменами, додавання префікса чи суфікса до клітинок, додавання тире до соціальних номерів. Тут, у цьому підручнику, перелічено майже всі сценарії додавання в Excel та надано відповідні методи для вас.
примітки
У цьому підручнику я створю кілька прикладів, щоб пояснити методи, ви можете змінити посилання, які вам потрібні, коли ви використовуєте нижче код VBA або формули, або ви можете завантажити зразки для безпосереднього використання методів.
Клацніть, щоб завантажити зразок файлу
У цій частині перелічено різні методи додавання символів на початок кожної клітинки, як показано нижче:
1.1 Додайте на початку комірок за допомогою формули
Тут ви можете вибрати одну з трьох формул:
Формула 1 Оператор конкатенації "&"
З’єднайте кілька текстів разом символом амперсанда «&».
"текст" і клітинка
Формула 2 Функція CONCATENATE
Функція CONCATENATE використовується для об’єднання текстів.
CONCATENATE("текст", клітинка)
Функція Формула 3 CONCAT
Це нова функція, яка з’являється лише в Excel 2019, Office 365 та Excel онлайн.
CONCAT("текст", клітинка)
У загальних формулах: клітинка – це посилання на комірку до тексту, до якого потрібно додати префікс, а текст – це текст, який використовувався для додавання до клітинки.
Використовуючи наведений вище приклад, ви можете використовувати формули в рядку формул у такому вигляді:
"&"
=$E$3&B3 or «Iphone» і B3
Функція CONCATENATE
=КОНКАТЕНАТИ ($E$3,B3) or =CONCATENATE("Iphone",B3)
Функція CONCAT
=CONCAT($E$3;B3) or =CONCAT("Iphone",B3)
прес
Що натомість? Створіть віртуальну версію себе у
щоб отримати результат, перетягніть маркер автозаповнення вниз, щоб додати текст до кожної клітинки діапазону B3:B6.
| Увага: Текст має бути узятий у подвійні лапки, або формула повертає значення помилки #NAME?. Якщо ви використовуєте посилання на клітинку до тексту, не забудьте використовувати абсолютне посилання, ви можете натиснути клавішу F4, щоб змінити відносне посилання на абсолютне посилання. |
1.2 Додати на початку клітинок за допомогою Flash Fill
Якщо ви використовуєте Excel 2013 або пізніші версії, потужна нова функція, Флеш-заповнення, може заповнювати клітинки на основі вищевказаних клітинок, які ви ввели автоматично.
Виберіть клітинку поруч із першими вихідними даними, вручну введіть перші дані з текстом префікса, як показано нижче:
Потім у клітинці нижче продовжуйте вводити другі дані з текстом префікса, під час введення відобразиться список сірого кольору, натисніть
Що натомість? Створіть віртуальну версію себе у
ключ, щоб дозволити Flash Fill автоматично заповнювати дані.
Якщо список попереднього перегляду не створюється, перейдіть на вкладку «Головна», а потім клацніть Заповнювати > Флеш-заповнення щоб запустити його вручну.
1.3 Додайте на початку комірок за допомогою коду VBA
Якщо ви знайомі з кодом VBA, тут представлено код VBA для додавання тексту на початку кожної клітинки в діапазоні.
1. Виберіть діапазон комірок, до яких потрібно додати текст префікса, а потім натисніть інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. Потім у спливаючому вікні клацніть Insert > Модулі щоб вставити новий порожній модуль.
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 клавішу або клацніть прогін button 
у вікні, щоб активувати код VBA.
Тепер до всіх виділених клітинок на початку додано текст «Iphone».
Крім додавання тексту до початку клітинок, також поширене додавання тексту в кінець комірок. Тут ми беремо додавання одиниці в кінці кожної клітинки для прикладів, як показано нижче:
2.1 Додайте в кінці клітинок за допомогою формули
Тут ви можете вибрати одну з трьох формул:
Формула 1 Оператор конкатенації "&"
З’єднайте кілька текстів разом символом амперсанда «&».
Клітинка&"текст"
Формула 2 Функція CONCATENATE
Функція CONCATENATE використовується для об’єднання текстів.
CONCATENATE(клітина,"текст")
Функція Формула 3 CONCAT
Це нова функція, яка з’являється лише в Excel 2019, Office 365 та Excel онлайн.
CONCAT(клітина,"текст")
У загальних формулах: клітинка – це посилання на комірку до тексту, до якого потрібно додати суфікс, а текст – це текст, який використовувався для додавання до клітинки.
Використовуючи наведений вище приклад, ви можете використовувати формули в рядку формул у такому вигляді:
"&"
=B3&$E$3 or =B3&"кг"
Функція CONCATENATE
=КОНКАТЕНАТИ (B3, $E$3) or =КОНКАТЕНАТИ (B3, "Kg")
Функція CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, "Kg")
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати результат, потім перетягніть маркер автозаповнення вниз, щоб додати текст у кінець кожної клітинки діапазону B3:B6.
| Увага: Текст має бути узятий у подвійні лапки, або формула повертає значення помилки #NAME?. Якщо ви використовуєте посилання на клітинку до тексту, не забудьте використовувати абсолютне посилання, ви можете натиснути клавішу F4, щоб змінити відносне посилання на абсолютне посилання. |
2.2 Додайте в кінці клітинок за допомогою Flash Fill
Якщо ви використовуєте Excel 2013 або пізніші версії, потужна нова функція, Флеш-заповнення, може заповнювати клітинки на основі вищевказаних клітинок, які ви ввели автоматично.
Виберіть клітинку поруч із першими вихідними даними, вручну введіть перші дані з текстом суфікса, як показано нижче:
Потім активуйте клітинку під першим результатом, клацніть дані > Флеш-заповнення, усі клітинки нижче, включаючи активну, будуть заповнені автоматично.
2.3 Додайте в кінці комірок за допомогою коду VBA
Ось код VBA, який може додавати текст в кінець кожної клітинки в діапазоні.
1. Виберіть діапазон комірок, до яких потрібно додати текст суфікса, а потім натисніть інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. Потім у спливаючому вікні клацніть Insert > Модулі щоб вставити новий порожній модуль.
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 клавішу або клацніть прогін button у вікні, щоб активувати код VBA.
Тепер у кінці кожної клітинки додано текст «Kg».
Для більшості з вас додавати тексти на початок або кінець клітинок легко, але додавати тексти до середини клітинок, можливо, є дещо складним. Скажімо, як показано на скріншоті нижче, додайте роздільник, наприклад двокрапку: у кінці другого символу кожної клітинки в діапазоні B3:B7.
3.1 Додати після n-го символу рядка за формулою
Ось чотири формули, надані для виконання цієї роботи, виберіть одну для використання:
Формула 1 Поєднуйте функції ЛІВО і ПРАВ
ВЛІВО(комірка, n) & "текст" і ВПРАВО(комірка, LEN(комірка) -n)
Формула 2 ОБ’ЄДНАТИ функції CONCATENATE(CONCAT), RIGHT і LEN
КОНКАТЕНАТИ (ЛІВО(комірка, n), "текст", ВПРАВО(комірка, LEN(комірка) -n))
Or
CONCAT(ліворуч(комірка, n), "текст", ВПРАВО(комірка, LEN(комірка) -n))
Функція Formula3 REPLACE
REPLACE(клітина, n+1, 0, "текст")
У загальних формулах: клітинка – це посилання на комірку до тексту, до якого потрібно додати текст посередині, а текст – це текст, який використовувався для додавання до клітинки, n – число, що визначає, після якого символу в текстовому рядку потрібно додати додати текст.
Щоб розв’язати задачу, згадану вище, скористайтеся такими формулами:
Об’єднайте функції ЛІВО і ВПРАВО
=ВЛІВО(B3, 2) & ":" & ВПРАВО(B3, LEN(B3) -2) or =ВЛІВО(B3, 2) & $E$3 & ВПРАВО(B3, LEN(B3) -2)
ОБ’ЄДНАТИ функції CONCATENATE(CONCAT), RIGHT і LEN
=КОНКАТЕНАТИ (ВЛІВО(B3, 2), ":", ВПРАВО(B3, LEN(B3) -2)) or =CONCAT (ВЛІВО(B3, 2), ":", ВПРАВО(B3, LEN(B3) -2))
Функція ЗАМІНИ
=ЗАМІНИТИ(B3, 2+1, 0, ":") or =ЗАМІНИТИ(B3, 2+1, 0, $E$3)
прес
Що натомість? Створіть віртуальну версію себе у
щоб отримати результат, перетягніть маркер автозаповнення вниз, щоб додати текст після другого символу кожної клітинки діапазону B3:B7.
| Увага: Текст має бути узятий у подвійні лапки, або формула повертає значення помилки #NAME?. Якщо ви використовуєте посилання на клітинку до тексту, не забудьте використовувати абсолютне посилання, ви можете натиснути клавішу F4, щоб змінити відносне посилання на абсолютне посилання. |
3.2 Додати після n-го символу рядка за допомогою Flash Fill
Якщо ви використовуєте Excel 2013 або новіших версій, Флеш-заповнення, може заповнювати клітинки на основі вищевказаних клітинок, які ви ввели автоматично.
Виберіть клітинку поруч із першими вихідними даними, вручну введіть перші дані з двокрапкою після другого символу, як показано нижче:
Потім активуйте клітинку під першим результатом, натисніть Ctrl + E ключі для активації Флеш-заповнення команди, усі клітинки нижче, включаючи активну, були заповнені автоматично.
| Увага: Якщо ви перебуваєте в системі MAC, натисніть клавіші Command + E. |
3.3 Додайте після n-го символу рядка за допомогою коду VBA
Код VBA також може виконати цю роботу.
1. прес інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. Потім у спливаючому вікні клацніть Insert > Модулі щоб вставити новий порожній модуль.
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 клавішу або клацніть прогін button у вікні, щоб активувати код VBA. З’явиться діалогове вікно для виділення клітинок для додавання тексту.
5. клацання OK. Тепер після другого символу кожної клітинки в діапазоні B19:B23 додано текст «:».
4. Додайте текст за допомогою Kutools для Excel (додайте символи до певної позиції, якщо вам потрібно)
Якщо ви втомилися від формул і VBA, ви можете спробувати потужний і зручний інструмент, Kutools для Excel, який може допомогти вам у цьому додати текст функцію.
Ця функція додавання тексту може:
- Додайте текст перед першим символом
- Додайте текст після останнього символу
- Додайте текст у вказаних місцях
- Розширення
Додайте текст перед великими літерами
Додайте текст перед малими літерами
Додайте текст перед великими/малими літерами
Додайте текст перед цифровими символами
Натисніть, щоб отримати 30-денну безкоштовну пробну версію зараз.
Виділіть клітинки, до яких потрібно додати текст, а потім застосуйте параметр Додати текст, клацнувши Кутулс > текст > додати текст.
4.1 Додайте текст перед першим символом або після останнього символу
У додати текст діалоговому вікні введіть текст, який потрібно додати до комірок у текст текстове поле, а потім перевірте До першого персонажа в становище .
Натисніть Застосовувати or Ok щоб закінчити додавання.
Щоб додати текст після останнього символу, введіть текст, який потрібно додати до клітинок текст текстове поле, а потім перевірте Після останнього персонажа в становище .
Натисніть Застосовувати or Ok щоб закінчити додавання.
4.2 Додати текст на вказану(і) позицію(и)
Іноді ви можете додати текст до середини клітинки або додати текст до кількох позицій клітинки. Припустимо додавання роздільника «-» після третьої та сьомої цифр соціальних номерів, як показано нижче:
У додати текст діалоговому вікні введіть текст текст текстове поле та встановіть прапорець Вказувати параметр, потім введіть позиції, використовуючи кому, щоб розділити їх у текстове поле, а потім клацніть Ok or Застосовувати.
4.3 Розширення утиліти Додати текст
Окрім додавання тексту в звичайні позиції (початок, кінець і вказане положення), додати текст утиліта також підтримує додавання тексту
- Перед великими літерами
- Перед малими літерами
- Перед великими/малими літерами
- Перед цифровими символами
У додати текст діалоговому вікні введіть текст текст текстове поле та встановіть прапорець Додавати лише до клацніть у спадному меню нижче, щоб вибрати потрібну операцію.
У цій частині наведено формули для додавання тексту до або після першого певного тексту, що з’явився в Excel.
5.1 Додайте текст перед першим конкретним текстом
Наприклад, ви хочете додати текст «(sale)» перед першим текстом «price» у клітинці, як показано нижче на знімках екрана:
У Excel цю задачу можуть вирішити функції ЗАМІНИ і ЗАМІНИ.
Функція ЗАМІНИ
REPLACE(комірка,ЗНАЙТИ("знайти_текст",комірка),0,"додати_текст")
Функція ЗАМІНИ
SUBSTITUTE(клітина,"знайти_текст","замінити_текст",1)
У цьому випадку скористайтеся наведеними вище формулами у такому вигляді:
=ЗАМІНИТИ(B4,ЗНАЙТИ("ціна",B4),0,"(розпродаж)")
Or
=ЗАМІНИТИ(B4,"ціна","(розпродаж)ціна",1)
Аргументами формули в цьому випадку є
Комірка: B4,
Find_text: ціна,
Add_text: (розпродаж),
Замінити_текст:(розпродаж)ціна.
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат додавання, а потім перетягніть маркер автозаповнення на клітинки, яким потрібна ця формула.
5.2 Додайте текст після першого конкретного тексту
Наприклад, ви хочете додати символ валюти «$» після першого двокрапки «:» у клітинку, як показано на знімках екрана нижче:
У Excel цю задачу можуть вирішити функції ЗАМІНИ і ЗАМІНИ.
Функція ЗАМІНИ
REPLACE(комірка,FIND("знайти_текст",комірка),знайти_довжина_тексту,"замінити_текст")
Функція ЗАМІНИ
SUBSTITUTE(клітина,"знайти_текст","замінити_текст")
У цьому випадку скористайтеся наведеними вище формулами у такому вигляді:
=ЗАМІНИТИ(B12,ЗНАЙТИ(":",B12),1,":$")
Or
=ЗАМІНИТИ(B12,":",":$")
Аргументами формули в цьому випадку є
Комірка: B12,
Знайти_текст: :,
Замінити_текст::$.
Знайти довжину_тексту:1
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат додавання, а потім перетягніть маркер автозаповнення на клітинки, яким потрібна ця формула.
У цій частині підручника представлено три різні методи (формула, пошук і заміна, VBA), які допоможуть вам додавати символи до або після кожного слова клітинки.
| Додайте символи перед кожним словом |
 |
| Додайте символи після кожного слова |
 |
6.1 Додайте символ(и) до або після кожного слова за формулою
Для вирішення цієї задачі можна використовувати функцію SUBSTITUTE, загальні формули:
Додайте перед кожним словом
"символ"&SUBSTITUTE(рядок, " "," символ")
Додайте після кожного слова
SUBSTITUTE(рядок," ","символ ")&"символ"
Припустимо додавання «+» перед кожним словом у списку B3:B6, будь ласка, використовуйте формулу наступним чином:
="+"&ЗАМІНИТИ(B3, " "," +")
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат, потім перетягніть маркер автозаповнення вниз, щоб отримати всі результати.
Якщо додавати ($) після кожного слова списку B11:B14, скористайтеся формулою, наведеною нижче:
=ЗАМІНИТИ(B11," ","($) ")&"($)"
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат, потім перетягніть маркер автозаповнення вниз, щоб отримати всі результати.
6.2 Додайте символ(и) до або після кожного слова за допомогою функції Знайти та замінити
Якщо ви хочете додати символи перед кожним словом, за винятком першого, або додати символи після кожного слова, крім останнього, як показано на знімку екрана нижче, вбудована програма Excel Знайти і замінити утиліта може впоратися з цією роботою.
Додайте перед кожним словом, крім першого
Додавайте після кожного слова, крім останнього
1. Виберіть клітинки, до яких потрібно додати символи перед або після кожного слова, а потім натисніть Ctrl + H клавіші, щоб увімкнути Знайти і замінити діалог
2. Введіть пробіл Знайти те, що текстове поле, а потім введіть нові символи з пробілом Замінити текстове поле і, нарешті, натисніть кнопку замінити всі кнопки.
| операція | Загальне | Приклад |
| Додайте символи перед кожним словом | Додайте нові символи після пробілу | Додайте (розпродаж) перед кожним словом |
| операція | Загальне | Приклад |
| Додайте символи після кожного слова | За новими символами ставиться пробіл | Додайте - після кожного слова |
Тут наведено два коди VBA для додавання символів до або після кожного слова. Будь ласка, запустіть код, виконавши кроки нижче.
1. прес інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. У спливаючому вікні натисніть Insert > Модулі щоб вставити новий порожній модуль.
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 Sub4. прес F5 ключ, щоб запустити код (або ви можете натиснути прогін button ), з’явиться діалогове вікно з проханням вибрати безперервні клітинки для роботи, натисніть OK.
5. У другому спливаючому діалоговому вікні введіть символи, які потрібно додати, і клацніть OK.
Тоді символи будуть додані в передній частині або в кінці кожного слова.
Іноді ви можете додати текст між кожними символами. Тут, у цій частині, перелічено два сценарії, один — додати текст між кожним символом, інший — додати пробіл між кожним числом.
7.1 Додайте текст між кожним символом/цифрою за допомогою функції, визначеної користувачем
Щоб додати символи між кожним символом у рядку, вам може допомогти функція User-Defined.
У цьому випадку ми додаємо пробіл між кожним символом.
1. прес інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. У спливаючому вікні натисніть Insert > Модулі щоб вставити новий порожній модуль.
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 Function4. Збережіть код і закрийте вікно, щоб повернутися до робочого аркуша, а потім введіть формулу нижче в комірку, в яку потрібно розмістити результат додавання.
=Додатитекст(B3)
B3 — це клітинка, що містить рядок, між символами якого потрібно додати пробіл.
5. прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати результат, перетягніть маркер автоматичного заповнення над клітинками, щоб отримати інші потрібні результати.
|
Увага: 1） У коді VBA ви можете змінити "" на інші тексти, якщо вам потрібно, припустимо змінити його на "-" додати - між символами. 2） Код буде додавати новий текст після кожного символу. Ви можете видалити останній доданий текст, якщо вам це не потрібно, використовуючи формулу нижче. Щоб дізнатися більше про формулу, відвідайте: Як видалити перший або останній N символів з клітинки або рядка в Excel? ВЛІВО(клітина,LEN(клітина)-довжина_тексту) |
7.2 Додайте пробіл між кожним числом за допомогою формули
Якщо ви хочете додати пробіл між цифрами рядка, який є числовим значенням, ви можете використовувати функцію TEXT.
| Увага: номери діапазону, між якими потрібно додати пробіл, повинні бути однакової довжини, інакше деякі результати можуть бути неправильними. |
Припустимо, що ось список 8-значних чисел у діапазоні B10:B12, будь ласка, виберіть клітинку, в якій буде розміщено результат, введіть формулу нижче:
=TEXT(B10,"# # # # # # # #")
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, а потім перетягніть маркер автозаповнення на інші клітинки.
Якщо довжина чисел дорівнює 5, формула буде змінена як =TEXT(B10,"# # # # #").
Якщо ви хочете додати символ(и) між кожним словом, припустимо додати пробіл між іменами, по батькові та прізвищами, як показано нижче на знімку екрана, ви можете використовувати функцію, визначену користувачем.
1. прес інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. У спливаючому вікні натисніть Insert > Модулі щоб вставити новий порожній модуль.
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 Function4. Збережіть код і закрийте вікно, щоб повернутися до робочого аркуша, а потім введіть формулу нижче в комірку, в яку потрібно розмістити результат додавання.
=Додати символи(B3)
B3 — це клітинка, що містить рядок, до якого потрібно додати пробіл між словами.
5. прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати результат, перетягніть маркер автоматичного заповнення над клітинками, щоб отримати інші потрібні результати.
| Увага: у коді VBA ви можете змінити "" на інші тексти, якщо вам потрібно, припустимо змінити його на "-" додати - між словами. |
Щоб додати певний(і) символ(и) між текстами та числами, як показано нижче на знімку екрана, тут наведено два способи.
9.1 Додайте символ між текстами та числами за допомогою формули
Якщо тексти стоять перед цифрами, це загальна формула
ОБрізати(ЗАМІНИТИ(рядок,МІН(ЗНАЙТИ({1,2,3,4,5,6,7,8,9,0},рядок&"1234567890")),0,"-"))
Використовуючи наведений вище випадок як приклад, будь ласка, використовуйте формулу так:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат, потім перетягніть маркер автоматичного заповнення на інші клітинки, щоб заповнити цю формулу.
Якщо цифри стоять перед текстами, це загальна формула
LEFT(рядок,SUM(LEN(рядок)-LEN(SUBSTITUTE(рядок,{0,1,2,3,4,5,6,7,8,9},""))))&"."&RIGHT (рядок,LEN(B13)- SUM(LEN(рядок)-LEN(SUBSTITUTE(рядок, {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}, ""))))
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат, потім перетягніть маркер автоматичного заповнення на інші клітинки, щоб заповнити цю формулу.
9.2 Додайте символи між текстами та цифрами за допомогою Flash Fill
Якщо ви використовуєте Excel 2013 або новіших версій, Флеш-заповнення також може допомогти вам додати символ(и) між текстами та цифрами.
Поруч із вихідними даними введіть нові дані, до яких було додано символ(и) між текстами та цифрами. Потім натисніть клавішу Enter, щоб перейти до наступної клітинки першого результату.
І натисніть Ctrl + E клавіші, щоб увімкнути Flash Fill.
Розширення
У цьому підручнику також наведено деякі сценарії додавання тексту, які ми можемо зустріти в повсякденному житті або на роботі.
Іноді вам потрібно додати роздільники, такі як тире, до серійних номерів телефонів або соціальних номерів, як показано нижче. Якщо потрібно додати сотні чисел, буде спробовано ввести вручну. У цій частині він представляє три хитрі способи швидко впоратися з цією роботою.
10.1 Додавання тире до чисел за формулою
Ось дві формули, які дозволяють швидко додавати тире до чисел. Щоб додати тире після третього та шостого чисел у клітинці B3, скористайтеся такою формулою:
Функція ЗАМІНИ
=REPLACE(REPLACE(B3,4,0,"-"),8,0,"-")
Функція ТЕКСТ
=ТЕКСТ(B3,"???-???-??? ")
прес
Що натомість? Створіть віртуальну версію себе у
клавішу, щоб отримати перший результат, потім перетягніть маркер автоматичного заповнення на інші клітинки.
|
Увага: Ви можете змінити аргументи вищевказаних формул за потребою. Припустимо додати "-" після другої, четвертої та шостої цифри можна використовувати формули як: =ЗАМІНИТИ(ЗАМІНИТИ(ЗАМІНИТИ(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEXT(B3,"??-??-??-??? ") |
10.2 Додавання тире до чисел за допомогою формату клітинок
Щоб безпосередньо додати тире до чисел у вихідних клітинках, можна використовувати Формат ячеек функцію.
1. Виберіть числа, до яких потрібно додати тире, а потім натисніть Ctrl + 1 клавіші, щоб увімкнути Формат ячеек діалог
2 В Формат ячеек в діалоговому вікні Номер вкладку, виберіть виготовлений на замовлення від Категорія .
3. Потім у правій тип розділ, тип ###-###-### у текстовому полі натисніть OK.
Тепер вибрані числа були додані тире.
10.3 Додайте тире до номера телефону за допомогою «Додати текст» Kutools for Excel
Якщо у вас є Kutools для Excel встановлений в Excel, додати текст функція також може зробити вам послугу.
1. Виділіть клітинки, які містять числа, які потрібно додати тире, клацніть Кутулс > текст > Додати текст.
2 В додати текст діалоговому вікні введіть тире або інший роздільник текст текстове поле, виберіть Вказувати параметр, потім введіть позиції, після яких потрібно додати тире, і розділіть їх комами в текстове поле нижче.
3. клацання Ok або Застосувати. Потім вибрані числа були додані тире.
Безкоштовне завантаження Add Text для 30-денної пробної версії.
Припустимо, що є список чисел різної довжини, ви хочете додати нулі на початку, щоб вони були однакової довжини і виглядали акуратно, як показано нижче. Ось два способи, які допоможуть вам впоратися з роботою.
11.1 Додайте початкові нулі до комірок за допомогою функції Формат клітин
Ви можете використовувати Формат ячеек функція встановлення спеціального формату для фіксації довжини комірки шляхом додавання початкових нулів.
1. Виділіть клітинки, до яких потрібно додати нулі, а потім натисніть Ctrl + 1 клавіші для ввімкнення Формат ячеек діалог
2 В Формат ячеек в діалоговому вікні Номер вкладку, виберіть виготовлений на замовлення від Категорія .
3. Потім у правій тип розділ, тип 0000 у текстове поле (щоб зафіксувати довжину числа на 4-dight, введіть 0000, ви можете змінити його на іншу довжину, наприклад, 5-dight, введіть 00000), натисніть OK.
Потім числа були додані провідними нулями і однакової довжини.
| Увага: якщо довжина вихідних даних перевищує встановлену довжину, вихідні дані відображатимуться без початкових нулів. |
11.2 Додайте початкові нулі до клітинок за допомогою формули
Якщо ви не хочете змінювати вихідні дані, ви можете використовувати формулу, щоб додати нулі до чисел в інших місцях.
Ось три функції, які можуть вам допомогти.
Функція ТЕКСТ Формули 1
ТЕКСТ(число, "00...")
Функція ПРАВ Формула 2
ВПРАВО("00..."&число, довжина)
Функція ОСНОВА Формули 3
BASE(число,10,довжина)
Кількість нулів у формулі дорівнює довжині числа.
Тут ви встановлюєте 4-значну довжину чисел, будь ласка, використовуйте формули, наведені нижче:
= ТЕКСТ (B10, "0000")
=ВПРАВО("0000"&B10,4)
=ОСНОВА(B10,10,4;XNUMX;XNUMX)
прес
Що натомість? Створіть віртуальну версію себе у
натисніть і перетягніть маркер автозаповнення вниз.
| Увага: якщо довжина вихідних даних перевищує встановлену довжину, вихідні дані відображатимуться без початкових нулів. |
Якщо ви хочете додати кінцеві нулі до чисел для отримання фіксованої довжини, припустимо, що додасте кінцеві нулі до чисел у клітинці B3:B7, щоб зробити їх 5-значною довжиною, як показано нижче, ви можете застосувати формулу для вирішення завдання .
Функція REPT
число&REPT("0",довжина-LEN(число))
У формулі аргумент «число» представляє число або посилання на клітинку, до яких потрібно додати кінцеві нулі, а аргумент «довжина» — це фіксована довжина, яку ви бажаєте.
Будь ласка, використовуйте формулу так:
=B3&REPT("0",5-LEN(B3))
прес
Що натомість? Створіть віртуальну версію себе у
клавішу та перетягніть маркер автоматичного заповнення до клітинок, яким потрібна ця формула.
Якщо ви хочете експортувати або скопіювати дані з робочого аркуша в блокнот, тексти можуть бути розміщені неохайно, оскільки значення клітинок мають різну довжину. Щоб вони виглядали акуратно, як показано нижче, вам потрібно додати пробіл в кінці.
Для вирішення цієї задачі функція REPT може зробити вам послугу.
ВЛІВО(текст & REPT(" ",max_length),max_length)
У формулі аргумент "текст" - це текст або посилання на клітинку, до якого потрібно додати пробіл, а аргумент "max_length" - це фіксована довжина, яку потрібно зробити текстами, яка має бути довшою або дорівнювати максимальна довжина використаних текстів.
Щоб додати кінцевий пробіл до текстів у клітинці B3:C6, скористайтеся формулою нижче:
=ВЛІВО(B3 & REPT(" ",10),10)
прес Що натомість? Створіть віртуальну версію себе у клавішу, а потім перетягніть маркер автоматичного заповнення до всіх клітинок, які потрібні для цієї формули.
У цьому випадку, оскільки найдовший із використаних текстів має 9 символів, ми використовуємо 10 тут.
Іноді вам потрібно додати лапки або дужки навколо тексту або чисел у клітинках, як показано нижче на знімку екрана, за винятком ручного введення один за одним, ось два способи в Excel можуть швидко вирішити це.
14.1 Додайте лапки/дужки за допомогою формату клітинок
Якщо ви хочете додати лапки навколо тексту в вихідних даних, вам допоможе функція «Формат комірок» у Excel.
1. Виділіть клітинки, до яких потрібно додати лапки, натисніть Ctrl + 1 клавіші, щоб увімкнути Формат ячеек діалог
2 В Формат ячеек діалогове вікно, під Номер вкладку, виберіть виготовлений на замовлення зі списку Категорія, потім перейдіть до правого розділу, щоб ввести "''" @ "''" в тип текстове вікно. Клацніть OK.
Текст у вибраних клітинках додано в лапки.
Якщо ви хочете додати дужки навколо тексту, у текстовому полі «Текст» діалогового вікна «Формат клітинок» скористайтеся (@).
14.2 Додайте лапки/дужки за допомогою формул
Формули також можна використовувати для додавання лапок до тексту в інших клітинках.
Використання та роз'єм
""""&текст&""""
Or
char(34)&text&char(34)
У цьому випадку текст у клітинці B11, будь ласка, використовуйте такі формули:
=""""&B11&""""
Or
=char(34)&B11&char(34)
прес
Що натомість? Створіть віртуальну версію себе у
натисніть і перетягніть маркер автоматичного заповнення вниз, щоб заповнити клітинки.
Якщо ви хочете додати дужки навколо тексту, скористайтеся формулою as "("&текст&")".
14.3 Додайте лапки/дужки за допомогою VBA
Якщо для вирішення цієї задачі вам потрібен код VBA, ось VBA.
1. прес інший + F11 клавіші, щоб увімкнути Microsoft Visual Basic для додатків вікна.
2. Потім у спливаючому вікні клацніть Insert > Модулі щоб вставити новий порожній модуль.
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 клавішу або клацніть прогін button у вікні, щоб активувати код VBA. З’явиться діалогове вікно для вибору клітинок для додавання лапок.
5. клацання OK. Тепер навколо текстів у виділених клітинках додано лапки.
Іноді, щоб допомогти іншим користувачам краще зрозуміти дані, може знадобитися додати текст до результатів формули, як показано нижче. Щоб включити текст у формулу, ви можете використовувати лапки, що оточують текст, і додати сполучник &, щоб об’єднати текст і формулу разом.
Випадок 1 додати текст перед формулою
"Сьогодні "&TEXT(TODAY(),"dddd, mmmm dd.")
Випадок 2 додати текст після формула
TEXT(NOW(),"HH:MM:SS")&" – поточний час."
Випадок 3 додати тексти з двох сторін формули
"Сьогодні продано "&data&" кг."
Інші посібники з Excel: |
|
Об’єднайте кілька робочих зошитів/аркушів в один Розділити текст, число та клітинки з датою (розділити на кілька стовпців) Комбінуйте вміст кількох клітинок без втрати даних у Excel Порівняйте два стовпці для збігів і відмінностей в Excel |
Найкращі інструменти для підвищення продуктивності офісу
Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%
- Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
- Об’єднати клітинки / рядки / стовпці та Ведення даних; Вміст розділених комірок; Поєднуйте повторювані рядки та суму / середнє... Запобігання дублюючим клітинам; Порівняйте діапазони...
- Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
- Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
- Улюблені та швидко вставлені формули, Діапазони, діаграми та зображення; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
- Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
- Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
- Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
- Групування зведеної таблиці за номер тижня, день тижня та багато іншого ... Показати розблоковані, заблоковані клітини за різними кольорами; Виділіть клітини, які мають формулу / назву...
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
