Excel dodaje tekst i liczbę do określonej pozycji komórki
W programie Excel dodawanie tekstów lub liczb do komórek jest bardzo powszechną pracą. Takie jak dodawanie spacji między nazwami, dodawanie prefiksu lub sufiksu do komórek, dodawanie myślników do numerów społecznościowych. W tym samouczku zawiera listę prawie wszystkich scenariuszy dodawania w programie Excel i zapewnia odpowiednie metody.
Note
W tym samouczku tworzę kilka przykładów, aby wyjaśnić metody, możesz zmienić referencje według potrzeb, używając poniższego kodu lub formuł VBA, lub możesz pobrać próbki do bezpośredniego wypróbowania metod.
Kliknij, aby pobrać przykładowy plik
W tej części wymieniono różne metody dodawania znaków na początku każdej komórki, jak pokazano na poniższym zrzucie ekranu:
1.1 Dodaj na początku komórek za pomocą formuły
Tutaj możesz wybrać jedną z trzech poniższych formuł:
Formuła 1 Konkatenacja operatora „&”
Połącz wiele tekstów za pomocą znaku ampersand „&”.
&komórka "tekst"
Funkcja CONCATENATE Formuły 2
Funkcja CONCATENATE służy do łączenia ze sobą tekstów.
ZŁĄCZ.TEKSTY("tekst";komórka)
Formuła 3 funkcja CONCAT
Jest to nowa funkcja, która pojawia się tylko w Excel 2019, Office 365 i Excel online.
ZŁĄCZ("tekst";komórka)
W formułach ogólnych: komórka to odwołanie do komórki do tekstu, do którego chcesz dodać prefiks, a tekst to tekst użyty do dodania do komórki.
Korzystając z powyższego przykładu, możesz użyć formuł na pasku formuły w następujący sposób:
„&”
=$3$&B3 or ="IPhone"&B3
ZŁĄCZ, funkcja
=ZŁĄCZ.TEKSTY($E$3;B3) or =ZŁĄCZ.TEKSTY("Iphone";B3)
Funkcja CONCAT
=CONCAT($E$3;B3) or =CONCAT("Iphone";B3)
Naciśnij przycisk Wchodzę aby uzyskać wynik, a następnie przeciągnij uchwyt autouzupełniania w dół, aby dodać tekst do każdej komórki z zakresu B3:B6.
| Uwaga: Tekst powinien być ujęty w cudzysłów lub formuła zwraca wartość błędu #NAZWA?. Jeśli używasz odwołania do komórki do tekstu, pamiętaj, aby użyć odniesienia bezwzględnego, możesz nacisnąć klawisz F4, aby zmienić odniesienie względne na odwołanie bezwzględne. |
1.2 Dodaj na początku komórek przez Flash Fill
Jeśli korzystasz z programu Excel 2013 lub nowszego, nowa, zaawansowana funkcja, Wypełnienie błyskiem, może wypełnić komórki na podstawie powyższych komórek wprowadzonych automatycznie.
Wybierz komórkę obok pierwszych oryginalnych danych, ręcznie wpisz pierwsze dane z tekstem prefiksu, jak pokazano poniżej:
Następnie w komórce poniżej kontynuuj wpisywanie drugich danych z tekstem prefiksu, podczas wpisywania zostanie wyświetlona lista w kolorze szarym, naciśnij Wchodzę aby funkcja Flash Fill automatycznie wypełniła dane.
Jeśli lista podglądu nie zostanie wygenerowana, przejdź do zakładki Strona główna, a następnie kliknij Wypełniać > Wypełnienie błyskiem uruchomić go ręcznie.
1.3 Dodaj na początku komórek za pomocą kodu VBA
Jeśli znasz kod VBA, tutaj przedstawiamy kod VBA, aby dodać tekst na początku każdej komórki w zakresie.
1. Wybierz zakres komórek, do których chcesz dodać tekst prefiksu, a następnie naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. Następnie w wyskakującym okienku kliknij wstawka > Moduł aby wstawić pusty nowy moduł.
3. Skopiuj i wklej poniższy kod VBA do nowego modułu.
VBA: Dodaj na początku komórek
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Uwaga: w skrypcie kodu, iphone to tekst, który chcesz dodać na początku wybranych komórek, zmień go według potrzeb. |
4. naciśnij F5 kliknij lub kliknij run przycisk 
w oknie, aby aktywować kod VBA.
Teraz do wszystkich wybranych komórek dodano tekst „Iphone” na początku.
Oprócz dodawania tekstu na początku komórek, dodawanie tekstu na końcu komórek jest również powszechne. Tutaj bierzemy dodawanie jednostki na końcu każdej komórki dla przykładów, jak pokazano na poniższym zrzucie ekranu:
2.1 Dodaj na końcu komórek za pomocą formuły
Tutaj możesz wybrać jedną z trzech poniższych formuł:
Formuła 1 Konkatenacja operatora „&”
Połącz wiele tekstów za pomocą znaku ampersand „&”.
Komórka&"tekst"
Funkcja CONCATENATE Formuły 2
Funkcja CONCATENATE służy do łączenia ze sobą tekstów.
ZŁĄCZ.TEKSTY(komórka;"tekst")
Formuła 3 funkcja CONCAT
To nowa funkcja, która pojawia się tylko w Excel 2019, Office 365 i Excel online.
CONCAT(komórka;"tekst")
W formułach ogólnych: komórka to odwołanie do komórki do tekstu, do którego chcesz dodać przyrostek, a tekst to tekst użyty do dodania do komórki.
Korzystając z powyższego przykładu, możesz użyć formuł na pasku formuły w następujący sposób:
„&”
=B3&$E$3 or =B3&"Kg"
ZŁĄCZ, funkcja
=ZŁĄCZ.TEKSTY(B3;$E$3) or =ZŁĄCZ.TEKSTY(B3;"Kg")
Funkcja CONCAT
=KONKAT(B3;$E$3) or =KONKAT(B3;"Kg")
Naciśnij przycisk Wchodzę aby uzyskać wynik, a następnie przeciągnij uchwyt autouzupełniania w dół, aby dodać tekst na końcu każdej komórki z zakresu B3:B6.
| Uwaga: Tekst powinien być ujęty w cudzysłów lub formuła zwraca wartość błędu #NAZWA?. Jeśli używasz odwołania do komórki do tekstu, pamiętaj, aby użyć odniesienia bezwzględnego, możesz nacisnąć klawisz F4, aby zmienić odniesienie względne na odwołanie bezwzględne. |
2.2 Dodaj na końcu komórek przez Flash Fill
Jeśli korzystasz z programu Excel 2013 lub nowszego, nowa, zaawansowana funkcja, Wypełnienie błyskiem, może wypełnić komórki na podstawie powyższych komórek wprowadzonych automatycznie.
Wybierz komórkę obok pierwszych oryginalnych danych, ręcznie wpisz pierwsze dane z tekstem sufiksu, jak pokazano poniżej:
Następnie aktywuj komórkę pod pierwszym wynikiem, kliknij Dane > Wypełnienie błyskiem, wszystkie poniższe komórki, w tym komórka aktywna, zostaną wypełnione automatycznie.
2.3 Dodaj na końcu komórek za pomocą kodu VBA
Oto kod VBA, który może dodać tekst na końcu każdej komórki w zakresie.
1. Wybierz zakres komórek, do których chcesz dodać tekst sufiksu, a następnie naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. Następnie w wyskakującym okienku kliknij wstawka > Moduł aby wstawić pusty nowy moduł.
3. Skopiuj i wklej poniższy kod VBA do nowego modułu.
VBA: Dodaj na końcu komórek
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Uwaga: w skrypcie kodu, Kg to tekst, który chcesz dodać na końcu wybranych komórek, zmień go według potrzeb. |
4. naciśnij F5 kliknij lub kliknij run przycisk w oknie, aby aktywować kod VBA.
Teraz tekst „Kg” został dodany na końcu każdej komórki.
Dla większości z was dodawanie tekstów na początku lub na końcu komórek jest łatwe, ale dodawanie tekstów do środka komórek może być nieco trudne. Weźmy na przykład, jak pokazano na poniższym zrzucie ekranu, dodając ogranicznik, taki jak dwukropek: na końcu drugiego znaku każdej komórki w zakresie B3: B7.
3.1 Dodaj po n-tym znaku ciągu według wzoru
Oto cztery formuły, które pomogą Ci obsłużyć to zadanie, wybierz jedną, której chcesz użyć:
Formuła 1 Połącz funkcje LEWEJ i PRAWEJ
LEWY(komórka, n) & "tekst" & PRAWY(komórka, LEN(komórka) -n)
Formuła 2 POŁĄCZ ŁĄCZENIE (KONCAT), funkcje PRAWO i DL
CONCATENATE(LEFT(komórka, n), "tekst", RIGHT(komórka, LEN(komórka) -n))
Or
CONCAT(LEWY(komórka, n), "tekst", PRAWY(komórka, LEN(komórka) -n))
Funkcja ZAMIEŃ Formuły 3
ZAMIEŃ(komórka, n+1, 0, "tekst")
W ogólnych formułach: komórka to odwołanie do komórki do tekstu, do którego chcesz dodać tekst w środku, a tekst to tekst użyty do dodania do komórki, n to liczba określająca, po którym znaku w ciągu tekstowym chcesz Dodaj tekst.
Do rozwiązania powyższego zadania użyj następujących wzorów:
Połącz funkcje LEWY i PRAWY
=LEWO(B3, 2) & ":" & PRAWO(B3, LEN(B3) -2) or =LEWO(B3) & $E$2 & PRAWO(B3,DŁ(B3)-3)
Połącz funkcje CONCATENATE (CONCAT), RIGHT i LEN
=ZŁĄCZ.TEKSTY(LEWO(B3;2);::;;PRAWO(B3;DŁ(B3)-2)) or =CONCAT (LEWO(B3, 2), ":", PRAWO(B3, LEN(B3) -2))
ZAMIEŃ, funkcja
=ZASTĄP(B3, 2+1, 0, ":") or =ZASTĄP(B3; 2+1; 0; $E$3)
Naciśnij przycisk Wchodzę aby uzyskać wynik, a następnie przeciągnij uchwyt autouzupełniania w dół, aby dodać tekst po drugim znaku każdej komórki z zakresu B3:B7.
| Uwaga: Tekst powinien być ujęty w cudzysłów lub formuła zwraca wartość błędu #NAZWA?. Jeśli używasz odwołania do komórki do tekstu, pamiętaj, aby użyć odniesienia bezwzględnego, możesz nacisnąć klawisz F4, aby zmienić odniesienie względne na odwołanie bezwzględne. |
3.2 Dodaj po n-tym znaku ciągu przez Flash Fill
Jeśli korzystasz z programu Excel 2013 lub nowszych wersji, Wypełnienie błyskiem, może wypełnić komórki na podstawie powyższych komórek wprowadzonych automatycznie.
Wybierz komórkę obok pierwszych oryginalnych danych, ręcznie wpisz pierwsze dane z dwukropkiem po drugim znaku, jak pokazano poniżej:
Następnie aktywuj komórkę pod pierwszym wynikiem, naciśnij Ctrl + E klawisze do aktywacji Wypełnienie błyskiem polecenie, wszystkie poniższe komórki, w tym komórka aktywna, zostały wypełnione automatycznie.
| Uwaga: Jeśli jesteś w systemie MAC, naciśnij klawisze Command + E. |
3.3 Dodaj po n-tym znaku ciągu za pomocą kodu VBA
Kod VBA również może wykonać tę pracę.
1. naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. Następnie w wyskakującym okienku kliknij wstawka > Moduł aby wstawić pusty nowy moduł.
3. Skopiuj i wklej poniższy kod VBA do nowego modułu.
VBA: dodaj do środka komórek
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| Uwaga: w skrypcie kodu, 2 to liczba określa pozycję, po której chcesz dodać tekst, : to tekst, do którego chcesz dodać, 3 to suma 2 i długości dodawanego tekstu. Zrób kolejną instancję, aby dodać „i” po pierwszym znaku ciągu „AB”, skrypt powinien mieć postać Rng.Value = VBA.Left(Rng.Value, 1) i „i& VBA.Środek(Rng.Wartość, 4, VBA.Len(Rng.Value) - 1) |
4. naciśnij F5 kliknij lub kliknij run przycisk w oknie, aby aktywować kod VBA. Pojawi się okno dialogowe wyboru komórek do dodania tekstu.
5. kliknij OK. Teraz tekst ":" został dodany po drugim znaku każdej komórki w zakresie B19:B23.
Jeśli jesteś zmęczony formułami i VBA, możesz wypróbować potężne i poręczne narzędzie, Kutools dla programu Excel, który może ci w tym pomóc Dodaj tekst cecha.
Ta funkcja dodawania tekstu może:
- Dodaj tekst przed pierwszym znakiem
- Dodaj tekst po ostatnim znaku
- Dodaj tekst w określonych pozycjach
- Rozbudowa
Dodaj tekst przed wielkimi literami
Dodaj tekst przed małymi literami
Dodaj tekst przed wielkimi/małymi literami
Dodaj tekst przed znakami numerycznymi
Kliknij, aby uzyskać 30-dniowy bezpłatny okres próbny już teraz.
Zaznacz komórki, do których chcesz dodać tekst, a następnie zastosuj Dodaj tekst, klikając Kutools > Tekst > Dodaj tekst.
4.1 Dodaj tekst przed pierwszym znakiem lub po ostatnim znaku
W Dodaj tekst wpisz tekst, który chcesz dodać do komórek w Tekst pole tekstowe, a następnie sprawdź Przed pierwszą postacią Pozycja 
Kliknij Aplikuj or Ok aby zakończyć dodawanie.
Aby dodać tekst po ostatnim znaku, wpisz tekst, który chcesz dodać do komórek w Tekst pole tekstowe, a następnie sprawdź Po ostatnim znaku Pozycja 
Kliknij Aplikuj or Ok aby zakończyć dodawanie.
4.2 Dodaj tekst w określonych pozycjach
Czasami możesz chcieć dodać tekst na środku komórki lub dodać tekst do kilku pozycji komórki. Przypuśćmy dodanie separatora „-” po trzeciej i siódmej cyfrze numerów społecznościowych, jak pokazano na poniższym zrzucie ekranu:
W Dodaj tekst wpisz tekst do Tekst pole tekstowe i zaznacz Sprecyzować opcji, a następnie wpisz pozycje przecinkiem, aby oddzielić je w polu tekstowym, a następnie kliknij Ok or Aplikuj.
4.3 Rozszerzenie narzędzia Dodaj tekst
Oprócz dodawania tekstu w normalnych pozycjach (początek, koniec i określona pozycja), Dodaj tekst narzędzie obsługuje również dodawanie tekstu
- Przed dużymi literami
- Przed małymi literami
- Przed wielkimi/małymi literami
- Przed znakami numerycznymi
W Dodaj tekst wpisz tekst do Tekst pole tekstowe i zaznacz Dodawaj tylko do opcję, a następnie kliknij menu rozwijane poniżej, aby wybrać żądaną operację.
Ta część zawiera formuły dotyczące dodawania tekstu przed lub po pierwszym pojawieniu się określonego tekstu w programie Excel.
5.1 Dodaj tekst przed pierwszym konkretnym tekstem
Na przykład chcesz dodać tekst „(sprzedaż)” przed pierwszym tekstem „cena” w komórce, jak pokazano na poniższych zrzutach ekranu:
W programie Excel funkcje REPLACE i SUBSTITUTE mogą rozwiązać to zadanie.
ZAMIEŃ, funkcja
REPLACE(komórka,ZNAJDŹ("znajdź_tekst",komórka),0,"dodaj_tekst")
PODSTAW, funkcja
SUBSTITUTE(komórka;"znajdź_tekst","zamień_tekst",1)
W takim przypadku skorzystaj z powyższych formuł, ponieważ:
=ZASTĄP(B4,ZNAJDŹ("cena";B4);0;"(sprzedaż)")
Or
=ZAMIENNIK(B4,"cena","(sprzedaż)cena",1)
Argumentami formuły w tym przypadku są
Komórka: B4,
Znajdź_tekst: cena,
Add_text: (sprzedaż),
Replace_text:(wyprzedaż)cena.
Naciśnij przycisk Wchodzę aby uzyskać pierwszy wynik dodawania, a następnie przeciągnij uchwyt autouzupełniania nad komórkami, które wymagają tej formuły.
5.2 Dodaj tekst po pierwszym określonym tekście
Na przykład chcesz dodać symbol waluty „$” po pierwszym dwukropku „:” w komórce, jak pokazano na poniższych zrzutach ekranu:
W programie Excel funkcje REPLACE i SUBSTITUTE mogą rozwiązać to zadanie.
ZAMIEŃ, funkcja
ZASTĄP(komórka;ZNAJDŹ("znajdź_tekst",komórka);znajdź_długość_tekstu;"zastąp_tekst")
PODSTAW, funkcja
SUBSTITUTE(komórka;"znajdź_tekst","zamień_tekst")
W takim przypadku skorzystaj z powyższych formuł, ponieważ:
=ZASTĄP(B12;ZNAJDŹ(":";B12);1":$")
Or
=SUBSTITUTE(B12,":";":$")
Argumentami formuły w tym przypadku są
Komórka: B12,
Znajdź tekst: :,
Zamień_tekst::$.
Znajdź_długość_tekstu:1
Naciśnij przycisk Wchodzę aby uzyskać pierwszy wynik dodawania, a następnie przeciągnij uchwyt autouzupełniania nad komórkami, które wymagają tej formuły.
W tej części samouczka wprowadzono trzy różne metody (formuła, Znajdź i zamień, VBA), które pomogą Ci dodać znaki przed lub po każdym słowie komórki.
| Dodaj znaki przed każdym słowem |
 |
| Dodaj znaki po każdym słowie |
 |
6.1 Dodaj znak(i) przed lub po każdym słowie według formuły
Funkcja SUBSTITUTE może być użyta do rozwiązania tego zadania, ogólne formuły, takie jak:
Dodaj przed każdym słowem
"znak"&SUBSTITUTE(ciąg; " "," znak")
Dodaj po każdym słowie
SUBSTITUTE(ciąg" ","znak ")&"znak"
Zakładając, że dodasz „+” przed każdym słowem na liście B3: B6, użyj następującej formuły:
="+"&ZAMIENNIK(B3; " "," +")
Naciśnij przycisk Wchodzę aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania w dół, aby uzyskać wszystkie wyniki.
Zakładając, że dodajemy ($) po każdym słowie listy B11:B14, użyj poniższego wzoru:
=ZAMIENNIK(B11," ","($) ")&"($)"
Naciśnij przycisk Wchodzę aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania w dół, aby uzyskać wszystkie wyniki.
6.2 Dodaj znak(i) przed lub po każdym słowie przez Znajdź i zamień
Jeśli chcesz dodać znaki przed każdym słowem z wyjątkiem pierwszego lub dodać znaki po każdym słowie, z wyjątkiem ostatniego, jak pokazano na poniższym zrzucie ekranu, wbudowany Excel Znajdowanie i zamienianie narzędzie może obsłużyć to zadanie.
Dodaj przed każdym słowem z wyjątkiem pierwszego
Dodaj po każdym słowie z wyjątkiem ostatniego
1. Wybierz komórki, do których chcesz dodać znaki przed lub po każdym słowie, a następnie naciśnij Ctrl + H klawisze, aby włączyć Znajdowanie i zamienianie dialog.
2. Wpisz spację w Znajdź co pole tekstowe, a następnie wpisz nowe znaki ze spacją w Zastąp pole tekstowe, a na koniec kliknij Zamień wszystko przycisk.
| Działanie | Ogólne | Przykład |
| Dodaj znaki przed każdym słowem | Dodaj nowe znaki po spacji | Dodaj (sprzedaż) przed każdym słowem |
| Działanie | Ogólne | Przykład |
| Dodaj znaki po każdym słowie | Nowe znaki, po których następuje spacja | Dodaj - po każdym słowie |
Tutaj znajdują się dwa kody VBA do dodawania znaków przed lub po każdym słowie, uruchom kod, wykonując poniższe czynności.
1. naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. W wyświetlanym oknie kliknij wstawka > Moduł aby wstawić nowy pusty moduł.
3. Skopiuj i wklej poniższy kod VBA do modułu.
Dodaj przed każdym słowem
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 SubDodaj po każdym słowie
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. naciśnij F5 klucz do uruchomienia kodu (lub możesz kliknąć run przycisk ), pojawi się okno dialogowe z prośbą o wybranie ciągłych komórek do pracy, kliknij OK.
5. W drugim wyskakującym oknie dialogowym wpisz znaki, które chcesz dodać, kliknij OK.
Następnie znaki zostaną dodane na początku lub na końcu każdego słowa.
Czasami możesz chcieć dodać tekst między każdymi znakami. W tej części wymieniono dwa scenariusze, jeden to dodanie tekstu między każdym znakiem, drugi to dodanie odstępu między każdą liczbą.
7.1 Dodaj tekst pomiędzy każdym znakiem/cyfrą za pomocą funkcji zdefiniowanej przez użytkownika
Aby dodać znaki między każdym znakiem w ciągu, może ci pomóc funkcja zdefiniowana przez użytkownika.
W tym przypadku dodajemy spację między każdym znakiem.
1. naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. W wyświetlanym oknie kliknij wstawka > Moduł aby wstawić nowy pusty moduł.
3. Skopiuj i wklej poniższy kod VBA do modułu.
Dodaj tekst między znakami
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. Zapisz kod i zamknij okno, aby wrócić do arkusza roboczego, a następnie wpisz poniżej formułę w komórce, w której chcesz umieścić wynik dodawania.
=DodajTekst(B3)
B3 to komórka zawierająca ciąg, do którego chcesz dodać spację między znakami.
5. naciśnij Wchodzę klucz, aby uzyskać wynik, przeciągnij uchwyt automatycznego wypełniania nad komórkami, aby uzyskać inne wyniki zgodnie z potrzebami.
|
Uwaga: 1）W kodzie VBA możesz zmienić "" do innych tekstów, jeśli potrzebujesz, przypuśćmy, że zmień go na "-" dodać - między znakami. 2） Kod doda nowy tekst po każdym znaku. Możesz usunąć ostatnio dodany tekst, jeśli nie potrzebujesz, korzystając z poniższej formuły. Więcej informacji o formule znajdziesz na: Jak usunąć pierwsze lub ostatnie N znaków z komórki lub ciągu w Exctak? LEWY(komórka;DŁ(komórka)-długość_tekstu) |
7.2 Dodaj spację między każdą liczbą za pomocą wzoru
Jeśli chcesz dodać spację między cyframi ciągu, który jest wartością numeryczną, możesz użyć funkcji TEKST.
| Uwaga: numery zakresu, między którymi chcesz dodać spację, muszą być tej samej długości, w przeciwnym razie niektóre wyniki mogą być nieprawidłowe. |
Przypuśćmy, że tutaj jest lista 8-cyfrowych liczb z zakresu B10:B12, wybierz komórkę, w której zostanie umieszczony wynik, wpisz poniżej formułę:
=TEKST(B10;"# # # # # # # #")
Naciśnij przycisk Wchodzę klawisza, a następnie przeciągnij uchwyt autouzupełniania na inne komórki.
Jeśli długość liczb wynosi 5, formuła zostanie zmieniona jako =TEKST(B10;"# # # # #").
Jeśli chcesz dodać znaki między każdym słowem, zakładając, że dodaj spację między pierwszym, drugim imieniem i nazwiskiem, jak pokazano na poniższym zrzucie ekranu, możesz użyć funkcji zdefiniowanej przez użytkownika.
1. naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. W wyświetlanym oknie kliknij wstawka > Moduł aby wstawić nowy pusty moduł.
3. Skopiuj i wklej poniższy kod VBA do modułu.
Dodaj znaki między słowami
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. Zapisz kod i zamknij okno, aby wrócić do arkusza roboczego, a następnie wpisz poniżej formułę w komórce, w której chcesz umieścić wynik dodawania.
=Dodaj znaki(B3)
B3 to komórka zawierająca ciąg, do którego chcesz dodać spację między słowami.
5. naciśnij Wchodzę klucz, aby uzyskać wynik, przeciągnij uchwyt automatycznego wypełniania nad komórkami, aby uzyskać inne wyniki zgodnie z potrzebami.
| Uwaga: w kodzie VBA można zmienić "" do innych tekstów, jeśli potrzebujesz, przypuśćmy, że zmień go na "-" dodać - między słowami. |
Aby dodać określone znaki między tekstami i liczbami, jak pokazano na poniższym zrzucie ekranu, tutaj przedstawiono dwie metody.
9.1 Dodaj znak między tekstami i liczbami za pomocą formuły
Jeśli teksty znajdują się na początku liczb, ogólna formuła to
PRZYTNIJ(ZASTĄP(ciąg,MIN(ZNAJDŹ({1,2,3,4,5,6,7,8,9,0},ciąg&"1234567890")),0,"-"))
Używając powyższego przypadku jako przykładu, użyj wzoru w następujący sposób:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Naciśnij przycisk Wchodzę klucz, aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania nad innymi komórkami, aby wypełnić tę formułę.
Jeśli liczby znajdują się na początku tekstów, ogólna formuła to
LEFT(ciąg,SUM(DŁUG(ciąg)-DŁUG(PODSTAW(ciąg,{0,1,2,3,4,5,6,7,8,9},""))))&"."&PRAWY (ciąg,LEN(B13)- SUM(LEN(ciąg)-LEN(SUBSTITUTE(ciąg, {0,1,2,3,4,5,6,7,8,9}, ""))))
Korzystając z powyższego przypadku jako przykładu, użyj wzoru w następujący sposób:
=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}, ""))))
Naciśnij przycisk Wchodzę klucz, aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania nad innymi komórkami, aby wypełnić tę formułę.
9.2 Dodaj znak między tekstami i cyframi za pomocą funkcji Flash Fill
Jeśli korzystasz z programu Excel 2013 lub nowszych wersji, Wypełnienie błyskiem może również pomóc w dodawaniu znaków między tekstami i cyframi.
Obok oryginalnych danych wpisz nowe dane, które zostały dodane, znak(i) między tekstami i cyframi. Następnie naciśnij klawisz Enter, aby przejść do następnej komórki pierwszego wyniku.
I naciśnij Ctrl + E klawisze, aby włączyć funkcję Flash Fill.
Rozbudowa
W tym samouczku wymieniono również niektóre scenariusze dotyczące dodawania tekstu, które możemy spotkać w naszym codziennym życiu lub pracy.
Czasami musisz dodać separatory, takie jak myślniki, do serii numerów telefonów lub numerów społecznościowych, jak pokazano na poniższym zrzucie ekranu. Ręczne wpisywanie zostanie wypróbowane, jeśli trzeba dodać setki liczb. W tej części przedstawia trzy sposoby na szybkie wykonanie tej pracy.
10.1 Dodaj myślniki do liczb według wzoru
Oto dwie formuły, które pozwalają szybko dodawać myślniki do liczb. Aby dodać myślniki po trzeciej i szóstej liczbie w komórce B3, użyj następującej formuły:
ZAMIEŃ, funkcja
=ZASTĄP(ZASTĄP(B3,4,0;"-");8,0;"-")
TEKST, funkcja
=TEKST(B3;"???-???-???")
Naciśnij przycisk Wchodzę aby uzyskać pierwszy wynik, a następnie przeciągnij uchwyt automatycznego wypełniania nad innymi komórkami.
|
Uwaga: Możesz zmienić argumenty powyższych formuł według potrzeb. Przypuśćmy, że dodaj "-" po drugiej, czwartej i szóstej cyfrze możesz użyć formuł jako: =ZASTĄP(ZASTĄP(ZASTĄP(B3,3,0;"-");6,0;"-"),9,0;"-") =TEKST(B3;"??-??-??-???") |
10.2 Dodaj myślniki do liczb według formatowania komórek
Aby bezpośrednio dodać myślniki do liczb w oryginalnych komórkach, możesz użyć Formatowanie komórek cecha.
1. Wybierz liczby, do których chcesz dodać myślniki, a następnie naciśnij Ctrl + 1 klawisze, aby włączyć Formatowanie komórek dialog.
2. w Formatowanie komórek okno dialogowe w obszarze Numer kartę, wybierz Zamówienia Indywidualne z Kategoria
3. Następnie w prawo Rodzaj Nieruchomości sekcja, typ ###-###-### w polu tekstowym kliknij OK.
Teraz do wybranych liczb dodano myślniki.
10.3 Dodaj myślniki do numeru telefonu, dodając tekst Kutools for Excel
Jeśli Kutools dla programu Excel zainstalowany w programie Excel, Dodaj tekst Funkcja może również wyświadczyć ci przysługę.
1. Wybierz komórki zawierające liczby potrzebne do dodania myślników, kliknij Kutools > Tekst > Dodaj tekst.
2. w Dodaj tekst w oknie dialogowym wpisz myślnik lub inny separator do Tekst pole tekstowe, wybierz Sprecyzować opcji, a następnie wpisz pozycje, po których chcesz dodać myślniki, i oddziel je przecinkami w poniższym polu tekstowym.
3. kliknij Ok lub Zastosuj. Następnie do wybranych liczb dodano myślniki.
Darmowe pobieranie Dodaj tekst na 30-dniowy okres próbny.
Przypuśćmy, że istnieje lista liczb o różnych długościach, chcesz dodać wiodące zera, aby miały tę samą długość i wyglądały schludnie, jak pokazano na zrzucie ekranu. Oto dwie metody obsługi zadania.
11.1 Dodaj wiodące zera do komórek za pomocą funkcji Formatuj komórki
Możesz użyć Formatowanie komórek funkcja, aby ustawić niestandardowy format, aby ustalić długość komórki, dodając wiodące zera.
1. Wybierz komórki, do których chcesz dodać wiodące zera, a następnie naciśnij Ctrl + 1 klucze do włączenia Formatowanie komórek dialog.
2. w Formatowanie komórek okno dialogowe w obszarze Numer kartę, wybierz Zamówienia Indywidualne z Kategoria
3. Następnie w prawo Rodzaj Nieruchomości sekcja, typ 0000 w pole tekstowe (aby ustawić długość liczby na 4 cyfry, wpisz 0000, możesz zmienić ją na inną, np. 5 cyfr, wpisz 00000), kliknij OK.
Następnie do liczb zostały dodane wiodące zera o tej samej długości.
| Uwaga: jeśli długość oryginalnych danych przekracza ustawioną długość, wyświetli oryginalne dane bez wiodących zer. |
11.2 Dodaj wiodące zera do komórek za pomocą formuły
Jeśli nie chcesz zmieniać oryginalnych danych, możesz użyć formuły, aby dodać wiodące zera do liczb w innych lokalizacjach.
Oto trzy funkcje, które mogą ci pomóc.
Funkcja TEKST Formuły 1
TEKST(liczba;"00…")
Formuła2 PRAWA funkcja
PRAWY("00…"&liczba,długość)
Funkcja PODSTAWY Formuły 3
PODSTAWA(liczba,10,długość)
Liczba zer we wzorze jest równa długości liczby.
Tutaj ustawiasz 4-cyfrową długość liczb, użyj poniższych wzorów:
= TEKST (B10; "0000")
=PRAWY("0000"&B10,4)
=BAZA(B10,10,4;XNUMX)
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania w dół.
| Uwaga: jeśli długość oryginalnych danych przekracza ustawioną długość, wyświetli oryginalne dane bez wiodących zer. |
Jeśli chcesz dodać końcowe zera do liczb, aby uzyskać stałą długość, przypuśćmy, że dodając końcowe zera do liczb w komórce B3: B7, aby uzyskać 5-cyfrową długość, jak pokazano na zrzucie ekranu, możesz zastosować formułę, aby poradzić sobie z zadaniem .
Funkcja POWT
liczba&POWT("0",długość-LEN(liczba))
W formule argument „liczba” reprezentuje liczbę lub odwołanie do komórki, do którego chcesz dodać końcowe zera, a argument „długość” to żądana stała długość.
Użyj poniższego wzoru:
=B3&POWT("0",5-DŁ(B3))
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania w dół do komórek, które wymagają tej formuły.
Jeśli chcesz wyeksportować lub skopiować dane z arkusza roboczego do notatnika, teksty mogą być umieszczone nieporządnie, ponieważ wartości komórek mają różne długości, aby wyglądały schludnie, jak pokazano na poniższym zrzucie ekranu, musisz dodać spację końcową.
Aby rozwiązać tę pracę, funkcja REPT może wyświadczyć ci przysługę.
LEWO(tekst i POWT(" ",maks.długość);maks.długość)
W formule argument „tekst” to tekst lub odwołanie do komórki, do którego chcesz dodać spację końcową, a argument „maks.długość” to stała długość tekstu, która musi być dłuższa lub równa maksymalna długość użytych tekstów.
Aby dodać spację końcową do tekstów w komórce B3:C6, użyj poniższej formuły:
=LEWO(B3 i POWT("",10);10)
Naciśnij przycisk Wchodzę klucz, a następnie przeciągnij uchwyt automatycznego wypełniania do wszystkich komórek, które wymagają tej formuły.
W tym przypadku, ponieważ najdłuższy z użytych tekstów ma 9 znaków, używamy tutaj 10.
Czasami trzeba dodać cudzysłowy lub nawiasy wokół tekstu lub liczb w komórkach, jak pokazano na poniższym zrzucie ekranu, z wyjątkiem ręcznego wpisywania jeden po drugim, oto dwie metody w programie Excel, które mogą szybko to rozwiązać.
14.1 Dodaj cudzysłowy/nawiasy wokół według Formatuj komórki
Jeśli chcesz dodać znaki cudzysłowu wokół tekstu w oryginalnych danych, może Ci pomóc funkcja Formatuj komórki w programie Excel.
1. Wybierz komórki, do których chcesz dodać cudzysłowy, naciśnij Ctrl + 1 klawisze, aby włączyć Formatowanie komórek dialog.
2. w Formatowanie komórek dialog, pod Numer kartę, wybierz Zamówienia Indywidualne z listy Kategoria, a następnie przejdź do prawej sekcji, aby wpisać "''" @ "''" do Rodzaj Nieruchomości pole tekstowe. Kliknij OK.
Tekst w wybranych komórkach został dodany w cudzysłowie.
Jeśli chcesz dodać nawiasy wokół tekstu, w polu tekstowym Typ okna dialogowego Formatowanie komórek użyj (@).
14.2 Dodaj cudzysłowy/nawiasy wokół wzorów
Formuły mogą być również używane do dodawania cudzysłowów do tekstu w innych komórkach.
Korzystanie i złącze
""""&tekst&""""
Or
znak(34)&tekst&znak(34)
W takim przypadku tekst w komórce B11, użyj następujących formuł:
=""""&B11&""""
Or
=znak(34)&B11&znak(34)
Naciśnij przycisk Wchodzę i przeciągnij uchwyt automatycznego wypełniania w dół, aby wypełnić komórki.
Jeśli chcesz dodać nawiasy wokół tekstu, używając formuły jako "("&tekst&")".
14.3 Dodaj cudzysłowy/nawiasy wokół przez VBA
Jeśli potrzebujesz kodu VBA do rozwiązania tego zadania, oto VBA.
1. naciśnij inny + F11 klawisze, aby włączyć Microsoft Visual Basic for Applications okno.
2. Następnie w wyskakującym okienku kliknij wstawka > Moduł aby wstawić pusty nowy moduł.
3. Skopiuj i wklej poniższy kod VBA do nowego modułu.
VBA: Dodaj cudzysłowy wokół tekstów
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| Uwaga: w skrypcie kodu, """" & Wartość.Rng & """" wskazuje, aby dodać "" wokół tekstów, jeśli chcesz dodać nawiasy () wokół tekstu, zmień go na „(” i wartość.zad i „)”. |
4. naciśnij F5 kliknij lub kliknij run przycisk w oknie, aby aktywować kod VBA. Pojawi się okno dialogowe wyboru komórek do dodania cudzysłowów.
5. kliknij OK. Teraz dodano cudzysłowy wokół tekstów w wybranych komórkach.
Czasami, aby pomóc innym użytkownikom lepiej zrozumieć dane, może być konieczne dodanie tekstu w wynikach formuły, jak pokazano na zrzucie ekranu. Aby uwzględnić tekst w formule, możesz użyć cudzysłowów otaczających tekst i dodać łącznik &, aby połączyć tekst i formułę.
Przypadek 1 dodaj tekst przed formułą
„Dzisiaj to „&TEKST(DZIŚ()”, „dddd, mmmm dd.”)
Przypadek 2 dodaj tekst po formuła
TEKST(TERAZ();"GG:MM:SS")&" to aktualny czas."
Przypadek 3 dodaj teksty po dwóch stronach wzoru
"Dzisiaj sprzedano "&data&" kg."
Więcej samouczków Excela: |
|
Połącz wiele skoroszytów/arkuszy roboczych w jeden Podziel komórki tekstowe, liczbowe i dat (oddziel na wiele kolumn) Połącz zawartość wielu komórek bez utraty danych w programie Excel Porównaj dwie kolumny pod kątem dopasowań i różnic w programie Excel |
Najlepsze narzędzia biurowe
Kutools dla programu Excel rozwiązuje większość problemów i zwiększa produktywność o 80%
- Pasek Super Formula (łatwo edytować wiele wierszy tekstu i formuły); Układ do czytania (łatwe odczytywanie i edytowanie dużej liczby komórek); Wklej do filtrowanego zakresu...
- Scal komórki / wiersze / kolumny i przechowywanie danych; Podziel zawartość komórek; Połącz zduplikowane wiersze i sumę / średnią... Zapobiegaj zduplikowanym komórkom; Porównaj zakresy...
- Wybierz Duplikat lub Unikalny Wydziwianie; Wybierz puste wiersze (wszystkie komórki są puste); Super Find i Fuzzy Find w wielu zeszytach ćwiczeń; Losowy wybór ...
- Dokładna kopia Wiele komórek bez zmiany odwołania do formuły; Automatyczne tworzenie odniesień do wielu arkuszy; Wstaw punktory, Pola wyboru i nie tylko ...
- Ulubione i szybkie wstawianie formuł, Zakresy, wykresy i obrazy; Szyfruj komórki z hasłem; Utwórz listę mailingową i wysyłaj e-maile ...
- Wyodrębnij tekst, Dodaj tekst, Usuń według pozycji, Usuń przestrzeń; Tworzenie i drukowanie podsumowań stronicowania; Konwertuj zawartość komórek i komentarze...
- Super filtr (zapisz i zastosuj schematy filtrów do innych arkuszy); Zaawansowane sortowanie według miesiąca / tygodnia / dnia, częstotliwości i innych; Specjalny filtr pogrubieniem, kursywą ...
- Połącz skoroszyty i arkusze robocze; Scal tabele na podstawie kluczowych kolumn; Podziel dane na wiele arkuszy; Konwersja wsadowa xls, xlsx i PDF...
- Grupowanie tabel przestawnych według numer tygodnia, dzień tygodnia i więcej ... Pokaż odblokowane, zablokowane komórki w różnych kolorach; Podświetl komórki, które mają formułę / nazwę...
- Włącz edycję i czytanie na kartach w programach Word, Excel, PowerPoint, Publisher, Access, Visio i Project.
- Otwieraj i twórz wiele dokumentów w nowych kartach tego samego okna, a nie w nowych oknach.
- Zwiększa produktywność o 50% i redukuje setki kliknięć myszką każdego dnia!
