Cara Mengeluarkan Nombor Dalam Excel Dari Kiri: Mudah!

Ada kalanya anda bekerja dengan data yang tidak diformat mengikut cara yang anda perlukan. Sebagai contoh, anda mungkin mempunyai gabungan nombor dan teks dalam sel, dan anda perlu membersihkan data ini.

Terdapat beberapa teknik untuk mengalih keluar nombor dalam Excel dari kiri teks. Sesetengah kaedah berfungsi apabila data mempunyai corak yang konsisten. VBA atau formula yang lebih kompleks berfungsi pada data yang kurang berstruktur.

Artikel ini menunjukkan tujuh kaedah untuk memadam nombor di sebelah kiri data teks. Pada akhirnya, anda akan mempunyai pemahaman yang komprehensif tentang teknik yang berbeza dan bila untuk menggunakannya.

Mari pergi!

Isi kandungan

• Cara Menggunakan Isian Kilat untuk Mengeluarkan Nombor dalam Excel Dari Kiri
• Cara Menggunakan Ciri Teks ke Lajur untuk Mengalih Keluar Nombor
• 5 Fungsi Teks untuk Mengeluarkan Nombor dalam Excel Dari Kiri
  • 1. Fungsi MID
  • 2. Gabungkan dengan Fungsi LEN
  • 3. Menggabungkan Fungsi KIRI dan GANTI
  • 4. Menggunakan Fungsi KANAN dan LEN
  • 5. Menggunakan Fungsi REPLACE
• Cara Menggunakan Power Query untuk Mengalih Keluar Nombor Dari Kiri
• 3 Teknik Lanjutan Tidak Kira Berapa Banyak Watak
  • 1. Gabungkan Lajur Pembantu Dengan Formula untuk Mengeluarkan Aksara Dari Kiri Sel
  • 2. Cipta Makro VBA untuk Mengeluarkan Nombor
  • 3. Buat Fungsi Ditentukan Pengguna
• Fikiran Akhir

Cara Menggunakan Isian Kilat untuk Mengeluarkan Nombor dalam Excel Dari Kiri

Isian Flash ialah ciri Excel yang boleh mengisi corak secara automatik untuk anda. Ini adalah cara yang cepat dan mudah untuk mengalih keluar nombor dari sebelah kiri sel.

Kaedah ini mungkin tidak selalu berfungsi dengan sempurna, tetapi ia boleh membantu untuk kes mudah. Ikuti langkah ini untuk menggunakan Isian Kilat:

1. Dalam lajur kosong bersebelahan dengan data anda, taipkan hasil yang diingini tanpa nombor (cth, jika "1234apple" berada dalam sel A1, taip "epal" dalam sel B1).

2. Pilih sel dengan data baharu (B1 dalam contoh kami) dan klik tab Data pada reben.

3. Klik butang Isi Kilat dalam bahagian Alat Data.

4. Excel akan menggunakan corak secara automatik untuk mengalih keluar nombor dari sebelah kiri sel bersebelahan.

Ini ialah lokasi butang Isi Kilat dalam tab Data.

Jika anda lebih suka menggunakan pintasan, tekan CTRL + E pada Windows atau CMD + E pada Mac.

Isian Flash ialah ciri yang berguna, tetapi seperti kebanyakan perkara dalam Excel, terdapat pelbagai cara untuk menyelesaikan tugas yang sama.

Dalam bahagian seterusnya, kami melihat bagaimana anda boleh melakukan perkara yang sama menggunakan ciri "Teks ke Lajur".

Cara Menggunakan Ciri Teks ke Lajur untuk Mengalih Keluar Nombor

Anda boleh menggunakan ciri "Teks ke Lajur" dalam Excel untuk memisahkan nombor dari sebelah kiri sel. Walau bagaimanapun, kaedah ini menganggap bahawa terdapat corak atau pembatas yang konsisten antara nombor dan teks dalam sel.

Mari kita bekerja dengan satu set data yang menggunakan sengkang untuk memisahkan nombor daripada huruf seperti ini:

• 1234-epal

• 4321-oren

• 9876-pir

Ini adalah sebelum dan selepas yang ingin kami capai:

Berikut ialah panduan langkah demi langkah dengan set data sampel ini:

1. Pilih lajur A.

2. Pergi ke tab Data dan klik pada wizard Teks ke Lajur.

3. Pilih pilihan Terhad dan klik Seterusnya.

4. Nyahtanda semua pembatas (tab mungkin digunakan secara lalai).

5. Tandai pembatas Lain dan taip "-" ke dalam kotak input.

6. Pilih pembatas yang sesuai (ruang, koma, dsb.).

7. Klik Seterusnya dan Selesai.

Data kini akan dipisahkan kepada dua lajur. Aksara yang dialih keluar berada dalam lajur pertama manakala selebihnya teks berada dalam lajur kedua. Gambar ini menunjukkan cara menetapkan pembatas:

Seterusnya, mari kita lihat lima fungsi teks untuk mengalih keluar nombor dalam Excel.

5 Fungsi Teks untuk Mengeluarkan Nombor dalam Excel Dari Kiri

Jika anda tahu berapa banyak aksara yang membentuk digit, maka anda boleh menggunakan fungsi teks untuk mengalih keluarnya.

Katakan sel A1 mengandungi rentetan " 1234apple ". Empat aksara pertama ialah nombor yang ingin kita alih keluar.

Terdapat beberapa fungsi yang boleh digunakan sendiri atau digabungkan untuk mengalih keluar aksara yang merupakan nilai angka.

1. Fungsi MID

Anda boleh menggunakan fungsi MID untuk mengekstrak bahagian rentetan yang bermula daripada aksara ke-5.

Fungsi MID mengekstrak bahagian tertentu rentetan teks, memandangkan kedudukan permulaan dan bilangan aksara untuk diekstrak. Sintaks untuk fungsi MID ialah:

MID(teks, nombor_mula, angka_huruf)

• teks: Rentetan teks yang mengandungi aksara yang anda ingin ekstrak.

• start_num: Kedudukan aksara pertama yang anda ingin ekstrak.

• num_chars: Bilangan aksara untuk diekstrak daripada rentetan teks.

Untuk mengalih keluar nombor di sebelah kiri, ikuti langkah berikut:

1. pilih sel B2 untuk mengandungi fungsi dan keputusan.

2. Taip formula: MID(A1, 5, 7)

Formula di atas akan mengalih keluar aksara daripada slot ke-5 dalam rentetan.

2. Gabungkan dengan Fungsi LEN

Satu kelemahan dengan kaedah ini ialah rentetan teks anda mungkin tidak selalu sama panjang.

Anda boleh menggunakan fungsi LEN untuk mengira panjang secara dinamik. Fungsi ini mengembalikan panjang rentetan teks.

Anda boleh mengubah suai formula untuk menggantikan parameter ketiga dengan fungsi LEN seperti ini:

=MID(A1, 5, LEN(A1)-4)

Anda boleh menyalin formula ke bawah dengan menggunakan pemegang isian. Gambar ini menunjukkan formula di atas yang digunakan:

3. Menggabungkan Fungsi KIRI dan GANTI

Fungsi LEFT mengembalikan aksara paling kiri daripada rentetan teks. Sebagai contoh, LEFT("1234apple", 4) akan mengembalikan nilai "1234".

Ini berguna apabila anda ingin mengekstrak nombor. Tetapi bagaimana jika anda mahu mengeluarkannya?

Anda boleh menggabungkan LEFT dengan fungsi SUBSTITUTE untuk menggantikan nombor dengan rentetan kosong. Ini adalah format untuk contoh kami:

=GANTI(A1, KIRI(A1, 4), “”)

4. Menggunakan Fungsi KANAN dan LEN

Fungsi RIGHT mengembalikan aksara paling kanan daripada rentetan teks. Sintaks untuk fungsi RIGHT ialah:

RIGHT(teks, [num_chars])

• teks: Rentetan teks yang mengandungi aksara yang anda ingin ekstrak.

• num_chars: (Pilihan) Bilangan aksara untuk diekstrak dari sebelah kanan rentetan teks. Jika ditinggalkan, ia lalai kepada 1.

Sebagai contoh, RIGHT("1234apple", 5) akan mengembalikan nilai "epal".

Anda juga boleh menggabungkan fungsi RIGHT dan LEN untuk mengelakkan pengekodan keras bilangan aksara alfa. Ini mengandaikan bahawa anda mengetahui bilangan aksara angka.

Format untuk contoh kami ialah: =RIGHT(A1, LEN(A1)-4)

5. Menggunakan Fungsi REPLACE

Fungsi REPLACE boleh digunakan untuk menggantikan bilangan aksara tertentu dari kiri dalam sel. Ini adalah sintaksnya:

REPLACE(teks_lama, angka_mula, angka_huruf, teks_baru)

• old_text: rentetan teks asal yang anda ingin ubah suai.

• start_num: kedudukan dalam old_text di mana anda mahu mula menggantikan aksara.

• num_chars: bilangan aksara dalam teks lama yang anda mahu gantikan.

• new_text: rentetan teks yang anda mahu gantikan dengan aksara yang ditentukan.

Formula berikut adalah untuk data sampel kami:

=GANTIKAN(A1, 1, 4, “”)

Dan itulah yang terakhir daripada lima fungsi teks kami untuk mengalih keluar aksara dari kiri dalam Excel.

Dalam kebanyakan kes, tujuh kaedah yang telah kami bincangkan setakat ini sepatutnya mencukupi, tetapi jika anda mahukan lebih kuasa, Power Query ialah jawapannya, dan itulah yang akan kita bincangkan dalam bahagian seterusnya!

Cara Menggunakan Power Query untuk Mengalih Keluar Nombor Dari Kiri

Power Query ialah alat transformasi data yang berkuasa yang tersedia dalam Excel. Kaedah yang ditunjukkan dalam bahagian ini menganggap bahawa terdapat pembatas antara nombor dan teks dalam sel.

Langkah pertama ialah mengubah data sasaran ke dalam jadual:

1. Pergi ke tab Data.

2. Pilih julat sel.

3. Pilih "Dari Jadual/Julat" daripada bahagian Dapatkan Data dalam reben.

Apabila Editor Pertanyaan Kuasa dibuka, ikuti langkah berikut:

1. Pilih lajur sasaran.

2. Pergi ke tab "Transformasi".

3. Klik pada "Ekstrak" dalam reben, dan pilih "Teks Selepas Pembatas."

4. Dalam kotak dialog "Teks Selepas Pembatas", masukkan sempang (-).

5. Klik "OK" untuk menerima butiran.

Lajur yang diubah kini akan mempunyai nombor yang dialih keluar dari sebelah kiri teks.

Klik "Tutup & Muatkan" untuk memuatkan semula data yang diubah ke dalam lembaran kerja anda.

Jika anda baru menggunakan alat data ini, lihat tutorial kami tentang cara menggunakan Power Query dalam Excel .

Jika anda ingin mendalami penggunaan jadual dalam Power Query, video ini menelusuri beberapa contoh yang lebih kompleks:

3 Teknik Lanjutan Tidak Kira Berapa Banyak Watak

Kelemahan utama kaedah setakat ini ialah nombor dalam semua sel mesti mengikut corak yang sama atau menjadi bilangan aksara tertentu.

Bagaimana jika anda ingin memadam aksara tanpa corak yang jelas dalam data asal? Teknik dalam bahagian ini adalah untuk pengguna Excel pertengahan.

1. Gabungkan Lajur Pembantu Dengan Formula untuk Mengeluarkan Aksara Dari Kiri Sel

Anda boleh menggabungkan fungsi dengan lajur pembantu apabila anda tidak mengetahui jumlah panjang nombor. Kaedah ini memerlukan proses dua langkah:

• gunakan formula untuk mencipta versi diubah suai sementara bagi teks asal

• gunakan formula lain untuk mengeluarkan nombor berdasarkan teks yang diubah suai.

Langkah pertama ialah menggunakan formula berikut untuk menggantikan semua nombor dengan aksara tertentu (cth, “|”):

=GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIKAN(GANTIAN(A1, “0”,”|”), “1”, “|”), “2”, “|”), “3”, “|”), “4”, “|”), “5”, “|”), “6”, “|”), “7”, “|”), “8”, “ |”), “9”, “|”)

Langkah kedua ialah menggunakan formula berikut dalam lajur pembantu yang lain (cth lajur C):

=TRIM(KANAN(GANTIKAN(B1, “|”, REPT(” “, LEN(B1))), LEN(B1)))

Ini ialah sebelum dan selepas dengan formula dalam lajur B dan C:

Apabila anda mula menggabungkan fungsi seperti ini, anda boleh menghadapi masalah dengan memilih pilihan yang salah dalam formula anda. Artikel kami tentang cara mencari rujukan bulat dalam Excel akan membantu anda menyelesaikan masalah ralat biasa.

Anda juga mungkin mahu berkongsi hamparan yang mengandungi lajur pembantu. Adalah penting bahawa rakan sekerja anda tidak memadam atau meminda formula dalam hasil perantaraan. Artikel kami tentang cara mengunci lajur dalam Excel akan membantu anda menghasilkan fungsi tersebut.

2. Cipta Makro VBA untuk Mengeluarkan Nombor

VBA ialah bahasa skrip serba boleh yang membolehkan anda mengautomasikan tugas dan mencipta penyelesaian tersuai dalam Excel.

Sebagai contoh, anda boleh menulis makro yang mengalih keluar nombor dari kiri semua sel dalam lajur atau julat tertentu.

Untuk berbuat demikian, ikuti langkah berikut:

1. Tekan ALT + F11 untuk membuka editor VBA.

2. Klik Sisipkan > Modul untuk mencipta modul baharu.

3. Salin dan tampal kod berikut untuk mengalih keluar nombor sahaja dari sebelah kiri lajur A:

Sub RemoveLeftNumbers()
    Dim ws As Worksheet, cell As Range, i As Integer
    
    ' Set the active worksheet as the target worksheet
    Set ws = ActiveSheet

    ' Loop through all the cells in column A
    For Each cell In ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
        ' Remove numbers from the left of each cell
        For i = 1 To Len(cell.Value)
            If Not IsNumeric(Mid(cell.Value, i, 1)) Then
                Exit For
            End If
        Next i
        cell.Value = Mid(cell.Value, i)
    Next cell
End Sub

Langkah terakhir ialah menjalankan makro. Klik segi tiga hijau atau item menu "run".

3. Buat Fungsi Ditentukan Pengguna

Anda juga boleh mencipta fungsi yang ditentukan pengguna dengan VBA yang boleh anda gunakan dalam formula anda.

Untuk berbuat demikian, ikuti langkah berikut:

1. Tekan ALT + F11 untuk membuka editor VBA.

2. Klik Sisipkan > Modul untuk mencipta modul baharu.

3. Salin dan tampal kod berikut:

Public Function RemoveNumbersFromLeft (text As String) As String
    Dim i As Integer
    For i = 1 To Len(text)
        If Not IsNumeric(Mid(text, i, 1)) Then
            Exit For
        End If
    Next i
    RemoveNumbersFromLeft = Mid(text, i)
End Function

Sekarang, kembali ke helaian Excel anda dan gunakan fungsi awam dalam formula.

Sebagai contoh, formula ini berfungsi dengan menerima sel A1:

=RemoveNumbersFromLeft(A1)

Fikiran Akhir

Dalam artikel ini, kami telah meneroka berbilang kaedah untuk mengalih keluar nombor dari sebelah kiri rentetan teks dalam Excel. Setiap teknik mempunyai kelebihan dan batasan tersendiri.

Tiada kaedah tunggal yang sesuai untuk setiap situasi, tetapi pasti ada pendekatan yang paling sesuai dengan keperluan anda. Dengan latihan, anda akan menjadi mahir dalam menggunakan teknik ini untuk menangani sebarang cabaran pemformatan data yang datang kepada anda.

Jadi begitulah, selok belok mengalih keluar digit yang tidak diingini dari kiri dalam Excel. Ia mungkin kelihatan agak rumit pada mulanya, tetapi dengan teknik yang betul, anda akan mengatasinya dalam masa yang singkat! Selamat mencari nombor!