Excel menambahkan teks dan angka ke posisi sel yang ditentukan
Di Excel, menambahkan teks atau angka ke sel adalah pekerjaan yang sangat umum. Seperti menambahkan spasi di antara nama, menambahkan awalan atau akhiran ke sel, menambahkan tanda hubung ke nomor sosial. Di sini, di tutorial ini, ini mencantumkan hampir semua skenario penambahan di Excel dan menyediakan metode yang sesuai untuk Anda.
Note
Dalam tutorial ini, saya membuat beberapa contoh untuk menjelaskan metode, Anda dapat mengubah referensi yang Anda butuhkan saat menggunakan kode atau rumus VBA di bawah ini, atau Anda dapat mengunduh sampel untuk mencoba metode secara langsung.
Klik untuk mendownload file contoh
Bagian ini mencantumkan metode yang berbeda untuk menambahkan karakter ke awal setiap sel seperti gambar di bawah ini:
1.1 Tambahkan di awal sel dengan menggunakan rumus
Di sini Anda dapat memilih salah satu dari tiga formula di bawah ini:
Formula1 Penggabungan operator "&"
Gabungkan beberapa teks bersama-sama dengan karakter ampersand "&".
"teks"&sel
Fungsi CONCATENATE Formula2
Fungsi CONCATENATE digunakan untuk menggabungkan teks.
CONCATENATE("teks", sel)
Fungsi CONCAT Formula3
Ini adalah fungsi baru yang hanya muncul di Excel 2019, Office 365, dan Excel online.
CONCAT("teks", sel)
Dalam rumus umum: sel adalah referensi sel ke teks yang ingin Anda tambahkan awalan, dan teks adalah teks yang digunakan untuk ditambahkan ke sel.
Dengan menggunakan contoh di atas, Anda dapat menggunakan rumus di bilah rumus sebagai berikut:
"&"
=$E$3&B3 or = "Iphone" & B3
Fungsi CONCATENATE
=GAMBUNGAN($E$3,B3) or =CONCATENATE("Iphone",B3)
Fungsi CONCAT
=CONCAT($E$3,B3) or =CONCAT("Iphone",B3)
Tekan Enter untuk mendapatkan hasilnya, lalu seret pegangan isi otomatis ke bawah untuk menambahkan teks ke setiap sel rentang B3:B6.
| Perhatian: Teks harus diapit tanda kutip ganda, atau rumus mengembalikan nilai kesalahan #NAME?. Jika menggunakan referensi sel ke teks, harap ingat untuk menggunakan referensi absolut, Anda dapat menekan tombol F4 untuk mengubah referensi relatif menjadi referensi absolut. |
1.2 Tambahkan di awal sel dengan Flash Fill
Jika Anda menggunakan Excel 2013 atau versi yang lebih baru, fitur baru yang canggih, Isi Flash, dapat mengisi sel berdasarkan sel di atas yang Anda masukkan secara otomatis.
Pilih sel di sebelah data asli pertama, ketikkan data pertama secara manual dengan teks awalan seperti yang ditunjukkan di bawah ini:
Kemudian pada sel di bawah, lanjutkan mengetik data kedua dengan teks awalan，saat mengetik, daftar berwarna abu-abu akan ditampilkan, tekan Enter untuk memungkinkan Flash Fill mengisi data secara otomatis.
Jika daftar pratinjau tidak menghasilkan, silakan buka tab Beranda, lalu klik Mengisi > Isi Flash untuk menjalankannya secara manual.
1.3 Tambahkan di awal sel dengan menggunakan kode VBA
Jika Anda sudah familiar dengan kode VBA, di sini memperkenalkan kode VBA untuk menambahkan teks di awal setiap sel dalam rentang.
1. Pilih rentang sel yang ingin Anda tambahkan teks awalan, lalu tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Kemudian di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul baru yang kosong.
3. Salin dan tempel kode VBA di bawah ini ke modul baru.
VBA: Tambahkan di awal sel
Sub AppendToExistingOnLeft()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = "Iphone" & c.Value
Next
End Sub| Perhatian: dalam skrip kode, iphone adalah teks yang ingin Anda tambahkan di awal sel yang dipilih, ubah sesuai kebutuhan Anda. |
4. tekan F5 kunci atau klik Run tombol 
di jendela untuk mengaktifkan kode VBA.
Sekarang semua sel yang dipilih telah ditambahkan teks "Iphone" di awal.
Kecuali menambahkan teks ke awal sel, menambahkan teks ke akhir sel juga umum. Di sini kami menambahkan unit di akhir setiap sel untuk contoh seperti gambar di bawah ini yang ditunjukkan:
2.1 Tambahkan di akhir sel dengan menggunakan rumus
Di sini Anda dapat memilih salah satu dari tiga formula di bawah ini:
Formula1 Penggabungan operator "&"
Gabungkan beberapa teks bersama-sama dengan karakter ampersand "&".
Sel&"teks"
Fungsi CONCATENATE Formula2
Fungsi CONCATENATE digunakan untuk menggabungkan teks.
CONCATENATE(sel,"teks")
Fungsi CONCAT Formula3
Ini adalah fungsi baru yang hanya muncul di Excel 2019, Office 365 dan Excel online.
CONCAT(sel,"teks")
Dalam rumus umum: sel adalah referensi sel ke teks yang ingin Anda tambahkan akhiran, dan teks adalah teks yang digunakan untuk menambahkan ke sel.
Dengan menggunakan contoh di atas, Anda dapat menggunakan rumus di bilah rumus sebagai berikut:
"&"
=B3&$E$3 or =B3&"kg"
Fungsi CONCATENATE
=CONCATENATE(B3, $E$3) or =CONCATENATE(B3, "Kg")
Fungsi CONCAT
=CONCAT(B3, $E$3) or =CONCAT(B3, "Kg")
Tekan Enter untuk mendapatkan hasilnya, lalu seret pegangan isi otomatis ke bawah untuk menambahkan teks ke akhir setiap sel rentang B3:B6.
| Perhatian: Teks harus diapit tanda kutip ganda, atau rumus mengembalikan nilai kesalahan #NAME?. Jika menggunakan referensi sel ke teks, harap ingat untuk menggunakan referensi absolut, Anda dapat menekan tombol F4 untuk mengubah referensi relatif menjadi referensi absolut. |
2.2 Tambahkan di akhir sel dengan Flash Fill
Jika Anda menggunakan Excel 2013 atau versi yang lebih baru, fitur baru yang canggih, Isi Flash, dapat mengisi sel berdasarkan sel di atas yang Anda masukkan secara otomatis.
Pilih sel di sebelah data asli pertama, ketikkan data pertama secara manual dengan teks akhiran seperti yang ditunjukkan di bawah ini:
Kemudian aktifkan sel di bawah hasil pertama, klik Data > Isi Flash, semua sel di bawah ini termasuk sel yang aktif akan terisi secara otomatis.
2.3 Tambahkan di akhir sel dengan menggunakan kode VBA
Berikut adalah kode VBA yang dapat menambahkan teks di akhir setiap sel dalam suatu rentang.
1. Pilih rentang sel yang ingin Anda tambahkan teks akhiran, lalu tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Kemudian di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul baru yang kosong.
3. Salin dan tempel kode VBA di bawah ini ke modul baru.
VBA: Tambahkan di akhir sel
Sub AppendToExistingOnRight()
Dim c As Range
For Each c In Selection
If c.Value <> "" Then c.Value = c.Value & "Kg"
Next
End Sub| Perhatian: dalam skrip kode, Kg adalah teks yang ingin Anda tambahkan di akhir sel yang dipilih, ubah sesuai kebutuhan Anda. |
4. tekan F5 kunci atau klik Run tombol di jendela untuk mengaktifkan kode VBA.
Sekarang teks "Kg" telah ditambahkan di akhir setiap sel.
Bagi sebagian besar dari Anda, menambahkan teks ke awal atau akhir sel itu mudah, tetapi menambahkan teks ke tengah sel mungkin agak sulit. Ambil contoh, seperti gambar di bawah ini, menambahkan pembatas, seperti titik dua : di akhir karakter kedua setiap sel dalam rentang B3:B7.
3.1 Tambahkan setelah karakter ke-n string dengan rumus
Berikut adalah empat formula yang disediakan bagi Anda untuk menangani pekerjaan ini, pilih satu untuk digunakan:
Formula1 Menggabungkan fungsi KIRI dan KANAN
KIRI(sel, n) & "teks" & KANAN(sel, LEN(sel) -n)
Formula2 MENGGABUNGKAN CONCATENATE(CONCAT), fungsi KANAN dan LEN
CONCATENATE(KIRI(sel, n), "teks", KANAN(sel, LEN(sel) -n))
Or
CONCAT(KIRI(sel, n), "teks", KANAN(sel, LEN(sel) -n))
Fungsi Formula3 REPLACE
REPLACE(sel, n+1, 0, "teks")
Dalam rumus umum: sel adalah referensi sel ke teks yang ingin Anda tambahkan teks di tengah, dan teks adalah teks yang digunakan untuk ditambahkan ke sel, n adalah angka yang ditentukan setelah karakter dalam string teks yang Anda inginkan Tambahkan teks.
Untuk menyelesaikan pekerjaan yang disebutkan di atas, gunakan rumus sebagai berikut:
Gabungkan fungsi KIRI dan KANAN
=KIRI(B3, 2) & ":" & KANAN(B3, LEN(B3) -2) or =KIRI(B3, 2) & $E$3 & KANAN(B3, LEN(B3) -2)
MENGGABUNGKAN CONCATENATE (CONCAT), KANAN dan fungsi LEN
=CONCATENATE(KIRI(B3, 2), ":", KANAN(B3, LEN(B3) -2)) or =CONCAT (KIRI(B3, 2), ":", KANAN(B3, LEN(B3) -2))
Fungsi REPLACE
=GANTI(B3, 2+1, 0, ":") or =GANTI(B3, 2+1, 0, $E$3)
Tekan Enter untuk mendapatkan hasilnya, lalu seret pegangan isi otomatis ke bawah untuk menambahkan teks setelah karakter kedua dari setiap sel rentang B3:B7.
| Perhatian: Teks harus diapit tanda kutip ganda, atau rumus mengembalikan nilai kesalahan #NAME?. Jika menggunakan referensi sel ke teks, harap ingat untuk menggunakan referensi absolut, Anda dapat menekan tombol F4 untuk mengubah referensi relatif menjadi referensi absolut. |
3.2 Tambahkan setelah karakter ke-n string dengan Flash Fill
Jika Anda menggunakan Excel 2013 atau versi yang lebih baru, Isi Flash, dapat mengisi sel berdasarkan sel di atas yang Anda masukkan secara otomatis.
Pilih sel di sebelah data asli pertama, ketikkan data pertama secara manual dengan titik dua setelah karakter kedua seperti yang ditunjukkan di bawah ini:
Kemudian aktifkan sel di bawah hasil pertama, tekan Ctrl + E tombol untuk mengaktifkan Isi Flash perintah, semua sel di bawah ini termasuk sel aktif telah terisi secara otomatis.
| Perhatian: Jika Anda berada di sistem MAC, tekan tombol Command + E. |
3.3 Tambahkan setelah karakter string ke-n dengan menggunakan kode VBA
Kode VBA juga dapat melakukan pekerjaan ini.
1. tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Kemudian di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul baru yang kosong.
3. Salin dan tempel kode VBA di bawah ini ke modul baru.
VBA: Tambahkan ke tengah sel
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| Perhatian: dalam skrip kode, 2 adalah nomor yang menentukan posisi yang ingin Anda tambahkan teks setelahnya, : adalah teks yang ingin Anda tambahkan, 3 adalah jumlah dari 2 dan panjang teks yang Anda tambahkan. Ambil contoh lain, untuk menambahkan "dan" setelah karakter pertama string "AB", skripnya harus Rng.Value = VBA.Left(Rng.Value, 1) & "dan" & VBA.Mid(Rng.Nilai, 4, VBA.Len(Rng.Nilai) - 1) |
4. tekan F5 kunci atau klik Run tombol di jendela untuk mengaktifkan kode VBA. Dialog muncul untuk memilih sel untuk menambahkan teks.
5. klik OK. Sekarang teks ":" telah ditambahkan setelah karakter kedua dari setiap sel dalam rentang B19:B23.
Jika Anda bosan dengan formula dan VBA, Anda dapat mencoba alat yang ampuh dan praktis, Kutools untuk Excel, yang dapat membantu Anda dengan itu Tambahkan Teks fitur.
Fitur Tambahkan teks ini dapat:
- Tambahkan teks sebelum karakter pertama
- Tambahkan teks setelah karakter terakhir
- Tambahkan teks pada posisi tertentu
- Perpanjangan
Tambahkan teks sebelum huruf besar
Tambahkan teks sebelum huruf kecil
Tambahkan teks sebelum huruf besar/kecil
Tambahkan teks sebelum karakter numerik
Klik untuk mendapatkan uji coba gratis 30 hari sekarang.
Pilih sel yang ingin Anda tambahkan teks, lalu terapkan Tambahkan Teks dengan mengklik Kutools > Teks > Tambahkan Teks.
4.1 Tambahkan teks sebelum karakter pertama atau setelah karakter terakhir
Dalam majalah Tambahkan Teks dialog, ketik teks yang ingin Anda tambahkan ke sel di Teks kotak teks, lalu centang Sebelum karakter pertama dalam Posisi bagian.
Klik Mendaftar or Ok untuk menyelesaikan penambahan.
Untuk menambahkan teks setelah karakter terakhir, ketik teks yang ingin Anda tambahkan ke sel di Teks kotak teks, lalu centang Setelah karakter terakhir dalam Posisi bagian.
Klik Mendaftar or Ok untuk menyelesaikan penambahan.
4.2 Tambahkan teks pada posisi tertentu
Terkadang, Anda mungkin ingin menambahkan teks ke tengah sel, atau menambahkan teks ke beberapa posisi sel. Misalkan menambahkan pemisah "-" setelah digit ketiga dan ketujuh dari nomor sosial seperti gambar di bawah ini:
Dalam majalah Tambahkan Teks dialog, ketik teks ke dalam Teks kotak teks, dan centang Menentukan pilihan, lalu ketik posisi menggunakan koma untuk memisahkan ke dalam kotak teks dan kemudian klik Ok or Mendaftar.
4.3 Perpanjangan utilitas Tambahkan Teks
Selain menambahkan teks ke posisi normal (awal, akhir dan posisi tertentu), Tambahkan Teks utilitas juga mendukung untuk menambahkan teks
- Sebelum huruf besar
- Sebelum huruf kecil
- Sebelum huruf besar/kecil
- Sebelum karakter numerik
Dalam majalah Tambahkan Teks dialog, ketik teks ke dalam Teks kotak teks, dan centang Hanya tambahkan ke pilihan, lalu klik menu tarik-turun di bawah untuk memilih operasi yang Anda butuhkan.
Bagian ini menyediakan rumus untuk menambahkan teks sebelum atau setelah teks tertentu yang pertama kali muncul di Excel.
5.1 Tambahkan teks sebelum teks spesifik pertama
Misalnya, Anda ingin menambahkan teks "(penjualan)" sebelum teks pertama "harga" di sel seperti gambar di bawah ini:
Di Excel, fungsi REPLACE dan SUBSTITUTE dapat menyelesaikan pekerjaan ini.
Fungsi REPLACE
REPLACE(sel,TEMUKAN("temukan_teks",sel),0,"tambahkan_teks")
Fungsi SUBSTITUTE
PENGGANTI(sel,"temukan_teks",,"ganti_teks",1)
Dalam hal ini, silakan gunakan rumus di atas sebagai berikut:
=GANTI(B4, TEMUKAN("harga",B4),0,"(penjualan)")
Or
=SUBSTITUTE(B4,"harga","(jual)harga",1)
Rumus argumen dalam kasus ini adalah
Sel: B4,
Temukan_teks: harga,
Tambahkan_teks: (penjualan),
Replace_text:(jual)harga.
Tekan Enter untuk mendapatkan hasil penambahan pertama, lalu seret pegangan isi otomatis ke sel yang membutuhkan rumus ini.
5.2 Tambahkan teks setelah teks spesifik pertama
Misalnya, Anda ingin menambahkan simbol mata uang "$" setelah titik dua pertama ":" di sel seperti gambar di bawah ini:
Di Excel, fungsi REPLACE dan SUBSTITUTE dapat menyelesaikan pekerjaan ini.
Fungsi REPLACE
REPLACE(sel,FIND("find_text",cell),find_text_length,"replace_text")
Fungsi SUBSTITUTE
PENGGANTI(sel,"temukan_teks",,"ganti_teks")
Dalam hal ini, silakan gunakan rumus di atas sebagai berikut:
=GANTI(B12,TEMUKAN(":",B12),1,":$")
Or
=SUBSTITUTE(B12,":",":$")
Rumus argumen dalam kasus ini adalah
Sel: B12,
Temukan teks: :,
Ganti_teks::$.
Temukan_teks_panjang:1
Tekan Enter untuk mendapatkan hasil penambahan pertama, lalu seret pegangan isi otomatis ke sel yang membutuhkan rumus ini.
Di bagian tutorial ini, ia memperkenalkan tiga metode berbeda (rumus, Temukan dan Ganti, VBA) untuk membantu Anda menambahkan karakter sebelum atau sesudah setiap kata dalam sel.
| Tambahkan karakter sebelum setiap kata |
 |
| Tambahkan karakter setelah setiap kata |
 |
6.1 Tambahkan karakter sebelum atau sesudah setiap kata dengan rumus
Fungsi SUBSTITUTE dapat digunakan untuk menyelesaikan pekerjaan ini, rumus umum sebagai berikut:
Tambahkan sebelum setiap kata
"karakter"&SUBSTITUTE(string, " "," karakter")
Tambahkan setelah setiap kata
PENGGANTI(string," ","karakter ")&"karakter"
Misalkan menambahkan "+" sebelum setiap kata dalam daftar B3:B6, gunakan rumus seperti ini:
="+"&GANTI(B3, " "," +")
Tekan Enter untuk mendapatkan hasil pertama, lalu seret pegangan pengisian otomatis ke bawah untuk mendapatkan semua hasil.
Misalkan menambahkan ($) setelah setiap kata dari daftar B11:B14, gunakan rumus di bawah ini:
=SUBSTITUTE(B11," ","($) ")&"($)"
Tekan Enter untuk mendapatkan hasil pertama, lalu seret pegangan pengisian otomatis ke bawah untuk mendapatkan semua hasil.
6.2 Tambahkan karakter sebelum atau sesudah setiap kata dengan Cari dan Ganti
Jika Anda ingin menambahkan karakter sebelum setiap kata tidak termasuk yang pertama, atau menambahkan karakter setelah setiap kata tidak termasuk yang terakhir seperti gambar di bawah ini, Excel built-in Cari dan Ganti utilitas dapat menangani pekerjaan ini.
Tambahkan sebelum setiap kata kecuali yang pertama
Tambahkan setelah setiap kata kecuali yang terakhir
1. Pilih sel yang ingin Anda tambahkan karakter sebelum atau sesudah setiap kata, lalu tekan Ctrl + H kunci untuk mengaktifkan Cari dan Ganti dialog.
2. Ketik spasi ke dalam Temukan apa kotak teks, lalu ketik karakter baru dengan spasi di Ganti dengan kotak teks, dan terakhir klik Ganti Semua .
| Operasi | Umum | Contoh |
| Tambahkan karakter sebelum setiap kata | Tambahkan karakter baru setelah spasi | Tambahkan (penjualan) sebelum setiap kata |
| Operasi | Umum | Contoh |
| Tambahkan karakter setelah setiap kata | Karakter baru diikuti dengan spasi | Tambahkan - setelah setiap kata |
Di sini menyediakan dua kode VBA untuk menambahkan karakter sebelum atau sesudah setiap kata, jalankan kode dengan mengikuti langkah-langkah di bawah ini.
1. tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul kosong baru.
3. Salin dan tempel kode VBA di bawah ini ke modul.
Tambahkan sebelum setiap kata
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 SubTambahkan setelah setiap kata
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. tekan F5 untuk menjalankan kode (atau Anda dapat mengklik Run tombol ), sebuah dialog muncul untuk meminta Anda memilih sel berkelanjutan untuk bekerja, klik OK.
5. Pada dialog pop-out kedua, ketik karakter yang ingin Anda tambahkan, klik OK.
Kemudian karakter akan ditambahkan di depan atau akhir setiap kata.
Terkadang, Anda mungkin ingin menambahkan teks di antara setiap karakter. Di sini, di bagian ini, ini mencantumkan dua skenario, satu adalah menambahkan teks di antara setiap karakter, yang lain adalah menambahkan spasi di antara setiap angka.
7.1 Tambahkan teks di antara setiap karakter/digit dengan fungsi Buatan Pengguna
Untuk menambahkan karakter di antara setiap karakter dalam string, fungsi Buatan Pengguna dapat membantu Anda.
Dalam hal ini, kami menambahkan spasi di antara setiap karakter.
1. tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul kosong baru.
3. Salin dan tempel kode VBA di bawah ini ke modul.
Tambahkan teks di antara karakter
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. Simpan kode dan tutup jendela untuk kembali ke lembar kerja, lalu ketik rumus di bawah ini ke dalam sel yang ingin Anda tempatkan hasil penambahannya.
=TambahkanTeks(B3)
B3 adalah sel yang berisi string yang ingin Anda tambahkan spasi antar karakter.
5. tekan Enter untuk mendapatkan hasil, seret pegangan isi otomatis ke sel untuk mendapatkan hasil lain sesuai kebutuhan.
|
Perhatian: 1） Dalam kode VBA, Anda dapat mengubah "" ke teks lain yang Anda butuhkan, seandainya mengubahnya menjadi "-" untuk menambahkan - antar karakter. 2） Kode akan menambahkan teks baru setelah setiap karakter. Anda dapat menghapus teks yang terakhir ditambahkan jika tidak perlu dengan menggunakan rumus di bawah ini. Untuk detail lebih lanjut tentang rumus, silakan kunjungi: Cara Menghapus Karakter N Pertama Atau Terakhir Dari Sel Atau String Di Excya? KIRI(sel,LEN(sel)-teks_panjang) |
7.2 Tambahkan spasi di antara setiap angka dengan rumus
Jika Anda ingin menambahkan spasi di antara digit string yang merupakan nilai numerik, Anda dapat menggunakan fungsi TEXT.
| Perhatian: jumlah rentang yang ingin Anda tambahkan spasi di antaranya harus dalam panjang yang sama, jika tidak, beberapa hasil mungkin salah. |
Misalkan di sini adalah daftar angka 8 digit dalam rentang B10:B12, silakan pilih sel yang akan menempatkan hasilnya, ketikkan rumus di bawah ini:
=TEXT(B10,"# # # # # # # #")
Tekan Enter tombol, lalu seret tuas IsiOtomatis ke sel lain.
Jika panjang bilangan adalah 5, rumusnya akan diubah menjadi: =TEXT(B10,"# # # # #").
Jika Anda ingin menambahkan karakter di antara setiap kata, misalkan menambahkan spasi di antara nama depan, tengah, dan belakang seperti gambar di bawah ini, Anda dapat menggunakan fungsi yang Ditentukan Pengguna.
1. tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul kosong baru.
3. Salin dan tempel kode VBA di bawah ini ke modul.
Tambahkan karakter di antara kata-kata
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. Simpan kode dan tutup jendela untuk kembali ke lembar kerja, lalu ketik rumus di bawah ini ke dalam sel yang ingin Anda tempatkan hasil penambahannya.
=TambahKarakter(B3)
B3 adalah sel berisi string yang ingin Anda tambahkan spasi di antara kata-kata.
5. tekan Enter untuk mendapatkan hasil, seret pegangan isi otomatis ke sel untuk mendapatkan hasil lain sesuai kebutuhan.
| Perhatian: dalam kode VBA, Anda dapat mengubah "" ke teks lain yang Anda butuhkan, seandainya mengubahnya menjadi "-" untuk menambahkan - di antara kata-kata. |
Untuk menambahkan karakter tertentu antara teks dan angka seperti gambar di bawah ini, di sini menyediakan dua metode.
9.1 Tambahkan karakter antara teks dan angka dengan rumus
Jika teks berada di depan angka, rumus umum adalah
TRIM(GANTI(string,MIN(TEMUKAN({1,2,3,4,5,6,7,8,9,0},string&"1234567890")),0,"-"))
Menggunakan kasus di atas sebagai contoh, silakan gunakan rumus seperti ini:
=TRIM(REPLACE(B4,MIN(FIND({1,2,3,4,5,6,7,8,9,0},B4&"1234567890")),0,"-"))
Tekan Enter untuk mendapatkan hasil pertama, lalu seret pegangan pengisian otomatis ke sel lain untuk mengisi rumus ini.
Jika angka ada di depan teks, rumus umum adalah
KIRI(string,SUM(LEN(string)-LEN(GANTI(string,{0,1,2,3,4,5,6,7,8,9},""))))&"."&KANAN (string,LEN(B13)- SUM(LEN(string)-LEN(SUBSTITUTE(string, {0,1,2,3,4,5,6,7,8,9}, ""))))
Menggunakan kasus di atas sebagai contoh, silakan gunakan rumus seperti ini:
=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}, ""))))
Tekan Enter untuk mendapatkan hasil pertama, lalu seret pegangan pengisian otomatis ke sel lain untuk mengisi rumus ini.
9.2 Tambahkan karakter antara teks dan angka dengan Flash Fill
Jika Anda menggunakan Excel 2013 atau versi yang lebih baru, Isi Flash juga dapat membantu Anda menambahkan karakter di antara teks dan angka.
Di sebelah data asli, ketik data baru yang telah ditambahkan karakter antara teks dan angka. Kemudian tekan tombol Enter untuk pergi ke sel berikutnya dari hasil pertama.
Dan tekan Ctrl + E tombol untuk mengaktifkan Isi Flash.
Perpanjangan
Tutorial ini juga mencantumkan beberapa skenario tentang menambahkan teks yang mungkin kita temui dalam kehidupan atau pekerjaan kita sehari-hari.
Terkadang, Anda perlu menambahkan pemisah seperti tanda hubung ke nomor seri telepon atau nomor sosial seperti gambar di bawah ini. Pengetikan manual akan dicoba jika ada ratusan angka yang perlu ditambahkan. Pada bagian ini, memperkenalkan tiga cara trik untuk menangani pekerjaan ini dengan cepat.
10.1 Tambahkan tanda hubung ke angka dengan rumus
Berikut adalah dua rumus yang dapat dengan cepat menambahkan tanda hubung ke angka. Untuk menambahkan tanda hubung setelah angka ketiga dan keenam di sel B3, gunakan rumus sebagai berikut:
Fungsi REPLACE
=GANTI(GANTI(B3,4,0,"-"),8,0,"-")
Fungsi TEXT
=TEXT(B3,"???-???-??? ")
Tekan Enter untuk mendapatkan hasil pertama, lalu seret pegangan pengisian otomatis ke sel lain.
|
Perhatian: Anda dapat mengubah argumen rumus di atas sesuai kebutuhan. Misalkan tambahkan "-" setelah digit kedua, keempat dan keenam, Anda dapat menggunakan rumus sebagai: =GANTI(GANTI(GANTI(B3,3,0,"-"),6,0,"-"),9,0,"-") =TEXT(B3,"??-??-??-??? ") |
10.2 Tambahkan tanda hubung ke angka dengan Format Sel
Untuk langsung menambahkan tanda hubung ke angka di sel asli, Anda dapat menggunakan Format Cells fitur.
1. Pilih nomor yang ingin Anda tambahkan tanda hubung, lalu tekan Ctrl + 1 kunci untuk mengaktifkan Format Cells dialog.
2. Dalam Format Cells dialog, di bawah Jumlah tab, pilih Kustom dari Kategori bagian.
3. Kemudian di sebelah kanan Tipe bagian, jenis ###-###-### ke dalam kotak teks, klik OK.
Sekarang nomor yang dipilih telah ditambahkan tanda hubung.
10.3 Tambahkan tanda hubung ke nomor telepon dengan Tambahkan Teks Kutools for Excel
Jika Anda memiliki Kutools untuk Excel diinstal di Excel, Tambahkan Teks fitur juga dapat membantu Anda.
1. Pilih sel yang berisi angka yang perlu ditambahkan tanda hubung, klik Kutools > Teks > Tambahkan teks.
2. Dalam Tambahkan Teks dialog, ketik tanda hubung atau pemisah lainnya ke dalam Teks kotak teks, pilih Menentukan pilihan, lalu ketik posisi yang ingin Anda tambahkan tanda hubung dan pisahkan dengan koma ke dalam kotak teks di bawah ini.
3. klik Ok atau Terapkan. Kemudian nomor yang dipilih telah ditambahkan tanda hubung.
Unduh gratis Tambahkan Teks untuk uji coba 30 hari.
Misalkan ada daftar angka dengan panjang yang berbeda, Anda ingin menambahkan nol di depan untuk membuatnya sama panjang dan terlihat rapi seperti gambar di bawah ini. Berikut adalah dua metode yang disediakan bagi Anda untuk menangani pekerjaan itu.
11.1 Tambahkan nol di depan ke sel dengan fitur Format Sel
Anda dapat menggunakan Format Cells fitur untuk mengatur format khusus untuk memperbaiki panjang sel dengan menambahkan nol di depan.
1. Pilih sel yang ingin Anda tambahkan nol di depan, lalu tekan Ctrl + 1 kunci untuk mengaktifkan Format Cells dialog.
2. Dalam Format Cells dialog, di bawah Jumlah tab, pilih Kustom dari Kategori bagian.
3. Kemudian di sebelah kanan Tipe bagian, jenis 0000 ke dalam kotak teks (untuk memperbaiki panjang angka menjadi 4-dight, ketik 0000, Anda dapat mengubahnya menjadi panjang lainnya, seperti 5-dight, ketik 00000), klik OK.
Kemudian angka-angka tersebut telah ditambahkan nol di depan dan dengan panjang yang sama.
| Perhatian: jika panjang data asli melebihi panjang yang ditetapkan, data asli akan ditampilkan tanpa awalan nol. |
11.2 Tambahkan nol di depan ke sel dengan rumus
Jika Anda tidak ingin mengubah data asli, Anda bisa menggunakan rumus untuk menambahkan angka nol di depan ke angka di lokasi lain.
Berikut adalah tiga fungsi yang dapat membantu Anda.
Fungsi TEKS Formula1
TEKS(nomor,"00...")
Formula2 fungsi KANAN
KANAN("00..."&bilangan,panjang)
Fungsi BASE Formula3
DASAR (angka, 10, panjang)
Jumlah nol dalam rumus sama dengan panjang bilangan.
Di sini Anda menetapkan 4 digit sebagai panjang angka, silakan gunakan rumus seperti di bawah ini:
= TEXT (B10, "0000")
=KANAN("0000"&B10,4)
=DASAR(B10,10,4)
Tekan Enter tombol dan seret pegangan pengisian otomatis ke bawah.
| Perhatian: jika panjang data asli melebihi panjang yang ditetapkan, data asli akan ditampilkan tanpa awalan nol. |
Jika Anda ingin menambahkan trailing zeros ke angka untuk membuat panjang tetap, misalkan menambahkan trailing zero ke angka di sel B3:B7 untuk membuatnya dalam panjang 5 digit seperti gambar di bawah ini, Anda dapat menerapkan rumus untuk menangani pekerjaan .
fungsi REPT
nomor&REPT("0",panjang-LEN(angka))
Dalam rumus, argumen "angka" mewakili nomor atau referensi sel yang ingin Anda tambahkan di belakang nol, dan argumen "panjang" adalah panjang tetap yang Anda inginkan.
Silakan gunakan rumus seperti ini:
=B3&REPT("0",5-LEN(B3))
Tekan Enter kunci dan seret pegangan pengisian otomatis ke sel yang membutuhkan rumus ini.
Jika Anda ingin mengekspor atau menyalin data dari lembar kerja ke notepad, teks dapat ditempatkan dengan tidak rapi karena nilai sel memiliki panjang yang berbeda, untuk membuatnya terlihat rapi seperti gambar di bawah ini, Anda perlu menambahkan spasi tambahan.
Untuk menyelesaikan pekerjaan ini, fungsi REPT dapat membantu Anda.
KIRI(teks & REPT(" ",max_length),max_length)
Dalam rumus, argumen "teks" adalah teks atau referensi sel yang ingin Anda tambahkan spasi, dan argumen "max_length" adalah panjang tetap yang Anda inginkan untuk membuat teks, yang harus lebih panjang dari atau sama dengan panjang maksimum teks yang digunakan.
Untuk menambahkan spasi pada teks di sel B3:C6, gunakan rumus di bawah ini:
=KIRI(B3 & REPT(" ",10),10)
Tekan Enter kunci, lalu seret pegangan pengisian otomatis ke semua sel yang membutuhkan rumus ini.
Dalam hal ini, karena teks terpanjang yang digunakan adalah 9 karakter, kami menggunakan 10 di sini.
Terkadang, Anda perlu menambahkan tanda kutip atau tanda kurung di sekitar teks atau angka dalam sel seperti gambar di bawah ini, kecuali pengetikan manual satu per satu, berikut adalah dua metode di Excel yang dapat dengan cepat menyelesaikannya.
14.1 Tambahkan tanda kutip/kurung di sekitar dengan Format Sel
Jika Anda ingin menambahkan tanda kutip di sekitar teks dalam data asli, fitur Format Sel di Excel dapat membantu Anda.
1. Pilih sel yang ingin Anda tambahkan tanda kutip, tekan Ctrl + 1 kunci untuk mengaktifkan Format Cells dialog.
2. Dalam Format Cells dialog, di bawah Jumlah tab, pilih Kustom dari daftar Kategori, lalu pergi ke bagian kanan untuk mengetik "''" @ "''" ke dalam Tipe kolom tulisan. Klik OK.
Teks dalam sel yang dipilih telah ditambahkan dengan tanda kutip.
Jika Anda ingin menambahkan tanda kurung di sekitar teks, di kotak teks Type dari dialog Format Cells, gunakan (@).
14.2 Tambahkan tanda kutip/kurung dengan rumus
Rumus juga dapat digunakan untuk menambahkan tanda kutip ke teks di sel lain.
Menggunakan & konektor
""""&teks&""""
Or
karakter(34)&teks&karakter(34)
Dalam hal ini, teks di sel B11, gunakan rumus sebagai berikut:
=""""&B11&""""
Or
=char(34)&B11&char(34)
Tekan Enter tombol dan seret pegangan pengisian otomatis ke bawah untuk mengisi sel.
Jika Anda ingin menambahkan tanda kurung di sekitar teks, gunakan rumus sebagai "("&teks&")".
14.3 Tambahkan tanda kutip/kurung di sekitar VBA
Jika Anda memerlukan kode VBA untuk menyelesaikan pekerjaan ini, berikut adalah VBA.
1. tekan lain + F11 kunci untuk mengaktifkan Microsoft Visual Basic untuk Aplikasi jendela.
2. Kemudian di jendela yang muncul, klik Menyisipkan > Modul untuk menyisipkan modul baru yang kosong.
3. Salin dan tempel kode VBA di bawah ini ke modul baru.
VBA: Tambahkan tanda kutip di sekitar teks
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| Perhatian: dalam skrip kode, """" & Rng.Nilai & """" menunjukkan untuk menambahkan "" di sekitar teks, jika Anda ingin menambahkan tanda kurung () di sekitar teks, ubah ke "(" & Rng.Nilai & ")". |
4. tekan F5 kunci atau klik Run tombol di jendela untuk mengaktifkan kode VBA. Dialog muncul untuk memilih sel untuk menambahkan tanda kutip.
5. klik OK. Sekarang tanda kutip telah ditambahkan di sekitar teks di sel yang dipilih.
Terkadang, untuk membantu pengguna lain lebih memahami data, Anda mungkin perlu menambahkan teks dalam hasil rumus seperti gambar di bawah ini. Untuk menyertakan teks dalam rumus, Anda bisa menggunakan tanda kutip yang mengelilingi teks dan menambahkan konektor & untuk menggabungkan teks dan rumus bersama-sama.
Kasus 1 tambahkan teks sebelum rumus
"Hari ini adalah "&TEXT(HARI INI(),"dddd, mmmm dd.")
Kasus 2 tambahkan teks setelahnya rumus
TEXT(NOW(),"HH:MM:SS")&" adalah waktu saat ini."
Kasus 3 menambahkan teks di dua sisi rumus
"Hari ini terjual "&data&" kg."
Lebih Banyak Tutorial Excel: |
|
Gabungkan Beberapa Buku Kerja/Lembar Kerja Menjadi Satu Pisahkan Teks, Angka, Dan Sel Tanggal (Pisahkan Menjadi Beberapa Kolom) Gabungkan Isi Beberapa Sel Tanpa Kehilangan Data Di Excel Bandingkan Dua Kolom Untuk Kecocokan Dan Perbedaan Di Excel |
Alat Produktivitas Kantor Terbaik
Kutools for Excel Memecahkan Sebagian Besar Masalah Anda, dan Meningkatkan Produktivitas Anda hingga 80%
- Bilah Formula Super (dengan mudah mengedit beberapa baris teks dan rumus); Membaca Tata Letak (membaca dan mengedit sel dalam jumlah besar dengan mudah); Tempel ke Rentang yang Difilter...
- Gabungkan Sel / Baris / Kolom dan Menyimpan Data; Pisahkan Konten Sel; Gabungkan Baris Duplikat dan Jumlah / Rata-rata... Mencegah Sel Duplikat; Bandingkan Rentang...
- Pilih Duplikat atau Unik Baris; Pilih Baris Kosong (semua sel kosong); Temukan Super dan Temukan Fuzzy di Banyak Buku Kerja; Pilih Acak ...
- Salinan Tepat Beberapa Sel tanpa mengubah referensi rumus; Buat Referensi Otomatis ke Beberapa Lembar; Sisipkan Poin, Kotak Centang, dan lainnya ...
- Favorit dan Sisipkan Rumus dengan Cepat, Rentang, Grafik dan Gambar; Enkripsi Sel dengan kata sandi; Buat Milis dan mengirim email ...
- Ekstrak Teks, Tambahkan Teks, Hapus berdasarkan Posisi, Hapus Space; Membuat dan Mencetak Subtotal Paging; Konversi Konten Antar Sel dan Komentar...
- Filter Super (simpan dan terapkan skema filter ke sheet lain); Penyortiran Lanjutan menurut bulan / minggu / hari, frekuensi dan lainnya; Filter Khusus dengan huruf tebal, miring ...
- Gabungkan Workbooks dan WorkSheets; Gabungkan Tabel berdasarkan kolom kunci; Pisahkan Data menjadi Beberapa Lembar; Konversi Batch xls, xlsx dan PDF...
- Pengelompokan Tabel Pivot menurut nomor minggu, hari dalam seminggu dan banyak lagi ... Tampilkan Sel yang Tidak Terkunci dan Terkunci dengan warna berbeda; Sorot Sel Yang Memiliki Rumus / Nama...
- Aktifkan pengeditan dan pembacaan tab di Word, Excel, PowerPoint, Publisher, Access, Visio, dan Project.
- Buka dan buat banyak dokumen di tab baru di jendela yang sama, bukan di jendela baru.
- Meningkatkan produktivitas Anda sebesar 50%, dan mengurangi ratusan klik mouse untuk Anda setiap hari!
