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.
| Внимание: Текстът трябва да бъде ограден в двойни кавички или формулата връща стойност за грешка #ИМЕ?. Ако използвате препратката към клетка към текст, моля, не забравяйте да използвате абсолютната препратка, можете да натиснете клавиша F4, за да промените относителната препратка към абсолютна препратка. |
1.2 Добавете в началото на клетките чрез Flash Fill
Ако сте в Excel 2013 или по-нови версии, мощната нова функция, Запълване със светкавица, може да попълни клетките въз основа на горните клетки, които сте въвели автоматично.
Изберете клетка до първите оригинални данни, въведете ръчно първите данни с текста на префикса, както е показано по-долу:
След това в клетката по-долу продължете да въвеждате вторите данни с текст на префикса, по време на въвеждането ще се покаже списък в сив цвят, натиснете Въведете ключ, за да позволите на Flash Fill да попълни автоматично данните.
Ако списъкът за визуализация не се генерира, моля, отидете в раздела Начало и след това щракнете Напълнете > Запълване със светкавица да го стартирате ръчно.
1.3 Добавете в началото на клетките с помощта на VBA код
Ако сте запознати с VBA кода, тук се въвежда VBA код за добавяне на текст в началото на всяка клетка в диапазон.
1. Изберете диапазон от клетки, към които искате да добавите префиксния текст, след което натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. След това щракнете върху изскачащия прозорец Поставете > Модули за вмъкване на празен нов модул.
3. Копирайте и поставете по-долу VBA кода в новия модул.
VBA: Добавяне в началото на клетките
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Внимание: в кодовия скрипт, iphone е текстът, който искате да добавите в началото на избраните клетки, променете го според вашите нужди. |
4. Натиснете F5 клавиша или щракване бягане бутон 
в прозореца, за да активирате VBA кода.
Сега към всички избрани клетки е добавен текстът "Iphone" в началото.
Освен добавянето на текст в началото на клетките, добавянето на текст в края на клетките също е обичайно. Тук добавяме единица в края на всяка клетка за примери, както е показано на екранната снимка по-долу:
2.1 Добавете в края на клетките с помощта на формула
Тук можете да изберете една от следните три формули:
Формула 1 Конкатениран оператор "&"
Свържете няколко текста заедно с амперсанд "&".
Клетка&"текст"
Формула 2 Функция CONCATENATE
Функцията CONCATENATE се използва за свързване на текстове.
CONCATENATE(клетка;"текст")
Формула3 функция CONCAT
Това е нова функция, която се появява само в Excel 2019, Office 365 и Excel онлайн.
CONCAT(клетка;"текст")
В общите формули: клетката е препратката към клетката към текста, към който искате да добавите суфикс, а текстът е текстът, използван за добавяне към клетката.
Като използвате примера по-горе, можете да използвате формулите в лентата за формули, както следва:
"&"
=B3&$E$3 or =B3&"Kg"
Функция CONCATENATE
=CONCATENATE(B3, $E$3) or =CONCATENATE(B3, "Kg")
Функция CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, "Kg")
Натискане Въведете за да получите резултата, след това плъзнете манипулатора за автоматично попълване надолу, за да добавите текст в края на всяка клетка от диапазон B3:B6.
| Внимание: Текстът трябва да бъде ограден в двойни кавички или формулата връща стойност за грешка #ИМЕ?. Ако използвате препратката към клетка към текст, моля, не забравяйте да използвате абсолютната препратка, можете да натиснете клавиша F4, за да промените относителната препратка към абсолютна препратка. |
2.2 Добавете в края на клетките чрез Flash Fill
Ако сте в Excel 2013 или по-нови версии, мощната нова функция, Запълване със светкавица, може да попълни клетките въз основа на горните клетки, които сте въвели автоматично.
Изберете клетка до първите оригинални данни, въведете ръчно първите данни със суфиксния текст, както е показано по-долу:
След това активирайте клетката под първия резултат, щракнете Дата > Запълване със светкавица, всички клетки по-долу, включително активната клетка, ще бъдат попълнени автоматично.
2.3 Добавете в края на клетките с помощта на VBA код
Ето код на VBA, който може да добави текст в края на всяка клетка в диапазон.
1. Изберете диапазон от клетки, към които искате да добавите суфиксния текст, след което натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. След това щракнете върху изскачащия прозорец Поставете > Модули за вмъкване на празен нов модул.
3. Копирайте и поставете по-долу VBA кода в новия модул.
VBA: Добавяне в края на клетките
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Внимание: в кодовия скрипт, Kg е текстът, който искате да добавите в края на избраните клетки, променете го според вашите нужди. |
4. Натиснете F5 клавиша или щракване бягане бутон в прозореца, за да активирате VBA кода.
Сега текстът "Kg" е добавен в края на всяка клетка.
За повечето от вас добавянето на текстове в началото или края на клетките е лесно, но добавянето на текстове в средата на клетките може би е малко трудно. Вземете например, както е показано на екранната снимка по-долу, добавяйки разделител, като двоеточие: в края на втория знак на всяка клетка в диапазон B3:B7.
3.1 Добавете след n-тия знак от низ по формула
Ето четири формули, предоставени за вас, за да се справите с тази работа, изберете една, която да използвате:
Формула1 Комбинирайте ЛЯВО и ДЯСНО функции
НАЛЯВО(клетка, n) & "текст" & НАДЯСНО(клетка, LEN(клетка) -n)
Формула 2 COMBINE CONCATENATE(CONCAT), функции RIGHT и LEN
CONCATENATE(LEFT(клетка, n), "текст", RIGHT(клетка, LEN(клетка) -n))
Or
CONCAT(НАЛЯВО(клетка, n), "текст", НАДЯСНО(клетка, LEN(клетка) -n))
Формула 3 функция REPLACE
REPLACE(клетка, n+1, 0, "текст")
В общите формули: клетката е препратката към клетката към текста, към който искате да добавите текст в средата, а текстът е текстът, използван за добавяне към клетката, n е числото, определящо след кой знак в текстовия низ искате да добави текст.
За решаване на задачата, спомената по-горе, моля, използвайте следните формули:
Комбинирайте ЛЯВО и ДЯСНО функции
=НАЛЯВО(B3, 2) & ":" & НАДЯСНО(B3, LEN(B3) -2) or =НАЛЯВО(B3, 2) & $E$3 & НАДЯСНО(B3, LEN(B3) -2)
COMBINE CONCATENATE(CONCAT), функции RIGHT и LEN
=СЪЕДИНЯВАНЕ(НАЛЯВО(B3, 2), ":", НАДЯСНО(B3, LEN(B3) -2)) or =CONCAT (НАЛЯВО(B3, 2), ":", НАДЯСНО(B3, LEN(B3) -2))
Функция REPLACE
=ЗАМЕНЯНЕ(B3, 2+1, 0, ":") or =ЗАМЕНЯНЕ(B3, 2+1, 0, $E$3)
Натискане Въведете за да получите резултата, след това плъзнете манипулатора за автоматично попълване надолу, за да добавите текст след втория знак на всяка клетка от диапазон B3:B7.
| Внимание: Текстът трябва да бъде ограден в двойни кавички или формулата връща стойност за грешка #ИМЕ?. Ако използвате препратката към клетка към текст, моля, не забравяйте да използвате абсолютната препратка, можете да натиснете клавиша F4, за да промените относителната препратка към абсолютна препратка. |
3.2 Добавете след n-тия знак от низ чрез Flash Fill
Ако сте в Excel 2013 или по-нови версии, Запълване със светкавица, може да попълни клетките въз основа на горните клетки, които сте въвели автоматично.
Изберете клетка до първите оригинални данни, въведете ръчно първите данни с двоеточие след втория знак, както е показано по-долу:
След това активирайте клетката под първия резултат, натиснете Ctrl + E ключове за активиране Запълване със светкавица всички клетки по-долу, включително активната клетка, са попълнени автоматично.
| Внимание: Ако сте в MAC система, натиснете клавишите Command + E. |
3.3 Добавете след n-тия знак от низ с помощта на VBA код
VBA код може да свърши и тази работа.
1. Натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. След това щракнете върху изскачащия прозорец Поставете > Модули за вмъкване на празен нов модул.
3. Копирайте и поставете по-долу VBA кода в новия модул.
VBA: Добавяне към средата на клетките
Sub AddToMidduleOfString()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, 2 & ":" & VBA.Mid(Rng.Value, 3, VBA.Len(Rng.Value) - 1)
Next
End Sub| Внимание: в кодовия скрипт, 2 е числото, което определя позицията, след която искате да добавите текст, : е текстът, към който искате да добавите, 3 е сумата от 2 и дължината на текста, който добавяте. Вземете друг пример, за да добавите "и" след първия знак на низа "AB", скриптът трябва да бъде Rng.Value = VBA.Left(Rng.Value, 1) & "намлява" & VBA.Mid(Rng.Value, 4, VBA.Len(Rng.Value) - 1) |
4. Натиснете F5 клавиша или щракване бягане бутон в прозореца, за да активирате VBA кода. Изскача диалогов прозорец за избор на клетки за добавяне на текст.
5. кликване OK. Сега текстът ":" е добавен след втория знак на всяка клетка в диапазон B19:B23.
Ако сте уморени от формули и VBA, можете да опитате мощен и удобен инструмент, Kutools за Excel, което може да ви помогне с добавяне на текст функция.
Тази функция за добавяне на текст може:
- Добавете текст преди първия знак
- Добавете текст след последния знак
- Добавете текст на определени позиции
- Разширение
Добавяне на текст преди главни букви
Добавете текст преди малки букви
Добавете текст преди главни/малки букви
Добавете текст преди цифровите знаци
Кликнете, за да получите 30-дневен безплатен пробен период сега.
Изберете клетките, към които искате да добавите текст, след което приложете Добавяне на текст, като щракнете Kutools > Текст > добавяне на текст.
4.1 Добавете текст преди първия знак или след последния знак
в добавяне на текст въведете текста, който искате да добавите към клетките в Текст текстово поле, след което проверете Преди първия знак в Позиция в компанията раздел.
Кликнете Добавете Обява or Ok за да завършите добавянето.
За да добавите текст след последния знак, въведете текста, който искате да добавите към клетките в Текст текстово поле, след което проверете След последния знак в Позиция в компанията раздел.
Кликнете Добавете Обява or Ok за да завършите добавянето.
4.2 Добавете текст на определена позиция(и)
Понякога може да искате да добавите текст в средата на клетката или да добавите текст в няколко позиции на клетката. Да предположим, че добавим разделител "-" след третата и седмата цифра на социалните номера, както е показано на екранната снимка по-долу:
в добавяне на текст диалогов прозорец, въведете текста в Текст текстово поле и проверете Посочете опция, след това въведете позициите, като използвате запетая, за да ги разделите в текстовото поле и след това щракнете Ok or Добавете Обява.
4.3 Разширение на помощната програма за добавяне на текст
Освен добавяне на текст в нормалните позиции (начало, край и определена позиция), добавяне на текст помощната програма също поддържа добавяне на текст
- Пред главни букви
- Пред малки букви
- Пред главни/малки букви
- Преди цифрови знаци
в добавяне на текст диалогов прозорец, въведете текста в Текст текстово поле и проверете Добавете само към опция, след което щракнете върху падащото меню по-долу, за да изберете желаната операция.
Тази част предоставя формули за добавяне на текст преди или след първия появил се конкретен текст в Excel.
5.1 Добавете текст преди първия конкретен текст
Например искате да добавите текст „(продажба)“ преди първия текст „цена“ в клетката, както са показани екранните снимки по-долу:
В Excel функциите REPLACE и SUBSTITUTE могат да решат тази задача.
Функция REPLACE
REPLACE(клетка,НАМИРАНЕ("търсен_текст",клетка),0,"добавяне_текст")
Функция SUBSTITUTE
SUBSTITUTE(клетка,"търсен_текст","заменен_текст",1)
В този случай, моля, използвайте горните формули, както следва:
=ЗАМЕНИТЕ(B4,НАМИРАНЕ("цена",B4),0,"(разпродажба)")
Or
=SUBSTITUTE(B4;"цена";"(продажна)цена";1)
Аргументите на формулата в този случай са
Клетка: B4,
Find_text: цена,
Add_text: (разпродажба),
Replace_text: (продажна) цена.
Натискане Въведете за да получите първия резултат от добавянето, след това плъзнете манипулатора за автоматично попълване върху клетките, които се нуждаят от тази формула.
5.2 Добавете текст след първия конкретен текст
Например искате да добавите валутен символ "$" след първото двоеточие ":" в клетката, както са показани екранните снимки по-долу:
В Excel функциите REPLACE и SUBSTITUTE могат да решат тази задача.
Функция REPLACE
REPLACE(cell,FIND("намерен_текст",клетка),намерен_текст_дължина,"заменен_текст")
Функция SUBSTITUTE
ЗАМЕСТВАНЕ(клетка,"намерен_текст","заменен_текст")
В този случай, моля, използвайте горните формули, както следва:
=ЗАМЕНЯНЕ(B12,НАМИРАНЕ(":",B12),1,":$")
Or
=ЗАМЕСТВАНЕ(B12;":",":$")
Аргументите на формулата в този случай са
Клетка: B12,
Търсене_текст: :,
Заменете_текст::$.
Намиране на_дължина_на_текста:1
Натискане Въведете за да получите първия резултат от добавянето, след това плъзнете манипулатора за автоматично попълване върху клетките, които се нуждаят от тази формула.
В тази част на урока той въвежда три различни метода (формула, намиране и замяна, VBA), за да ви помогне да добавите знаци преди или след всяка дума от клетка.
| Добавете знаци преди всяка дума |
 |
| Добавете знаци след всяка дума |
 |
6.1 Добавете символ(и) преди или след всяка дума по формула
Функцията SUBSTITUTE може да се използва за решаване на тази задача, общите формули като тези:
Добавете преди всяка дума
"символ"&SUBSTITUTE(низ, " "," знак")
Добавете след всяка дума
SUBSTITUTE(низ," ","символ")&"знак"
Да предположим, че добавяме "+" преди всяка дума в списъка на B3:B6, моля, използвайте формулата по следния начин:
="+"&SUBSTITUTE(B3, " "," +")
Натискане Въведете за да получите първия резултат, след това плъзнете манипулатора за автоматично попълване надолу, за да получите всички резултати.
Да предположим, че добавяме ($) след всяка дума от списък B11:B14, моля, използвайте формулата по-долу:
=ЗАМЕСТВАНЕ(B11," ","($) ")&"($)"
Натискане Въведете за да получите първия резултат, след това плъзнете манипулатора за автоматично попълване надолу, за да получите всички резултати.
6.2 Добавяне на символ(и) преди или след всяка дума чрез намиране и замяна
Ако искате да добавите знаци преди всяка дума, с изключение на първата, или да добавите знаци след всяка дума, с изключение на последната, както е показано на екранната снимка по-долу, вграденият Excel Търсене и заместване помощната програма може да се справи с тази работа.
Добавете преди всяка дума, с изключение на първата
Добавете след всяка дума, с изключение на последната
1. Изберете клетките, в които искате да добавите знаци преди или след всяка дума, след което натиснете Ctrl + H клавиши за активиране на Търсене и заместване диалогов прозорец.
2. Въведете интервал в Намерете това, текстово поле, след което въведете новите знаци с интервал Заменете със текстово поле и накрая щракнете върху Замести всички бутон.
| операция | общ | Пример |
| Добавете знаци преди всяка дума | Добавете нови знаци след интервал | Добавяне (продажба) преди всяка дума |
| операция | общ | Пример |
| Добавете знаци след всяка дума | Нови знаци последвани от интервал | Добавяне - след всяка дума |
Тук предоставя два VBA кода за добавяне на знаци преди или след всяка дума, моля, стартирайте кода, като следвате стъпките по-долу.
1. Натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. В изскачащия прозорец щракнете върху Поставете > Модули за да поставите нов празен модул.
3. Копирайте и поставете по-долу VBA кода в модула.
Добавете преди всяка дума
Sub InsertCharBeforeWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = xInStr & Trim(xStr)
Else
xValue = xValue & " " & xInStr & Trim(xStr)
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End SubДобавяне след всяка дума
Sub InsertCharAfterWord()
'UpdateByExtendOffice
Dim ws As Worksheet
Dim xRg As Range
Dim xSRg As Range
Dim xCell As Range
Dim xInStr As String
Dim xArr As Variant
Dim xValue As String
On Error Resume Next
Set xSRg = Application.Selection
Set xRg = Application.InputBox("Select cells(continuous):", "Kutools for Excel", xSRg.Address, , , , , 8)
If xRg Is Nothing Then Exit Sub
xInStr = Application.InputBox("Type characters you want to add:", "Kutools for Excel", "", , , , , 2)
If StrPtr(xInStr) = 0 Then Exit Sub
Application.ScreenUpdating = False
For Each xCell In xRg
xArr = Split(xCell.Text, " ")
xValue = ""
For Each xStr In xArr
If Trim(xStr) <> "" Then
If xValue = "" Then
xValue = Trim(xStr) & xInStr
Else
xValue = xValue & " " & Trim(xStr) & xInStr
End If
End If
Next
xCell.Value = xValue
Next
Application.ScreenUpdating = True
End Sub4. Натиснете F5 клавиш за изпълнение на кода (или можете да щракнете бягане бутон ), изскача диалогов прозорец, за да ви помоли да изберете непрекъснати клетки, за да работите, щракнете OK.
5. Във втория изскачащ диалогов прозорец въведете знаците, които искате да добавите, щракнете OK.
След това знаците ще бъдат добавени в началото или в края на всяка дума.
Понякога може да искате да добавите текст между всеки знак. Тук, в тази част, той изброява два сценария, единият е да добавите текст между всеки знак, а другият е да добавите интервал между всяко число.
7.1 Добавете текст между всеки знак/цифра с дефинирана от потребителя функция
За да добавите знаци между всеки знак в низ, дефинираната от потребителя функция може да ви помогне.
В този случай добавяме интервал между всеки знак.
1. Натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. В изскачащия прозорец щракнете върху Поставете > Модули за да поставите нов празен модул.
3. Копирайте и поставете по-долу VBA кода в модула.
Добавете текст между знаците
Function AddText(Str As String) As String
Dim i As Long
For i = 1 To Len(Str)
AddText = AddText & Mid(Str, i, 1) & " "
Next i
AddSpace = Trim(AddSpace)
End Function4. Запазете кода и затворете прозореца, за да се върнете към работния лист, след което въведете формулата по-долу в клетка, в която искате да поставите резултата от добавянето.
=Добавяне на текст(B3)
B3 е клетката, която съдържа низа, който искате да добавите интервал между знаците.
5. Натиснете Въведете клавиш, за да получите резултата, плъзнете манипулатора за автоматично попълване върху клетките, за да получите други резултати, каквито ви трябват.
|
Внимание: 1) В кода на VBA можете да промените "" към други текстове, колкото ви е необходимо, предполагайки, че го промените на "-" за добавяне - между символи. 2） Кодът ще добави новия текст след всеки знак. Можете да премахнете последния добавен текст, ако не ви е необходим, като използвате формулата по-долу. За повече подробности относно формулата, моля посетете: Как да премахнете първите или последните N знака от клетка или низ в Excел? ЛЯВО(клетка,LEN(клетка)-дължина_на_текста) |
7.2 Добавете интервал между всяко число с формула
Ако искате да добавите интервал между цифрите на низ, който е числова стойност, можете да използвате функцията TEXT.
| Внимание: числата от диапазона, между които искате да добавите интервал, трябва да са с еднаква дължина, в противен случай някои резултати може да са неправилни. |
Да предположим, че тук има списък от 8-цифрени числа в диапазон B10:B12, моля, изберете клетка, която ще постави резултата, въведете формулата по-долу:
=ТЕКСТ(B10,"# # # # # # # # #")
Натискане Въведете клавиш, след което плъзнете манипулатора за автоматично попълване върху други клетки.
Ако дължината на числата е 5, формулата ще се промени като =ТЕКСТ(B10;"# # # # #").
Ако искате да добавите знак(ове) между всяка дума, предполагайки, че добавите интервал между имената, бащите и фамилиите, както е показано на екранната снимка по-долу, можете да използвате функция, дефинирана от потребителя.
1. Натиснете Друг + F11 клавиши за активиране на Microsoft Visual Basic за приложения прозорец.
2. В изскачащия прозорец щракнете върху Поставете > Модули за да поставите нов празен модул.
3. Копирайте и поставете по-долу VBA кода в модула.
Добавете знаци между думите
Function AddCharacters(pValue As String) As String
'UpdatebyExtendoffice20160908
Dim xOut As String
xOut = VBA.Left(pValue, 1)
For i = 2 To VBA.Len(pValue)
xAsc = VBA.Asc(VBA.Mid(pValue, i, 1))
If xAsc >= 65 And xAsc <= 90 Then
xOut = xOut & " " & VBA.Mid(pValue, i, 1)
Else
xOut = xOut & VBA.Mid(pValue, i, 1)
End If
Next
AddCharacters = xOut
End Function4. Запазете кода и затворете прозореца, за да се върнете към работния лист, след което въведете формулата по-долу в клетка, в която искате да поставите резултата от добавянето.
=Добавяне на знаци(B3)
B3 е клетката, която съдържа низа, който искате да добавите интервал между думите.
5. Натиснете Въведете клавиш, за да получите резултата, плъзнете манипулатора за автоматично попълване върху клетките, за да получите други резултати, каквито ви трябват.
| Внимание: в кода на VBA можете да промените "" към други текстове, колкото ви е необходимо, предполагайки, че го промените на "-" за добавяне - между думите. |
За да добавите конкретни знаци между текстове и числа, както е показано на екранната снимка по-долу, тук се предоставят два метода.
9.1 Добавете знак между текстове и числа с формула
Ако текстовете са в предната част на числата, общата формула е
TRIM(REPLACE(низ,MIN(НАМИРАНЕ({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(ЗАМЕСТВАНЕ(низ, {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
=ЗАМЕНЯНЕ(ЗАМЕНЯНЕ(B3,4,0;8,0;XNUMX;"-");XNUMX;XNUMX;"-")
функция TEXT
=ТЕКСТ(B3;"???-???-???")
Натискане Въведете за да получите първия резултат, след това плъзнете манипулатора за автоматично попълване върху други клетки.
|
Внимание: Можете да промените горните аргументи на формулите, както е необходимо. Да предположим добавяне "-" след втората, четвъртата и шестата цифра можете да използвате формулите като: =ЗАМЕНИТЕ(ЗАМЕНИТЕ(ЗАМЕНИТЕ(B3,3,0,"-"),6,0,"-"),9,0,"-") =ТЕКСТ(B3;"??-??-??-???") |
10.2 Добавете тирета към числа чрез форматиране на клетки
За да добавите директно тирета към числата в оригиналните клетки, можете да използвате Форматиране на клетки функция.
1. Изберете числата, към които искате да добавите тирета, след което натиснете Ctrl + 1 клавиши за активиране на Форматиране на клетки диалогов прозорец.
2. В Форматиране на клетки диалогов прозорец под Телефон за връзка: раздела, изберете Custom от категория раздел.
3. След това вдясно Тип раздел, тип ###-###-### в текстовото поле щракнете OK.
Сега към избраните числа са добавени тирета.
10.3 Добавете тирета към телефонния номер чрез Добавяне на текст на Kutools за Excel
Ако имате Kutools за Excel инсталиран в Excel, the добавяне на текст функция също може да ви направи услуга.
1. Изберете клетките, които съдържат числата, необходими за добавяне на тирета, щракнете върху Kutools > Текст > Добави текст.
2. В добавяне на текст въведете тирето или друг разделител Текст текстово поле, изберете Посочете опция, след това въведете позициите, след които искате да добавите тирета, и ги разделете със запетаи в текстовото поле по-долу.
3. кликване Ok или Приложи. След това избраните числа са добавени тирета.
Безплатно изтегляне Add Text за 30-дневен пробен период.
Да предположим, че има списък с числа с различни дължини, искате да добавите водещи нули, за да бъдат с еднаква дължина и да изглеждат спретнато, както е показано на екранната снимка по-долу. Ето два предоставени метода, за да се справите с работата.
11.1 Добавете водещи нули към клетки с функцията за форматиране на клетки
Можете да използвате Форматиране на клетки функция за задаване на персонализиран формат за фиксиране на дължината на клетката чрез добавяне на водещи нули.
1. Изберете клетките, към които искате да добавите водещи нули, след което натиснете Ctrl + 1 ключове за активиране Форматиране на клетки диалогов прозорец.
2. В Форматиране на клетки диалогов прозорец под Телефон за връзка: раздела, изберете Custom от категория раздел.
3. След това вдясно Тип раздел, тип 0000 в текстовото поле (за да фиксирате дължината на числото на 4-dight, въведете 0000, можете да го промените на друга дължина, като например 5-dight, въведете 00000), щракнете върху OK.
След това числата са добавени водещи нули и в същата дължина.
| Внимание: ако дължината на оригиналните данни надвишава зададената дължина, тя ще покаже оригиналните данни без водещи нули. |
11.2 Добавете водещи нули към клетки с формула
Ако не искате да променяте оригиналните данни, можете да използвате формула, за да добавите водещи нули към числа на други места.
Ето три функции, които могат да ви помогнат.
Функция Formula1 TEXT
ТЕКСТ(число;"00...")
Формула2 ДЯСНА функция
ДЯСНО("00…"&число,дължина)
Функция Formula3 BASE
BASE(число,10,дължина)
Броят на нулите във формулата е равен на дължината на числото.
Тук задавате 4 цифри като дължина на числата, моля, използвайте формулите по-долу:
=ТЕКСТ(B10;"0000")
=ДЯСНО("0000"&B10,4)
=БАЗА(B10,10,4;XNUMX;XNUMX)
Натискане Въведете клавиш и плъзнете манипулатора за автоматично попълване надолу.
| Внимание: ако дължината на оригиналните данни надвишава зададената дължина, тя ще покаже оригиналните данни без водещи нули. |
Ако искате да добавите нули в края към числата за създаване на фиксирана дължина, като предположим, че добавите нули в края към числата в клетка B3: B7, за да ги направите в 5-цифрена дължина, както е показано на екранната снимка по-долу, можете да приложите формула, за да се справите със задачата .
Функция REPT
number&REPT("0",length-LEN(число))
Във формулата аргументът "число" представлява числото или препратката към клетка, към която искате да добавите нули в края, а аргументът "дължина" е желаната фиксирана дължина.
Моля, използвайте формулата по следния начин:
=B3&REPT("0",5-LEN(B3))
Натискане Въведете клавиш и плъзнете манипулатора за автоматично попълване надолу до клетки, които се нуждаят от тази формула.
Ако искате да експортирате или копирате данни от работен лист в бележник, текстовете може да са поставени неподредено, тъй като стойностите на клетките са с различна дължина, за да изглеждат добре, както е показано на екранната снимка по-долу, трябва да добавите интервал в края.
За решаването на тази задача функцията REPT може да ви направи услуга.
LEFT(текст & REPT(" ",max_length),max_length)
Във формулата аргументът „текст“ е текстът или препратката към клетката, към която искате да добавите интервал в края, а аргументът „максимална_дължина“ е фиксираната дължина, която искате да направите на текстовете, която трябва да е по-дълга или равна на максимална дължина на използваните текстове.
За да добавите крайно пространство към текстовете в клетка B3:C6, моля, използвайте формулата по-долу:
=НАЛЯВО(B3 & REPT(" ",10),10)
Натискане Въведете клавиш, след това плъзнете манипулатора за автоматично попълване до всички клетки, необходими на тази формула.
В този случай, тъй като най-дългият от използвания текст е от 9 знака, ние използваме 10 тук.
Понякога трябва да добавите кавички или скоби около текста или числата в клетките, както е показано на екранната снимка по-долу, с изключение на ръчното въвеждане един по един, ето два метода в Excel, които могат бързо да разрешат проблема.
14.1 Добавяне на кавички/скоби наоколо чрез Форматиране на клетки
Ако искате да добавите кавички около текста в оригиналните данни, функцията за форматиране на клетки в Excel може да ви помогне.
1. Изберете клетките, които искате да добавите в кавички, натиснете Ctrl + 1 клавиши за активиране на Форматиране на клетки диалогов прозорец.
2. В Форматиране на клетки диалог, под Телефон за връзка: раздела, изберете Custom от списъка на категория, след което отидете в десния раздел, за да въведете "''"@"''" в Тип текстово поле. Кликнете 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. След това щракнете върху изскачащия прозорец Поставете > Модули за вмъкване на празен нов модул.
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 клавиша или щракване бягане бутон в прозореца, за да активирате VBA кода. Изскача диалогов прозорец за избор на клетки за добавяне на кавички.
5. кликване OK. Сега кавичките са добавени около текстове в избраните клетки.
Понякога, за да помогнете на другите потребители да разберат по-добре данните, може да се наложи да добавите текст в резултатите от формулата, както е показано на екранната снимка по-долу. За включване на текст във формула можете да използвате кавички около текста и да добавите конектор &, за да комбинирате текста и формулата заедно.
Случай 1 добавете текст преди формулата
„Днес е „&ТЕКСТ(ДНЕС(),"дддд, мммм дд.")
Случай 2 добавете текст след формула
TEXT(NOW(),"HH:MM:SS")&" е текущият час."
Случай 3 добавете текстове от двете страни на формулата
"Днес продадени "&данни&" кг."
Още уроци за Excel: |
|
Комбинирайте няколко работни книги/работни листове в една Разделяне на клетки с текст, номера и дата (разделяне на няколко колони) Комбинирайте съдържанието на множество клетки, без да губите данни в Excel Сравнете две колони за съвпадения и разлики в Excel |
Най-добрите инструменти за производителност в офиса
Kutools за Excel решава повечето от вашите проблеми и увеличава вашата производителност с 80%
- Супер Формула Бар (лесно редактиране на няколко реда текст и формула); Оформление за четене (лесно четене и редактиране на голям брой клетки); Поставяне във филтриран диапазон...
- Обединяване на клетки/редове/колони и съхраняване на данни; Съдържание на разделени клетки; Комбинирайте дублиращи се редове и сума/средно... Предотвратяване на дублиращи се клетки; Сравнете диапазони...
- Изберете Дублиран или Уникален редове; Изберете Празни редове (всички клетки са празни); Super Find и Fuzzy Find в много работни тетрадки; Произволен избор...
- Точно копие Множество клетки без промяна на референтната формула; Автоматично създаване на препратки към множество листа; Вмъкване на куршуми, квадратчета за отметка и други...
- Любими и бързо вмъкнати формули, диапазони, диаграми и снимки; Шифроване на клетки с парола; Създаване на пощенски списък и изпращайте имейли...
- Извличане на текст, Добавяне на текст, Премахване по позиция, Премахване на пространството; Създаване и отпечатване на междинни суми за пейджинг; Конвертиране на съдържание и коментари между клетки...
- Супер филтър (запазване и прилагане на филтърни схеми към други листове); Разширено сортиране по месец/седмица/ден, честота и други; Специален филтър с удебелен шрифт, курсив...
- Комбинирайте работни тетрадки и работни листове; Обединяване на таблици въз основа на ключови колони; Разделете данните на няколко листа; Пакетно конвертиране на xls, xlsx и PDF...
- Групиране на обобщена таблица по номер на седмицата, ден от седмицата и други... Показване на отключени, заключени клетки с различни цветове; Маркирайте клетки, които имат формула/име...
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
