Ana içeriğe atla

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 ]

(İlgili bölüme gitmek için aşağıdaki içindekiler tablosundaki veya sağdaki herhangi bir başlığa tıklayın.)

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

Metin dizesi: Çıkarmak istediğiniz karakterleri içeren metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
Karakter_sayısı: Çıkarmak istediğiniz karakter sayısını belirtir.
Num_chars sıfırdan büyük veya sıfıra eşit olmalıdır;
Belirtilen Sayı_karakterleri metin dizesinin uzunluğundan büyükse, tüm metni döndürür;
Num_chars atlanırsa, 1 olduğu varsayılır.

Ş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

Metin dizesi: Çıkarmak istediğiniz karakterleri içeren metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
Karakter_sayısı: Çıkarmak istediğiniz karakter sayısını belirtir.
The Karakter_sayısı sıfırdan büyük veya sıfıra eşit olmalıdır;
belirtilen ise Karakter_sayısı metin dizesinin uzunluğundan büyükse, tüm metni döndürür;
Eğer Karakter_sayısı atlanırsa, 1 olduğu varsayılır.

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.

2.1) İçinde kaldığınızdan emin olun. Konuma göre ayıkla sekme;
2.2) içinde Aralık kutusunda, seçilen aralık içeride görüntülenir, ihtiyaçlarınıza göre başka bir aralıkla değiştirebilirsiniz;
2.3) içinde Opsiyonlar Bölüm:
İlk N karakteri çıkarmak istiyorsanız, İlk N karakteri radyo düğmesini tıklayın ve ardından metin kutusuna çıkaracağınız karakter sayısını belirtin. Bu durumda 2 sayısını giriyorum;
Son N karakteri çıkarmak istiyorsanız, Son N karakteri radyo düğmesini tıklayın ve ardından metin kutusuna çıkaracağınız karakter sayısını belirtin. Bu durumda, metin dizesinden son 2 karakteri çıkarmak istiyorum, bu yüzden metin kutusuna 2 sayısını giriyorum.
2.4) Tıklayın OK. Ekran görüntüsüne bakın:

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

Metin dizesi: Alt dizeyi çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
Sınırlayıcı: Bir hücreden hangi metnin çıkarılacağını tanımlayan ilk sınırlayıcı.

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

Metin dizesi: Alt dizeyi çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
sınırlayıcı: Bir hücreden hangi metnin çıkarılacağını tanımlayan ilk sınırlayıcı.
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 diğer sonuçları almak için Otomatik Doldurma Tutamağını aşağı sürükleyin.

=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.

2.1) İçinde kalın Konuma göre ayıkla sekme;
2.2) içinde Aralık kutusunda, seçilen aralık içeride görüntülenir, istediğiniz gibi değiştirebilirsiniz;
2.3) içinde Opsiyonlar Bölüm:
Metni ilk sınırlayıcıdan önce çıkarmak için, metinden önce radyo düğmesini tıklayın ve ardından metin kutusuna ilk sınırlayıcıyı yazın;
İlk sınırlayıcıdan sonra metni çıkarmak için metinden sonra radyo düğmesini tıklayın ve ardından metin kutusuna ilk sınırlayıcıyı girin.
2.4) Tıklayın Tamam.

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

Metin dizesi: Alt dizeyi çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
sınırlayıcı: Bir hücreden hangi metnin çıkarılacağını tanımlayan son sınırlayıcı.

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

Metin dizesi: Alt dizeyi çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
sınırlayıcı: Bir hücreden hangi metnin çıkarılacağını tanımlayan son sınırlayıcı.

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

Metin dizesi: Alt dizeyi çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
nth_char: Bir sayı, n'inci karakteri temsil eder ve ondan sonraki metni çıkaracaksınız.

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

Metin dizesi: n'inci sözcüğü çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
N: Bir sayı, metin dizesinden çıkaracağınız n. sözcüğü temsil eder.

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.

2.1) içinde Bir formül listesi seçin kutusu, Hücredeki n'inci kelimeyi çıkar seçenek vurgulanır;
2.2) içinde Hücre kutusunda, n'inci sözcüğü çıkarmak istediğiniz metin dizesini içeren bir hücre seçin;
2.3) içinde Nth kutusunda, n'inci sayıyı içeren bir hücre seçin veya ihtiyaçlarınıza göre doğrudan bir sayı girin;
2.4) Tıklayın Tamam.

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

Metin dizesi: Metni çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
N: Sayı, önündeki metni çıkaracağınız bir sınırlayıcının n. oluşumunu temsil eder.

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

Metin dizesi: Metni çıkarmak istediğiniz metin dizesi. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;
N: Sayı, kendisinden sonra metni çıkaracağınız bir sınırlayıcının n. oluşumunu temsil eder.

Ş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

Cep: İlk satır sonundan önce metni çıkarmak istediğiniz hücre.

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

Cep: İlk satır sonundan önce metni çıkarmak istediğiniz hücre.

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

Cep: Belirli bir kelimeden önce metni çıkarmak istediğiniz hücre.
Kelime: Ondan önceki tüm metni çıkarmak istediğiniz kelime. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;

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:

1) Bu formül büyük/küçük harfe duyarlıdır.
2) Girdiğiniz kelimenin aynı hücrede kopyaları varsa, formül metni yalnızca ilk geçtiği yerden sonra çıkarır.

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

Cep: Belirli bir kelimeden sonra metni çıkarmak istediğiniz hücre.
Kelime: Ondan sonraki tüm metni çıkarmak istediğiniz kelime. Bir hücre başvurusu veya çift tırnak içine alınmış gerçek bir metin dizesi olabilir;

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:

1) Bu formül büyük/küçük harfe duyarsızdır.
2) Girdiğiniz kelimenin aynı hücrede kopyaları varsa, formül metni yalnızca ilk geçtiği yerden sonra çıkarır.

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.

2.1) İçinde bulunduğunuzdan emin olun. Konuma göre ayıkla sekme;
2.2) içinde Aralık kutusunu tıklayın metinleri çıkarmak istediğiniz hücre aralığını seçmek için düğme;
2.3) içinde Opsiyonlar Bölüm:
Bir kelimeden önceki tüm metinleri çıkarmak için, metinden önce radyo düğmesine basın ve ardından kelimeyi metin kutusuna girin;
Bir kelimeden sonra tüm metinleri çıkarmak için metinden sonra radyo düğmesini tıklayın ve ardından kelimeyi metin kutusuna girin.
2.4) OK buton. Ekran görüntüsüne bakı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:

1) B5 aynı iki karakter arasında metin çıkarmak istediğiniz hücredir;
2) "/”, aralarında metin çıkarmak istediğiniz iki karakterdir.
Bu değişkenleri kendi verilerinize göre değiştirmeniz gerekir.

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

Cep: İki farklı karakter arasındaki metni çıkarmak istediğiniz hücre;
Bitiş_karakteri: İki farklı karakterin bitiş karakteri;
Başlat_char: İki farklı karakterin başlangıç ​​karakteri.

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.

2.1) Kontrol edin filtre kutusunu seçin ve ardından kelimeyi girin "Ayıkla" metin kutusuna;
2.2) içinde Bir formül listesi seçin kutusunu tıklayın Belirtilen metin arasında dizeleri ayıklayın seçenek;
2.3) içinde Bağımsız değişken girişi Bölüm:
içinde Hücre kutusunda, metni çıkarmak istediğiniz hücreyi seçin (burada B5 hücresini seçiyorum);
içinde Karakterleri başlat kutusuna iki farklı karakterin başlangıç ​​karakterini girin;
içinde Son karakter(ler) kutusuna iki farklı karakterin bitiş karakterini girin.
2.4) Tıklayın Tamam. Ekran Bkz:

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.

2.1) Kurala göre ayıkla sekme;
2.2) içinde Aralık bölümüne tıklayın. karakterler arasındaki metni çıkarmak istediğiniz hücre aralığını seçmek için düğme;
2.3) içinde Metin kutusuna gir <*>;
İpuçları: "<"Ve">”, aralarında metin çıkarmak istediğiniz karakterlerdir, * herhangi bir sayıda karakteri temsil eden bir joker karakterdir. Koşulları ihtiyaçlarınıza göre girebilirsiniz.
2.4) Ekle koşulu eklemek için düğmesine Kural açıklaması liste kutusu;
2.5) Ok buton. Ekran görüntüsüne bakı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

Cep: iki sözcük arasındaki tüm metin dizelerini çıkarmak istediğiniz hücre;
Başlangıç_kelime: ondan sonra tüm metin dizelerini çıkarmak istediğiniz iki kelimenin başlangıç ​​kelimesi;
uzunluk1: başlangıç ​​kelimesinin karakter uzunluğu.
Bitiş_kelime: ondan önceki tüm metin dizelerini çıkarmak istediğiniz iki kelimenin son kelimesi.
uzunluk2: başlangıç ​​kelimesinin karakter uzunluğu artı 1.

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.

2.1) Kontrol edin filtre kutusunu seçin ve ardından kelimeyi girin "Ayıkla" metin kutusuna;
2.2) içinde Bir formül listesi seçin kutusunu tıklayın Belirtilen metin arasında dizeleri ayıklayın seçenek;
2.3) içinde Bağımsız değişken girişi Bölüm:
içinde Hücre kutusunda, metni çıkarmak istediğiniz hücreyi seçin (burada B5 hücresini seçiyorum);
içinde Karakterleri başlat kutusuna, ondan sonra tüm metin dizelerini çıkarmak istediğiniz iki kelimenin başlangıç ​​kelimesini girin;
içinde Bitiş karakter(ler)i kutusuna, önündeki tüm metin dizelerini çıkarmak istediğiniz iki kelimenin bitiş kelimesini girin.
2.4) Tıklayın Tamam. Ekran Bkz:

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:

  1. Numara metnin başında;
  2. Numara metnin sonundadır;
  3. 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

Cep: Metin dizesinin solundaki sayıyı çıkarmak istediğiniz hücre.

Not:

1) Excel 2019 ve önceki sürümlerini kullanıyorsanız, Ctrl + vardiya + Keşfet bu dizi formülünü onaylamak için tuşlar.
2) Excel 365 veya Excel 2021 kullanıyorsanız, bu formülü Keşfet tuşuna basın.

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:

1) Bir hücre yalnızca sayılar içeriyorsa, tam sayı çıkarılacaktır.
2) Bu formül, yalnızca bir metin dizesinin solundaki sayıları çıkarır. Metin dizesinin ortasında veya sonunda sayılar varsa, bunlar yok sayılır.

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

Cep: Metin dizesinin başlangıcından sayı çıkarmak istediğiniz hücre.

Not:

1) Excel 2019 ve önceki sürümlerini kullanıyorsanız, Ctrl + vardiya + Keşfet bu dizi formülünü onaylamak için tuşlar.
2) Excel 365 veya Excel 2021 kullanıyorsanız bu formülü Enter tuşu ile onaylamanız yeterlidir.

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:

1) Bir hücre yalnızca sayılar içeriyorsa, tam sayı çıkarılacaktır.
2) Bu formül, yalnızca bir metin dizesinin sağındaki sayıları çıkarır. Metin dizesinin ortasında veya başında sayılar varsa, bunlar yok sayılır.

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

Cep: Belirli bir metinden sonra sayıları çıkarmak istediğiniz hücre;
Şarkı: Ondan sonra sayıları çıkarmak istediğiniz metin.

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:

1) Bir hücre belirli metni içermiyorsa formül #N/A değerini döndürür.
2) Formül büyük/küçük harfe duyarlıdır.

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:

1) Bu formülde B5, sayıyı çıkarmak istediğiniz hücredir ve “Hayır. ”, kendisinden sonra gelen sayıyı çıkarmak istediğiniz özel metindir. Bunları ihtiyacınıza göre değiştirebilirsiniz.
2) Belirli metin ile sayı arasında bir sınırlayıcı varsa, lütfen sınırlayıcıyı metnin sonuna ekleyin. Bu durumda, sonunda “Hayır” olarak görüntülenen “Hayır” metninden sonra bir boşluk ekledim. ”.
3) Bu yöntem büyük/küçük harfe duyarsızdır;
4) Bir hücre belirli metni içermiyorsa, formül boş bir sonuç döndürür.

İ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 AI Yardımcısı: Aşağıdakilere dayalı olarak veri analizinde devrim yaratın: Akıllı Yürütme   |  Kodunu oluşturun  |  Özel Formüller Oluşturun  |  Verileri Analiz Edin ve Grafikler Oluşturun  |  Kutools İşlevlerini Çağır...
Popüler Özellikler: Yinelenenleri Bul, Vurgula veya Tanımla   |  Boş Satırları Sil   |  Veri Kaybı Olmadan Sütunları veya Hücreleri Birleştirin   |   Formülsüz Tur ...
Süper Arama: Çoklu Ölçütlü VLookup    Çoklu Değer VLookup  |   Birden Çok Sayfada VLookup   |   Bulanık Arama ....
Gelişmiş Açılır Liste: Hızla Açılır Liste Oluşturun   |  Bağımlı Açılır Liste   |  Çoklu Seçim Açılır Liste ....
Sütun Yöneticisi: Belirli Sayıda Sütun Ekleme  |  Sütunları Taşı  |  Gizli Sütunların Görünürlük Durumunu Değiştir  |  Aralıkları ve Sütunları Karşılaştırın ...
Öne Çıkan Özellikler: Izgara Odağı   |  Tasarım görünümü   |   Büyük Formül Çubuğu    Çalışma Kitabı ve Sayfa Yöneticisi   |  Kaynak Kütüphanesi (Otomatik metin)   |  Tarih Seçici   |  Çalışma Sayfalarını Birleştirin   |  Hücreleri Şifrele/Şifresini Çöz    E-postaları Listeye Göre Gönder   |  Süper Filtre   |   Özel Filtre (kalın/italik/üstü çizili filtre...) ...
En İyi 15 Araç Seti12 Metin Tools (Metin ekle, Karakterleri Kaldır, ...)   |   50+ Grafik Türleri (Gantt şeması, ...)   |   40+ Pratik Formüller (Yaşı doğum gününe göre hesapla, ...)   |   19 sokma Tools (QR Kodunu Girin, Yoldan Resim Ekle, ...)   |   12 Dönüştürme Tools (Sayılardan Kelimelere, Para Birimi Dönüştürme, ...)   |   7 Birleştir ve Böl Tools (Gelişmiş Kombine Satırları, Bölünmüş hücreler, ...)   |   ... ve dahası

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...

Açıklama


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations