Excel öğreticisi - belirli bir konuma göre metin veya sayı ayıklayın
Çoğu durumda, adresten ili çıkarmak, bir cümleden e-posta adresini çıkarmak, bir konuşmadan kurye fatura numarasını çıkarmak gibi yalnızca bir hücredeki bir cümle veya metin dizesinden yararlı içeriği çıkarmanız gerekebilir. ve benzeri. Bu öğretici, hücredeki belirli bir konuma çıkarmayı daraltır ve Excel'de belirli bir konuma göre bir hücreden metin veya sayıların çıkarılmasına yardımcı olacak farklı yöntemler toplar.
İçindekiler: [ Saklamak ]
Metni konuma göre ayıkla
Bu bölüm, bir hücreden bir metnin çıkarılabileceği ortak konumları toplar ve bunları adım adım işlemek için karşılık gelen yöntemler sağlar. Daha fazla ayrıntı için göz atabilirsiniz.
1. Soldan veya sağdan karakter sayısını çıkarın
Bir dizenin sol veya sağ tarafından karakter sayısını çıkarmak için aşağıdaki yöntemlerden birini deneyebilirsiniz.
1.1 Formüllerle ilk veya son N karakteri ayıklayın
Aşağıdaki ekran görüntüsünde gösterildiği gibi B sütununda bir metin dizesi listeniz olduğunu varsayalım, her dizeden ilk 2 karakteri ve son 2 karakteri çıkarmak için aşağıdaki formülleri uygulayabilirsiniz.
Bir metin dizesinden ilk N karakteri çıkarın
SOL işlevi, Excel'deki bir metin dizesinden ilk N karakteri kolayca çıkarmaya yardımcı olabilir.
Genel formül
=LEFT(text_string,[num_chars])
Argümanlar
Şimdi, B sütunundaki hücrelerden ilk 2 karakteri çıkarmak için bu formülü uygulayabilirsiniz.
1. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet İlk sonucu almak için anahtar. Sonuç hücresini seçin ve formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LEFT(B5,2)
Şimdi B2:B5 aralığının her hücresindeki ilk 10 karakter ayıklandı.
Bir metin dizesinden son N karakteri çıkarın
Burada, Excel'deki bir metin dizesinden son N karakteri çıkarmak için SAĞ işlevini uyguluyoruz.
Genel formül
=RIGHT(text_string,[num_chars])
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Ardından bu sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=RIGHT(B5,2)
1.2 Harika bir araçla ilk veya son N karakteri ayıklayın
Yukarıdaki formüller basit olsa da, uzun bir metin dizesi listesinden ilk veya son n karakteri çıkarmak için, yine de Otomatik Doldurma Tutamağını yukarıdan aşağıya doğru sürüklemeniz gerekir ki bu biraz zaman alabilir. İşte tavsiye Kutools for Excel'S Metni Çıkar Toplu olarak bir metin dizesi listesinden ilk veya son N karakteri çıkarmaya yardımcı olan yardımcı program.
1. Metni çıkarmak istediğiniz metin dizesi listesini önceden seçin ve Kutools > Metin > Metni Çıkar.
2. Açılırken Metni Çıkar iletişim kutusu, aşağıdaki gibi yapılandırmanız gerekir.
not: Metin dizesi değiştiğinde sonucu dinamik hale getirmek için, Formül olarak ekle kutu.
3. Bir sonraki açılımda Metni Çıkar iletişim kutusunda, çıkarılan karakterlerin çıktısını almak için bir hücre seçin ve ardından OK.
Ardından, belirtilen ilk veya son N karakter, seçilen hücrelerden toplu olarak çıkarılır.
Bu özellik hakkında daha fazla bilgi edinmek için tıklayın.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
2. Belirli bir karakter/kelimeden önce veya sonra metni çıkarın
Belirli bir karakter veya kelimeden önce veya sonra metin çıkarmak için bu bölümdeki farklı senaryolar ihtiyaçlarınızı karşılayacaktır.
2.1 İlk sınırlayıcıdan (bir karakter) önce veya sonra metni ayıklayın
Aşağıdaki ekran görüntüsünde gösterildiği gibi, B4:B10 aralığındaki her hücreden ilk sınırlayıcıdan önce veya sonra metin çıkarmak için aşağıdaki yöntemlerden birini uygulayabilirsiniz.
2.1.1 İlk sınırlayıcıdan önceki metni formülle ayıklayın
SOL ve BUL işlevlerine dayalı bir formül uygulamak, bir hücreden ilk sınırlayıcıdan önceki metni çıkarmaya yardımcı olabilir. Bunu yapmak için aşağıdaki adımları takip edebilirsiniz.
Genel formül
=LEFT(text_string,FIND("delimiter",text_string,1)-1)
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya içine girin ve Keşfet İlk sonucu almak için anahtar. İlk sonuç hücresini seçin ve metinleri diğer hücrelerin ilk sınırlayıcısından önce almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LEFT(B5,FIND("-",B5,1)-1)
2.1.2 Formül ile ilk sınırlayıcıdan sonra metni ayıklayın
Aşağıdaki formül, Excel'deki bir hücreden ilk sınırlayıcıdan sonraki metni çıkarmaya yardımcı olur.
Genel formül
=MID(text_string,FIND("delimiter",text_string)+1,LEN(text_string))
Argümanlar
=MID(B5,FIND("-",B5)+1,LEN(B5))
2.1.3 Harika bir araçla ilk sınırlayıcıdan önce veya sonra metni ayıklayın
Burada şiddetle tavsiye edilir Metni Çıkar yarar Excel için Kutools. Bu özellik ile, bir dizi hücreden ilk sınırlayıcıdan önce veya sonra metinleri toplu olarak kolayca ayıklayabilirsiniz.
1. Metni çıkarmak istediğiniz hücre aralığını seçin ve ardından Kutools > Metin > Metni Çıkar.
2. içinde Metni Çıkar iletişim kutusu, aşağıdaki gibi yapılandırmanız gerekir.
Not: Metin dizesi değiştiğinde sonucu dinamik hale getirmek için Formül olarak ekle kutusunu işaretleyebilirsiniz.
3. Sonra başka Metni Çıkar iletişim kutusu açılır, sonuçların çıktısını almak için bir hücre seçin ve Tamam.
Ardından, ilk sınırlayıcıdan önceki veya sonraki metinler, seçili hücrelerden bir kerede ayıklanır.
Bu özellik hakkında daha fazla bilgi için lütfen şu adresi ziyaret edin: Excel'deki Hücrelerden Belirli Metni Hızla Çıkarın.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
2.2 Son sınırlayıcıdan (bir karakter) önce veya sonra metni ayıklayın
Yukarıdaki adımlarda, bir hücreden ilk sınırlayıcıdan önce veya sonra metin çıkarma yöntemlerini öğrendik. Aşağıdaki ekran görüntüsünde gösterildiği gibi, bu bölüm size bir hücreden son sınırlayıcıdan önce veya sonra metin çıkarmak için iki formül gösterecektir. Bunu yapmak için aşağıdaki adımları takip edebilirsiniz.
2.2.1 Son sınırlayıcıdan önceki metni formülle ayıklayın
Bir hücreden son sınırlayıcıdan önceki metni çıkarmak için, SOL işlevinin içindeki ARAMA, UZUNLUK ve DEĞİŞTİR işlevlerini kullanabilirsiniz.
Genel formül
=LEFT(text_string,SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter",""))))-1)
Argümanlar
Bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve aynı sütundaki diğer metin dizelerinden metinleri çıkarmak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)
2.2.2 Son sınırlayıcıdan sonra metni formülle ayıklayın
Bir hücreden son sınırlayıcıdan önceki metni çıkardıktan sonra, son sınırlayıcıdan sonraki metni ihtiyaç duyduğunuz şekilde çıkarmak için aşağıdaki formülü uygulayabilirsiniz.
Genel formül
=RIGHT(text_string,LEN(text_string)-SEARCH("#",SUBSTITUTE(text_string,"delimiter","#",LEN(text_string)-LEN(SUBSTITUTE(text_string,"delimiter","")))))
Argümanlar
Bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve aynı sütundaki diğer metin dizelerinden metinleri çıkarmak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))
2.3 n. karakterden sonra metni ayıklayın
Aşağıdaki örneğe bakın, B4:B10 aralığında bir metin dizesi listesi var, her hücreden üçüncü karakterden sonra metni çıkarmak için, MID işlevine ve UZUNLUK işlevine dayalı bir formül uygulayabilirsiniz.
Genel formül
=MID(text_string,nth_char+1,LEN(text_string))
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya içine girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=MID(B5,3+1,LEN(B5))
2.4 Bir metin dizesinden n. sözcüğü çıkarın
Aşağıdaki ekran görüntüsünde gösterildiği gibi bir metin dizesi listeniz olduğunu ve metin dizesinden yalnızca n. sözcüğü çıkarmak istediğinizi varsayalım, bu bölüm bunu yapmanız için üç yöntem sunar.
2.4.1 N'inci sözcüğü formülle ayıklayın
Bir hücredeki bir metin dizesinden n'inci sözcüğü çıkarmak için TRIM, MID, SUBSTITUTE, REPT ve UZUNLUK işlevlerini birleştirebilirsiniz.
Genel formül
=TRIM(MID(SUBSTITUTE(text_string," ",REPT(" ",LEN((text_string))), (N-1)*LEN((text_string)+1, LEN((text_string)))
Argümanlar
Bu durumda, B5:B10 aralığı metin dizelerini içerir, D5:D10, n'inci kelimeyi temsil eden sayıları içerir, hadi n'inci kelimeyi metin dizesinden çıkarmak için bu formülü uygulayalım.
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya içine girin ve Keşfet İlk sonucu almak için anahtar. Bu sonuç hücresini seçin ve diğer hücrelerin n'inci kelimesini almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))
Not: Formüldeki n'inci sayıyı doğrudan aşağıdaki gibi yazabilirsiniz.
=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))
2.4.2 Kullanıcı tanımlı işlevle n. sözcüğü çıkarın
Yukarıdaki formülün yanı sıra, Excel'deki bir hücreden n'inci sözcüğü çıkarmak için kullanıcı tanımlı bir işlev de uygulayabilirsiniz.
1. Tuşuna basın. Ara Toplam + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. içinde Uygulamalar için Microsoft Visual Basic Pencere, tıklayın Ekle > Modül, ve ardından aşağıdaki VBA'yı Kod penceresine kopyalayın.
VBA kodu: Bir hücredeki metin dizesinden n. sözcüğü çıkarın
Function ExtractTheNthWord(Source As String, Position As Integer)
'Update by Extendoffice 20211202
Dim arr() As String
arr = VBA.Split(Source, " ")
xCount = UBound(arr)
If xCount < 1 Or (Position - 1) > xCount Or Position < 0 Then
FindWord = ""
Else
FindWord = arr(Position - 1)
End If
End Function
3. Tuşuna basın. Ara Toplam + Q kapatmak için anahtarlar Uygulamalar için Microsoft Visual Basic pencere.
4. n'inci sözcüğü çıkarmak istediğiniz metin dizelerini içeren çalışma sayfasına geri dönün. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya içine girin ve Keşfet n. kelimeyi almak için anahtar.
=FindWord(B5,D5)
Or
=FindWord(B5,2)
Not: formülde D5, n'inci kelimeyi temsil eden bir sayı içeren hücredir. Alternatif olarak, hücre referansını doğrudan bir sayı ile değiştirebilirsiniz.
5. Sonuç hücresini seçin ve diğer hücrelerin metin dizilerinden n'inci sözcüğü çıkarmak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
2.4.3 Harika bir araçla n'inci kelimeyi çıkarın
Yukarıda sağlanan bir formülü veya kullanıcı tanımlı bir işlevi manuel olarak uygulamak istemiyorsanız, burada Kutools for Excel's'i önerir. Hücredeki n'inci kelimeyi çıkar Yarar. Bu özellik sayesinde, bir hücredeki bir metin dizesindeki n. sözcüğü yalnızca birkaç tıklamayla kolayca çıkarabilirsiniz.
1. Sonucu yerleştirmek için bir hücre seçin ve Kutools > Formül Yardımcısı > Metin > Çıkarmak hücredeki n. kelime. Ekran görüntüsüne bakın:
2. içinde Formül Yardımcısı iletişim kutusu, aşağıdaki gibi yapılandırmanız gerekir.
3. Ardından, B5 hücresindeki metin dizesinden nth (ikinci) kelime çıkarılır ve aynı anda bir formülün oluşturulduğunu görebilirsiniz. Bu sonuç hücresini seçin ve diğer metin dizelerinden n. sözcüğü almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
Bu özellik hakkında daha fazla bilgi edinmek için tıklayın.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
2.5 Bir sınırlayıcının n'inci oluşumundan önce veya sonra metni ayıklayın
Aşağıdaki ekran görüntüsünde gösterildiği gibi bir metin dizeleri listeniz olduğunu varsayalım. Metni bir boşluğun ikinci oluşumundan önce veya sonra çıkarmak için, bu bölüm, bunu yapmanıza yardımcı olacak iki formül sağlar.
2.5.1 Bir sınırlayıcının n'inci oluşumundan önceki metni ayıklayın
Excel'deki bir hücreden bir sınırlayıcının n'inci oluşumundan önce metni çıkarmak için SOL işlevini YERİNE GEÇİR ve BUL işlevleriyle birlikte kullanabilirsiniz.
Genel formül
=LEFT(SUBSTITUTE(text_string,"delimiter",CHAR(9),n),FIND(CHAR(9),SUBSTITUTE(text_string,"delimiter",CHAR(9),n),1)-1)
Argümanlar
Bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Listedeki diğer sonuçları almak için bu sonuç hücresini seçin ve Otomatik Doldurma Kolu'nu aşağı sürükleyin.
=LEFT(SUBSTITUTE(B5," ",CHAR(9),2),FIND(CHAR(9),SUBSTITUTE(B5," ",CHAR(9),2),1)-1)
Not: Formülde B5, metni çıkarmak istediğiniz metin dizesini içeren hücredir; Buradaki “ ” bir boşluğu temsil eder ve 2 sayısı bir boşluğun ikinci oluşumunu temsil eder. Bunları ihtiyaçlarınıza göre değiştirebilirsiniz.
2.5.2 Bir sınırlayıcının n'inci oluşumundan sonra metni ayıklayın
Bir sınırlayıcının n'inci oluşumundan sonra metni çıkarmak için SAĞ işlevini YERİNEK, UZUNLUK ve BUL işlevleriyle uygulayabilirsiniz.
Genel formül
=RIGHT(SUBSTITUTE(text_string, "delimiter", CHAR(9), n), LEN(text_string)- FIND(CHAR(9), SUBSTITUTE(text_string, "delimiter", CHAR(9), n), 1) + 1)
Argümanlar
Şimdi, aşağıdaki gibi B5:B10 aralığındaki her hücreden boşluğun ikinci oluşumundan sonra metni çıkarmak için bu formülü uygulayabilirsiniz.
Bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Kolu'nu aşağı sürükleyin.
=RIGHT(SUBSTITUTE(B5, " ", CHAR(9), 2), LEN(B5)- FIND(CHAR(9), SUBSTITUTE(B5, " ", CHAR(9), 2), 1) + 1)
2.6 Satır sonundan önce veya sonra metni ayıklayın
B sütununda bir sipariş listeniz olduğunu ve her hücreden yalnızca tarih bölümünü ve ürün No. bölümünü çıkarmak istediğinizi varsayalım. Bunu yapmak için aşağıdaki Excel formüllerini kullanabilirsiniz.
2.6.1 Formül ile ilk satır sonundan önce metni ayıklayın
Yukarıdaki ekran görüntüsünde de görebileceğiniz gibi, tarih bölümü hücre içinde ilk satır sonundan önce yer almaktadır. Bu bölüm, bir hücre içindeki ilk satır sonundan önce metni çıkarmanıza yardımcı olmak için SEARCH işleviyle birlikte bir SOL işlevi gösterecektir.
Genel formül
=LEFT(cell, SEARCH(CHAR(10), cell)-1)
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya içine girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LEFT(B5, SEARCH(CHAR(10), B5)-1)
Ardından, aşağıdaki ekran görüntüsünde gösterildiği gibi B5:B8 aralığındaki her hücredeki ilk satır sonu ayıklanmadan önce metni görebilirsiniz.
Not: Formülde CHAR(10), Windows'ta bir satır sonunu temsil eder.
2.6.2 Formülle son satır sonundan sonra metni ayıklayın
Bir önceki adımda, bir hücre içindeki ilk satır sonu gelmeden metnin nasıl ayıklanacağından bahsetmiştik. Ve bu bölüm, farklı bir formüle sahip bir hücre içindeki son satır sonundan sonra metni nasıl çıkaracağınız konusunda size rehberlik edecektir.
Genel formül
=TRIM(RIGHT(SUBSTITUTE(cell,CHAR(10),REPT(" ",200)),200))
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))
Daha sonra ürün no. Listedeki her hücrenin bir kısmı yukarıdaki ekran görüntüsünde gösterildiği gibi çıkarılır.
Not: Formülde CHAR(10), Windows'ta bir satır sonunu temsil eder.
2.7 Bir kelimeden önce veya sonra metni ayıklayın
Önceki bölümlerde, bir karakter veya sınırlayıcıdan önce veya sonra metnin nasıl çıkarılacağını öğrendik. Tüm bir kelimeden önce veya sonra metni çıkarmak için ne yapmalısınız? Bu bölümde, bu görevi gerçekleştirmenize yardımcı olacak üç yöntem tanıtılacaktır.
2.7.1 Belirli bir kelimeden önceki metni formülle ayıklayın
Aşağıdaki formül, Excel'deki bir hücredeki belirli bir sözcükten önce metni çıkarmanıza yardımcı olur.
Genel formül
=IFERROR(LEFT(cell,FIND(word,cell)-1),cell)
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
Bu örnekte, "Excel" kelimesinden önceki tüm metinleri çıkaracağız, bu yüzden kelimeyi doğrudan formüle yazıp çift tırnak içine alıyoruz. Veya "Excel" kelimesini içeren bir hücreye başvurabilirsiniz.
=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)
Notlar:
2.7.2 Formül ile belirli bir kelimeden sonra metni ayıklayın
Belirli bir kelimeden sonra metni çıkarmak için, bunu yapmak için aşağıdaki formülü uygulayabilirsiniz.
Genel formül
=TRIM(MID(cell,SEARCH(word,cell)+LEN(word),255))
Argümanlar
Bir hücre seçin, aşağıdaki formülü girin ve Keşfet sonucu almak için anahtar. Sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))
Ardından, aşağıdaki ekran görüntüsünde gösterildiği gibi her hücredeki “Excel” kelimesi çıkarıldıktan sonra tüm metinleri görebilirsiniz.
Notlar:
2.7.3 Harika bir araçla belirli bir kelimeden önce veya sonra metni ayıklayın
Formül kullanmanın çok fazla rahatsızlığa neden olabileceğini düşünüyorsanız, burada şiddetle tavsiye edilir: Metni Çıkar yarar Kutools for Excel. Bu özellik, Excel'deki Çıkarma görevini yalnızca birkaç tıklamayla otomatikleştirmeye yardımcı olur.
1. tık Kutools > Metin > Metni Çıkar Bu özelliği etkinleştirmek için.
2. içinde Metni Çıkar iletişim kutusunda aşağıdaki ayarları yapın.
Notlar: Dinamik sonuçlar oluşturmak istiyorsanız, Formül olarak ekle Kutu. Ardından, aralıktaki veriler değiştiğinde sonuçlar otomatik olarak güncellenecektir.
3. Sonra bir Metni Çıkar iletişim kutusu açılır, sonucu çıkarmak için bir hücre seçmeniz ve ardından OK düğmesine basın.
Ardından, seçilen aralıktaki her hücrede belirli bir kelimeden önceki veya sonraki metinler hemen ayıklanır.
Not: Bu özellik büyük/küçük harfe duyarlıdır.
Bu özellik hakkında daha fazla bilgi edinmek için tıklayın.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
3. Karakterler/kelimeler arasında ayıklayın
Belirli karakterler veya kelimeler arasında metin çıkarmak istiyorsanız, aşağıdaki yöntemleri deneyin.
3.1 İki karakter arasındaki metni ayıklayın
Aynı veya farklı karakterler olabilen iki karakter arasındaki metni çıkarmak için. Bu bölüm birkaç yöntem sunar ve ihtiyaçlarınıza göre bunlardan birini seçebilirsiniz.
3.1.1 Aynı iki karakter arasındaki metni formülle ayıklayın
Aşağıdaki ekran görüntüsünde gösterildiği gibi, B sütununda metin dizelerinin bir listesi var ve aralıktaki her hücreden “/” karakterleri arasındaki sayı bölümünü çıkarmak istiyorsanız, aşağıdaki formül size bir iyilik yapabilir.
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Sonuç hücresini seçin ve ardından listedeki diğer hücrelerin sonuçlarını almak için Otomatik Doldurma Kolu'nu aşağı sürükleyin.
=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")
Ardından, aralıktaki her hücreden iki aynı karakter “/” arasındaki metin çıkarılır. Ekran görüntüsüne bakın:
Not:
3.1.2 Formül ile iki farklı karakter arasındaki metni ayıklayın
Bir hücre içinde aynı iki karakter arasındaki metni nasıl ayıklayacağınızı öğrendikten sonra, burada iki farklı karakter arasındaki metni çıkarmak için bir formül göstereceğiz. Aşağıdaki ekran görüntüsünde gösterildiği gibi, B sütunundaki her hücreden yalnızca “<” ve “>” arasındaki e-posta adresini çıkarmak için aşağıdakileri yapabilirsiniz.
Genel formül
=MID(LEFT(cel,FIND("end_char",cell)-1),FIND("start_char",cell)+1,LEN(cell))
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))
Yukarıdaki ekran görüntüsünde gösterildiği gibi yalnızca belirtilen karakterler arasındaki metnin çıkarıldığını görebilirsiniz.
3.1.3 Harika bir araçla iki karakter arasındaki metni ayıklayın
Burada şiddetle tavsiye edilir Belirtilen metin arasında dizeleri ayıklayın özelliği Kutools for Excel Excel'deki bir hücrede aynı veya farklı iki karakter arasındaki metni kolayca ayıklamanıza yardımcı olmak için.
1. Sonucun çıktısını almak için boş bir hücre seçin ve ardından Kutools > Formül Yardımcısı > Formül Yardımcısı.
2. içinde Formül Yardımcısı iletişim kutusunda aşağıdaki ayarları yapın.
3. Ardından yalnızca B5 hücresindeki “<” ile “>” arasındaki metin çıkarılır. Bu arada, bir formül oluşturuldu, bu sonuç hücresini seçebilir ve ardından aynı listedeki diğer hücrelerden metin çıkarmak için Otomatik Doldurma Tutamağını aşağı sürükleyebilirsiniz.
Bu özellik hakkında daha fazla bilgi edinmek için tıklayın.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
3.1.4 Metni iki karakter (karakterler dahil) arasında kurala göre ayıklayın
Ayıkladıktan sonra iki karakteri tutmak istiyorsanız, Kutools for Excel'in Metin Çıkarma özelliğinde bir kural uygulamayı deneyin.
1. tık Kutools > Metin > Metni Çıkar.
2. içinde Metni Çıkar iletişim kutusunda aşağıdaki ayarları yapın.
3. Başka Metni Çıkar iletişim kutusu açılır, lütfen sonucu çıkarmak için bir hücre seçin ve ardından OK düğmesine basın.
Daha sonra belirtilen karakterler arasındaki metin (karakterler dahil) seçilen aralıktaki her hücreden toplu olarak çıkarılır.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
3.2 İki kelime arasındaki metni ayıklayın
İki karakter arasındaki metni çıkarmaya ek olarak, iki kelime arasındaki metni de çıkarmanız gerekebilir. Örneğin, aşağıdaki ekran görüntüsünde gösterildiği gibi, B sütunundaki her hücreden iki “KTE” ve “özellik” kelimesi arasındaki tüm metin dizelerini çıkarın. Bunu yapmak için aşağıdaki yöntemlerden birini deneyebilirsiniz.
3.2.1 İki kelime arasındaki metni formülle ayıklayın
Bir hücre içindeki iki sözcük arasındaki tüm metin dizelerini ayıklamak için MID işlevine ve SEARCH işlevine dayalı bir formül kullanabilirsiniz.
Genel formül
=MID(cell,SEARCH("start_word",cell)+3,SEARCH("end_word",cell)-SEARCH("start_word",cell)-4)
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=MID(B5,SEARCH("KTE",B5)+3,SEARCH("feature",B5)-SEARCH("KTE",B5)-4)
not: Formülde 3 sayısı "KTE" kelimesinin karakter uzunluğunu temsil etmektedir; 4 sayısı, "KTE" kelimesinin karakter uzunluğu artı 1'i temsil eder.
Belirtilen iki kelime arasındaki tüm metin dizelerinin B sütunundaki her hücreden çıkarıldığını görebilirsiniz.
3.2.2 Harika bir araçla iki kelime arasındaki metni ayıklayın
Birçok Excel kullanıcısı için formülleri hatırlamak ve işlemek zor olabilir. Burada, ile Belirtilen metin arasında dizeleri ayıklayın özelliği Kutools for Excel, sadece birkaç tıklamayla iki kelime arasındaki metni kolayca ayıklayabilirsiniz.
1. Sonucun çıktısını almak için bir hücre seçin ve ardından Kutools > Formül Yardımcısı > Formül Yardımcısı.
2. içinde Formül Yardımcısı iletişim kutusu, aşağıdaki gibi yapılandırmanız gerekir.
3. Daha sonra B5 hücresindeki “KTE” ve “özellik” kelimeleri arasındaki tüm metin dizeleri ayıklanır. Bu arada, bir formül oluşturuldu, bu sonuç hücresini seçebilir ve ardından aynı listedeki diğer hücrelerden metin çıkarmak için Otomatik Doldurma Tutamağını aşağı sürükleyebilirsiniz.
Bu yardımcı programın ücretsiz denemesine (30 günlük) sahip olmak istiyorsanız, indirmek için lütfen tıklayınızve ardından yukarıdaki adımlara göre işlemi uygulamaya gidin.
Sayıları konuma göre ayıkla
Alfasayısal dizelerin bir listesi için üç durum olabilir:
- Numara metnin başında;
- Numara metnin sonundadır;
- Sayı metnin herhangi bir yerinde olabilir.
Bu bölümde, yukarıda belirtilen her durumda sayıları çıkarmak için kullanılabilecek farklı yöntemler sunacağız.
1 Bir dizenin solundan sayıyı çıkarın
Bu bölümde, yalnızca hücredeki metinden önce görünen sayıları çıkarmanıza yardımcı olacak bir formül tanıtılacaktır.
Genel formül
=LEFT(cell, MATCH(FALSE, ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell)+1)), 1) *1), 0) -1)
Argümanlar
Not:
Boş bir hücre seçin, aşağıdaki formülü girin ve Ctrl + vardiya + Keşfet or Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından diğer hücrelerin sayısını almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LEFT(B5, MATCH(FALSE, ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5)+1)), 1) *1), 0) -1)
Notlar:
2 Bir dizenin sağından sayıyı çıkarın
Aşağıdaki ekran görüntüsünde gösterildiği gibi, yalnızca bir hücredeki metinden sonra görünen sayıları çıkarmak için aşağıdaki formülü deneyin.
Genel formül
=RIGHT(cell, LEN(cell) - MAX(IF(ISNUMBER(MID(cell, ROW(INDIRECT("1:"&LEN(cell))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(cell))), 0)))
Argümanlar
Not:
Boş bir hücre seçin, aşağıdaki formülü girin ve Ctrl + vardiya + Keşfet or Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından diğer hücrelerin sayısını almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=RIGHT(B5, LEN(B5) - MAX(IF(ISNUMBER(MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1) *1)=FALSE, ROW(INDIRECT("1:"&LEN(B5))), 0)))
Notlar:
3. Bir metin dizesindeki herhangi bir konumdan tüm sayıları çıkarın
Yukarıdaki yöntemler, bir metin dizesinin yalnızca solundan veya sağından sayı çıkarmaya yardımcı olur. Bir metin dizesindeki tüm sayıları herhangi bir yerden çıkarmak istiyorsanız, bunu yapmanız için burada size üç yöntem sunuyoruz.
3.1 Bir dizgede herhangi bir yerden tüm sayıları formülle ayıklayın
Excel'de bir metin dizesindeki tüm sayıları herhangi bir yerden çıkarmak için aşağıdaki formülü uygulayabilirsiniz.
1. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet B5 hücresinden tüm sayıları almak için tuş.
=SUMPRODUCT(MID(0&B5, LARGE(INDEX(ISNUMBER(--MID(B5, ROW(INDIRECT("1:"&LEN(B5))), 1)) * ROW(INDIRECT("1:"&LEN(B5))), 0), ROW(INDIRECT("1:"&LEN(B5))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B5)))/10)
2. Sonuç hücresini seçin ve ardından diğer tüm hücrelerin sayısını almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
3.2 VBA ile bir dizedeki tüm sayıları herhangi bir yerden ayıklayın
Yukarıdaki formül, birçok Excel kullanıcısı için çok uzun ve karmaşıktır. Aslında, görevi Excel'de otomatikleştirmek için bir VBA komut dosyası çalıştırabilirsiniz. Aşağıdaki gibi yapabilirsiniz.
1. Tuşuna basın. Ara Toplam + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. Açılışta Uygulamalar için Microsoft Visual Basic Pencere, tıklayın Ekle > Modülü. Ardından aşağıdaki VBA'yı Modül kodu penceresine kopyalayın.
VBA kodu: Bir metin dizesindeki tüm sayıları herhangi bir yerden çıkarın
Sub ExtrNumbersFromRange()
'Updated by Extendoffice 20220106
Dim xRg As Range
Dim xDRg As Range
Dim xRRg As Range
Dim nCellLength As Integer
Dim xNumber As Integer
Dim strNumber As String
Dim xTitleId As String
Dim xI As Integer
xTitleId = "KutoolsforExcel"
Set xDRg = Application.InputBox("Please select text strings:", xTitleId, "", Type:=8)
If TypeName(xDRg) = "Nothing" Then Exit Sub
Set xRRg = Application.InputBox("Please select output cell:", xTitleId, "", Type:=8)
If TypeName(xRRg) = "Nothing" Then Exit Sub
xI = 0
strNumber = ""
For Each xRg In xDRg
xI = xI + 1
nCellLength = Len(xRg)
For xNumber = 1 To nCellLength
If IsNumeric(Mid(xRg, xNumber, 1)) Then
strNumber = strNumber & Mid(xRg, xNumber, 1)
End If
Next xNumber
xRRg.Item(xI) = strNumber
strNumber = ""
Next xRg
End Sub
3. Tuşuna basın. F5 kodu çalıştırmak için anahtar. Açılışta KutoolsforExcel iletişim kutusunda, her hücreden tüm sayıları çıkarmak istediğiniz hücre aralığını seçin ve ardından OK düğmesine basın.
4. Sonra başka KutoolsforExcel iletişim kutusu açılır. Bu iletişim kutusunda bir hedef hücre seçin ve Tamam.
Ardından, seçilen aralıktaki her hücreden tüm sayılar toplu olarak çıkarılır.
4. Belirli bir metinden sonra sayıları ayıklayın
Aşağıdaki ekran görüntüsünde gösterildiği gibi, belirli “Hayır” metninden sonra herhangi bir sayıyı çıkarmak için bu bölüm, bunu yapmanıza yardımcı olacak iki yöntem sunar.
4.1 Formül ile belirli bir metinden sonra sayıları ayıklayın
Excel'de bir hücredeki belirli bir metinden sonra sayıları çıkarmak için aşağıdaki formülü uygulayabilirsiniz.
Genel formül:
=LOOKUP(10^6,1*MID(cell,MIN(FIND({0,1,2,3,4,5,6,7,8,9},cell&"0123456789",FIND("text"," "&cell&" "))),{2,3,4,5,6}))
Argümanlar
Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve Keşfet sonucu almak için anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=LOOKUP(10^6,1*MID(B5,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B5&"0123456789",FIND("No."," "&B5&" "))),{2,3,4,5,6}))
Notlar:
4.2 Kullanıcı tanımlı işlevle belirli bir metinden sonra sayıları ayıklayın
Aşağıdaki kullanıcı tanımlı işlev, bir hücredeki belirli bir metinden sonra sayıların çıkarılmasına da yardımcı olabilir. Lütfen aşağıdaki gibi yapın.
1. Tuşuna basın. Ara Toplam + F11 tuşlarını açmak için Uygulamalar için Microsoft Visual Basic pencere.
2. içinde Uygulamalar için Microsoft Visual Basic Pencere, tıklayın Ekle > Modül, ve ardından aşağıdaki VBA kodunu Modül kodu penceresine kopyalayın.
VBA kodu: bir hücredeki belirli bir metinden sonra sayıları ayıklayın
Function GetNumberAfterTheChar(Rng As Range, Char As String)
'Updated by Extendoffice 20220106
Dim xValue As String
Dim xRntString As String
Dim xStart As Integer
Dim xC
xValue = Rng.Text
xStart = InStr(1, xValue, Char, vbTextCompare)
If IsEmpty(xStart) Then
GetNumberAfterTheChar = ""
Exit Function
End If
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xStart = xStart - 1 + Len(Char)
If xStart < 1 Then
GetNumberAfterTheChar = ""
Exit Function
End If
xValue = Mid(xValue, xStart + 1)
xRntString = ""
For xI = 1 To Len(xValue)
xC = Mid(xValue, xI, 1)
Select Case Asc(xC)
Case 48 To 57
xRntString = xRntString & xC
Case Else
Exit For
End Select
Next
GetNumberAfterTheChar = xRntString
End Function
3. Tuşuna basın. Ara Toplam + Q kapatmak için anahtarlar Uygulamalar için Microsoft Visual Basic pencere.
4. Bir hücre seçin, aşağıdaki formülü girin ve Keşfet anahtar. Bu sonuç hücresini seçin ve ardından bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamağını aşağı sürükleyin.
=GetNumberAfterTheChar(B5,"No. ")
Notlar:
İlgili yazılar:
Excel Eğitimi: Metin, Sayı ve Tarih Hücrelerini Böl (Birden Çok Sütuna Ayırın)
Bu öğretici üç bölüme ayrılmıştır: bölünmüş metin hücreleri, bölünmüş sayı hücreleri ve bölünmüş tarih hücreleri. Her parça, aynı sorunla karşılaştığınızda bölme işiyle nasıl başa çıkacağınızı bilmenize yardımcı olacak farklı örnekler sunar.
Daha fazlasını öğrenmek için tıklayın ...
Excel Hücrenin Belirtilen Konumuna Metin ve Sayı Ekleme
Excel'de hücrelere metin veya sayı eklemek çok yaygın bir iştir. Adlar arasına boşluk ekleme, hücrelere ön ek veya son ek ekleme, sosyal sayılara tire ekleme gibi. İşte bu öğreticide, Excel'de neredeyse tüm ekleme senaryolarını listeler ve sizin için karşılık gelen yöntemleri sağlar.
Daha fazlasını öğrenmek için tıklayın ...
Excel Metin Dizelerinden Karakterleri, Kelimeleri, Sayıları Kaldırma
Diyelim ki, karakterleri, sayıları veya diğer belirli sembolleri içeren uzun bir metin dizileri listeniz var. Bazı durumlarda, metin dizilerinden sağ, sol veya orta gibi konuma göre bazı karakterleri kaldırmanız veya bazı istenmeyen karakterleri, sayıları diziler listesinden silmeniz gerekebilir. Çözümleri tek tek bulmak başınızı ağrıtacak, bu öğretici Excel'de karakterleri, kelimeleri veya sayıları kaldırmak için her türlü yöntemi toplar.
Daha fazlasını öğrenmek için tıklayın ...
En İyi Ofis Üretkenlik Araçları
Kutools for Excel ile Excel Becerilerinizi Güçlendirin ve Daha Önce Hiç Olmadığı Gibi Verimliliği Deneyimleyin. Kutools for Excel, Üretkenliği Artırmak ve Zamandan Tasarruf Etmek için 300'den Fazla Gelişmiş Özellik Sunar. En Çok İhtiyacınız Olan Özelliği Almak İçin Buraya Tıklayın...
Office Tab, Office'e Sekmeli Arayüz Getirir ve İşinizi Çok Daha Kolay Hale Getirir
- Word, Excel, PowerPoint'te sekmeli düzenlemeyi ve okumayı etkinleştirin, Publisher, Access, Visio ve Project.
- Yeni pencereler yerine aynı pencerenin yeni sekmelerinde birden çok belge açın ve oluşturun.
- Üretkenliğinizi% 50 artırır ve her gün sizin için yüzlerce fare tıklamasını azaltır!