Excel eğitimi – Belirli bir konuma göre metin veya sayı çıkarma

Pek çok durumda, bir hücredeki bir cümle veya metin dizisinden yalnızca faydalı metni çıkarmanız gerekebilir; örneğin, bir adresten ili çıkarmak, bir cümleden e-posta adresini almak, bir konuşmadan kargo takip numarasını çıkarmak gibi. Bu eğitim, çıkarma işlemini bir hücredeki belirli bir konuma indirger ve Excel'de belirli bir konuma göre bir hücreden metin veya sayı çıkarmak için farklı yöntemleri toplar.

Konuma göre metin çıkarma

Bu bölüm, bir hücreden metin çıkarılabilecek yaygın konumları toplar ve bunları adım adım nasıl ele alacağınızı gösteren yöntemler sunar. Daha fazla ayrıntı için göz atabilirsiniz.

1. Soldan veya sağdan karakter sayısını çıkarma

Bir dizenin solundan veya sağından karakter sayısını çıkarmak için aşağıdaki yöntemlerden birini deneyebilirsiniz.

1.1 Formüllerle ilk veya son N karakteri çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi, B sütununda bir metin dizisi listeniz olduğunu varsayalım; her bir diziden ilk2 ve son2 karakteri çıkarmak için aşağıdaki formülleri uygulayabilirsiniz.

Bir metin dizisinden ilk N karakteri çıkarma

LEFT fonksiyonu, Excel'de bir metin dizisinden ilk N karakteri kolayca çıkarmanıza yardımcı olabilir.

Genel formül

=LEFT(text_string,[num_chars])

Argümanlar

Şimdi bu formülü, B sütunundaki hücrelerden ilk2 karakteri çıkarmak için uygulayabilirsiniz.

1. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve ilk sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=LEFT(B5,2)

Artık verilen aralıktaki her hücredeki ilk2 karakteri çıkardınız.

Bir metin dizisinden son N karakteri çıkarma

Burada, Excel'de bir metin dizisinden son N karakteri çıkarmak için RIGHT fonksiyonunu kullanıyoruz.

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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=RIGHT(B5,2)

1.2 Harika bir araç ile ilk veya son N karakteri çıkarma

Yukarıdaki formüller basit olsa da, uzun bir metin dizisi listesinden ilk veya son n karakteri çıkarmak için yine de Otomatik Doldurma Tutamacını yukarıdan aşağıya sürüklemeniz gerekir, bu da biraz zaman alabilir. Burada, toplu olarak bir metin dizisi listesinden ilk veya son N karakteri çıkarmak için "Kutools for Excel"in "Metni Çıkart" aracını öneriyoruz.

1. Metin çıkarmak istediğiniz metin dizisi listesini önceden seçin ve "Kutools" > "Metin" > "Metni Çıkart"a tıklayın.

2. Açılan "Metni Çıkart" iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

Not: Metin dizisi değiştiğinde sonucu dinamik yapmak için "Formül Olarak Ekle" kutusunu işaretleyebilirsiniz.

3. Sonraki açılan "Metni Çıkart" iletişim kutusunda, çıkarılan karakterleri çıkış olarak vereceğiniz bir hücre seçin ve ardından "Tamam"a tıklayın.

Ardından, seçili hücrelerden toplu olarak ilk N veya son N karakter çıkarılır.

Bu özellikle ilgili daha fazla bilgi için tıklayın.

2. Belirli bir karakter/kelimeden önce veya sonra metin çıkarma

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 ayraçtan (bir karakter) önce veya sonra metin çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi, B4:B10 aralığındaki her hücreden ilk ayraçtan önce veya sonra metni çıkarmak için aşağıdaki yöntemlerden birini uygulayabilirsiniz.

2.1.1 Formül ile ilk ayraçtan önceki metni çıkarma

LEFT ve FIND fonksiyonlarına dayalı bir formül uygulamak, bir hücreden ilk ayraçtan önceki metni çıkarmaya yardımcı olabilir. Bunu yapmak için aşağıdaki adımları izleyebilirsiniz.

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 girin ve ilk sonucu almak için "Enter" tuşuna basın. İlk sonuç hücresini seçin ve diğer hücrelerin ilk ayraçtan önceki metinlerini almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=LEFT(B5,FIND("-",B5,1)-1)

2.1.2 Formül ile ilk ayraçtan sonraki metni çıkarma

Aşağıdaki formül, Excel'de bir hücreden ilk ayraçtan sonraki metni çıkarmanıza 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ç ile ilk ayraçtan önce veya sonra metni çıkarma

Burada, "Kutools for Excel"in "Metni Çıkart" aracını şiddetle tavsiye ediyoruz. Bu özellikle, bir hücre aralığındaki ilk ayraçtan önce veya sonra metinleri toplu olarak kolayca çıkarabilirsiniz.

1. Metni çıkarmak istediğiniz hücre aralığını seçin ve ardından "Kutools" > "Metin" > "Metni Çıkart"a tıklayın.

2. "Metni Çıkart" iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

Not: Metin dizisi değiştiğinde sonucu dinamik yapmak için Formül Olarak Ekle kutusunu işaretleyebilirsiniz.

3. Ardından başka bir "Metni Çıkart" iletişim kutusu açılır, sonuçları çıkış olarak vereceğiniz bir hücre seçin ve "Tamam"a tıklayın.

Ardından, seçili hücrelerden ilk ayraçtan önce veya sonra metinler anında çıkarılır.

Bu özellikle ilgili daha fazla bilgi için lütfen ziyaret edin: Excel'de Hücrelerden Belirli Metni Hızlıca Çıkartın.

2.2 Son ayraçtan (bir karakter) önce veya sonra metin çıkarma

Yukarıdaki adımlarda, bir hücreden ilk ayraçtan önce veya sonra metin çıkarmanın yöntemlerini öğrendik. Aşağıdaki ekran görüntüsünde olduğu gibi, bu bölümde bir hücreden son ayraçtan önce veya sonra metin çıkarmak için iki formül gösterilecektir. Bunu yapmak için aşağıdaki adımları izleyebilirsiniz.

2.2.1 Formül ile son ayraçtan önceki metni çıkarma

Bir hücreden son ayraçtan önceki metni çıkarmak için, LEFT fonksiyonu içinde SEARCH, LEN ve SUBSTITUTE fonksiyonlarını 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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve aynı sütundaki diğer metin dizilerinden metinleri çıkarmak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=LEFT(B5,SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-",""))))-1)

2.2.2 Formül ile son ayraçtan sonraki metni çıkarma

Bir hücreden son ayraçtan önceki metni çıkardıktan sonra, ihtiyacınıza göre son ayraçtan sonraki metni çı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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve aynı sütundaki diğer metin dizilerinden metinleri çıkarmak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=RIGHT(B5,LEN(B5)-SEARCH("#",SUBSTITUTE(B5,"-","#",LEN(B5)-LEN(SUBSTITUTE(B5,"-","")))))

2.3 N. karakterden sonraki metni çıkarma

Aşağıdaki örneğe bakın, B4:B10 aralığında bir metin dizisi listesi var; her hücreden üçüncü karakterden sonraki metni çıkarmak için MID ve LEN fonksiyonlarına 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 girin ve sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=MID(B5,3+1,LEN(B5))

2.4 Bir metin dizisinden N. kelimeyi çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi bir metin dizisi listeniz olduğunu ve yalnızca N. kelimeyi çıkarmak istediğinizi varsayalım, bu bölümde bunu yapmanız için üç yöntem sunulmaktadır.

2.4.1 Formül ile N. kelimeyi çıkarma

Bir hücredeki metin dizisinden N. kelimeyi çıkarmak için TRIM, MID, SUBSTITUTE, REPT ve LEN fonksiyonlarını 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ığında metin dizileri, D5:D10 aralığında ise N. kelimeyi temsil eden sayılar var; bu formülü uygulayarak metin dizisinden N. kelimeyi çıkaralım.

Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve ilk sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer hücrelerin N. kelimesini almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (D5-1)*LEN(B5)+1, LEN(B5)))

Not: Formülde doğrudan N. sayıyı yazabilirsiniz.

=TRIM(MID(SUBSTITUTE(B5," ",REPT(" ",LEN(B5))), (2-1)*LEN(B5)+1, LEN(B5)))

2.4.2 Kullanıcı tanımlı fonksiyon ile N. kelimeyi çıkarma

Yukarıdaki formül dışında, Excel'de bir hücreden N. kelimeyi çıkarmak için kullanıcı tanımlı bir fonksiyon da uygulayabilirsiniz.

1. "Microsoft Visual Basic for Applications" penceresini açmak için "Alt" + "F11" tuşlarına basın.

2. "Microsoft Visual Basic for Applications" penceresinde, "Ekle" > "Modül"e tıklayın ve ardından aşağıdaki VBA kodunu Kod penceresine kopyalayın.

VBA kodu: Bir hücredeki metin dizisinden N. kelimeyi çıkarma

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. "Microsoft Visual Basic for Applications" penceresini kapatmak için "Alt" + "Q" tuşlarına basın.

4. N. kelimeyi çıkarmak istediğiniz metin dizilerini içeren çalışma sayfasına geri dönün. Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve N. kelimeyi almak için "Enter" tuşuna basın.

=FindWord(B5,D5)

Veya

=FindWord(B5,2)

Not: Formülde, D5 N. kelimeyi temsil eden sayıyı içeren hücredir. Alternatif olarak, hücre referansı yerine doğrudan bir sayı yazabilirsiniz.

5. Sonuç hücresini seçin ve diğer hücrelerin metin dizilerinden N. kelimeyi çıkarmak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

2.4.3 Harika bir araç ile N. kelimeyi çıkarma

Yukarıda verilen formülü veya kullanıcı tanımlı fonksiyonu manuel olarak uygulamak istemiyorsanız, burada Kutools for Excel'in "Bir hücreden N. kelimeyi çıkar" aracını öneriyoruz. Bu özellikle, yalnızca birkaç tıklama ile bir hücredeki metin dizisinden kolayca N. kelimeyi çıkarabilirsiniz.

1. Sonucu yerleştirmek için bir hücre seçin ve "Kutools" > "Formül Yardımcısı" > "Metin" > "Bir hücreden N. kelimeyi çıkar"a tıklayın. Ekran görüntüsüne bakın:

2. "Formül Yardımcısı" iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

3. Ardından, N. (ikinci) kelime B5 hücresindeki metin dizisinden çıkarılır ve aynı anda bir formül oluşturulur. Sonuç hücresini seçin ve diğer metin dizilerinden N. kelimeyi almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

Bu özellikle ilgili daha fazla bilgi için tıklayın.

2.5 Bir ayıracın N. tekrarından önce veya sonra metin çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi bir metin dizisi listeniz olduğunu varsayalım. Bir boşluğun ikinci tekrarından önce veya sonra metni çıkarmak için bu bölümde iki formül sunulmaktadır.

2.5.1 Bir ayıracın N. tekrarından önceki metni çıkarma

Excel'de bir hücreden bir ayıracın N. tekrarından önceki metni çıkarmak için LEFT fonksiyonunu SUBSTITUTE ve FIND fonksiyonlarıyla 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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve listedeki diğer sonuçları almak için Otomatik Doldurma Tutamacını aşağıya 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 dizisini içeren hücredir; “ ” burada bir boşluğu temsil eder ve2 sayısı boşluğun ikinci tekrarını belirtir. Bunları ihtiyacınıza göre değiştirebilirsiniz.

2.5.2 Bir ayıracın N. tekrarından sonraki metni çıkarma

Bir ayıracın N. tekrarından sonraki metni çıkarmak için RIGHT fonksiyonunu SUBSTITUTE, LEN ve FIND fonksiyonlarıyla birlikte kullanabilirsiniz.

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 bu formülü, B5:B10 aralığındaki her hücreden boşluğun ikinci tekrarından sonraki metni çıkarmak için uygulayabilirsiniz.

Bir hücre seçin, aşağıdaki formülü girin ve sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer sonuçları almak için Otomatik Doldurma Tutamacını aşağıya 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 metin çıkarma

B sütununda bir sipariş listeniz olduğunu ve her hücreden yalnızca tarih kısmını ve ürün numarası kısmı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 önceki metni çıkarma

Yukarıdaki ekran görüntüsünde görebileceğiniz gibi, tarih kısmı hücredeki ilk satır sonundan önce yer alır. Bu bölüm, hücredeki ilk satır sonundan önceki metni çıkarmak için LEFT fonksiyonunu SEARCH fonksiyonu ile birlikte nasıl kullanacağınızı 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 girin ve sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=LEFT(B5, SEARCH(CHAR(10), B5)-1)

Artık B5:B8 aralığındaki her hücrede ilk satır sonundan önceki metnin çıkarıldığını görebilirsiniz.

Not: Formülde, CHAR(10) Windows'ta bir satır sonunu temsil eder.

2.6.2 Formül ile son satır sonundan sonraki metni çıkarma

Önceki adımda, bir hücredeki ilk satır sonundan önceki metni nasıl çıkaracağımızı konuştuk. Bu bölümde ise, farklı bir formül ile bir hücredeki son satır sonundan sonraki metni nasıl çıkaracağınızı göstereceğiz.

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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=TRIM(RIGHT(SUBSTITUTE(B5,CHAR(10),REPT(" ",200)),200))

Ardından, listedeki her hücrenin ürün numarası kısmı çıkarılır.

Not: Formülde, CHAR(10) Windows'ta bir satır sonunu temsil eder.

 2.7 Bir kelimeden önce veya sonra metin çıkarma

Önceki bölümlerde, bir karakterden veya ayıractan önce veya sonra metin çıkarmayı öğrendik. Peki, bir kelimenin tamamından önce veya sonra metin çıkarmak için ne yapmalısınız? Bu bölüm, bu görevi başarmanıza yardımcı olacak üç yöntem sunacaktır.

2.7.1 Formül ile belirli bir kelimeden önceki metni çıkarma

Aşağıdaki formül, Excel'de bir hücredeki belirli bir kelimeden önceki 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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

Bu örnekte, "Excel" kelimesinden önceki tüm metni çıkarmak istiyoruz, bu nedenle kelimeyi doğrudan formüle yazıp çift tırnak içine alıyoruz. Alternatif olarak, "Excel" kelimesini içeren bir hücreye de başvurabilirsiniz.

=IFERROR(LEFT(B5,FIND("Excel",B5)-1),B5)

Notlar:

2.7.2 Formül ile belirli bir kelimeden sonraki metni çıkarma

Belirli bir kelimeden sonraki metni çıkarmak 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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=TRIM(MID(B5,SEARCH("Excel",B5)+LEN("Excel"),255))

Ardından, her hücredeki "Excel" kelimesinden sonraki tüm metinlerin çıkarıldığını görebilirsiniz.

Notlar:

2.7.3 Harika bir araç ile belirli bir kelimeden önce veya sonra metni çıkarma

Formül kullanmak size zahmetli geliyorsa, burada "Kutools for Excel"in "Metni Çıkart" aracını şiddetle tavsiye ediyoruz. Bu özellik, Excel'de çıkarma işlemini yalnızca birkaç tıklama ile otomatikleştirir.

1. "Kutools" > "Metin" > "Metni Çıkart"a tıklayarak bu özelliği etkinleştirin.

2. "Metni Çıkart" iletişim kutusunda aşağıdaki ayarları yapın.

2.1) "Konuma Göre Çıkart" sekmesinde olduğunuzdan emin olun;

2.2) "Aralık" kutusunda, düğmesine tıklayarak metinleri çıkarmak istediğiniz hücre aralığını seçin;

2.3) "Seçenekler" bölümünde:

Tüm metinleri bir kelimeden önce çıkarmak için "Metinden Önce" radyo düğmesini seçin ve ardından kelimeyi metin kutusuna girin;

Tüm metinleri bir kelimeden sonra çıkarmak için "Metinden Sonra" radyo düğmesini seçin ve ardından kelimeyi metin kutusuna girin.

2.4) "Tamam" düğmesine tıklayın. Ekran görüntüsüne bakın:

Notlar: Dinamik sonuçlar oluşturmak istiyorsanız, "Formül Olarak Ekle" kutusunu işaretleyin. Böylece aralıktaki veriler değiştiğinde sonuçlar otomatik olarak güncellenir.

3. Ardından bir "Metni Çıkart" iletişim kutusu açılır, sonucu çıkış olarak vereceğiniz bir hücre seçmeniz ve ardından "Tamam" düğmesine tıklamanız gerekir.

Ardından, seçili aralıktaki her hücredeki belirli bir kelimeden önce veya sonra metinler anında çıkarılır.

Not: Bu özellik büyük/küçük harf duyarlıdır.

Bu özellikle ilgili daha fazla bilgi için tıklayın.

3. Karakterler/kelimeler arasında çıkarma

Belirli karakterler veya kelimeler arasındaki metni çıkarmak istiyorsanız, aşağıdaki yöntemleri deneyin.

3.1 İki karakter arasındaki metni çıkarma

İki karakter arasındaki metni çıkarmak için, bu karakterler aynı veya farklı olabilir. Bu bölümde birkaç yöntem sunulmuştur, ihtiyacınıza uygun olanı seçebilirsiniz.

 3.1.1 Formül ile aynı iki karakter arasındaki metni çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi, B sütununda bir metin dizisi listesi var ve aralıktaki her hücreden “/” karakterleri arasındaki sayı kısmını çıkarmak istiyorsunuz, aşağıdaki formül işinizi görecektir.

Boş bir hücre seçin, aşağıdaki formülü kopyalayın veya girin ve sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve listedeki diğer hücreler için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=SUBSTITUTE(MID(SUBSTITUTE("/" & B5&REPT(" ",6),"/",REPT(",",255)),2*255,255),",","")

Ardından, aralıktaki her hücreden aynı iki “/” karakteri arasındaki metin çıkarılır. Ekran görüntüsüne bakın:

Not:

 3.1.2 Formül ile farklı iki karakter arasındaki metni çıkarma

Bir hücrede iki aynı karakter arasındaki metni çıkarmayı öğ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 olduğu gibi, B sütunundaki her hücreden “<” ve “>” karakterleri arasındaki e-posta adresini çıkarmak için aşağıdaki adımları izleyebilirsiniz.

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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=MID(LEFT(B5,FIND(">",B5)-1),FIND("<",B5)+1,LEN(B5))

Yalnızca belirtilen karakterler arasındaki metnin çıkarıldığını yukarıdaki ekran görüntüsünde görebilirsiniz.

3.1.3 Harika bir araç ile iki karakter arasındaki metni çıkarma

Burada, "Kutools for Excel"in "Belirtilen metinler arasındaki dizeyi çıkar" özelliğini kullanmanızı öneririz. Bu özellikle, Excel'de bir hücredeki aynı veya farklı iki karakter arasındaki metni kolayca çıkarabilirsiniz.

1. Sonucu çıkış olarak vereceğiniz boş bir hücre seçin ve ardından "Kutools" > "Formül Yardımcısı" > "Formül Yardımcısı"na tıklayın.

2. "Formül Yardımcısı" iletişim kutusunda aşağıdaki ayarları yapın.

3. Ardından, yalnızca B5 hücresindeki “<” ve “>” arasındaki metin çıkarılır. Aynı zamanda bir formül oluşturulur, sonuç hücresini seçip listedeki diğer hücrelerden metin çıkarmak için Otomatik Doldurma Tutamacını aşağıya sürükleyebilirsiniz.

Bu özellikle ilgili daha fazla bilgi için tıklayın.

3.1.4 Kurala göre iki karakter arasındaki metni (karakterler dahil) çıkarma

Çıkardıktan sonra iki karakteri de korumak istiyorsanız, Kutools for Excel'in Metni Çıkart özelliğinde bir kural uygulamayı deneyin.

1. "Kutools" > "Metin" > "Metni Çıkart"a tıklayın.

2. "Metni Çıkart" iletişim kutusunda aşağıdaki ayarları yapın.

2.1) "Kurala Göre Çıkart" sekmesine tıklayın;

2.2) "Aralık" bölümünde, düğmesine tıklayarak karakterler arasındaki metni çıkarmak istediğiniz hücre aralığını seçin;

2.3) "Metin" kutusuna "<*>" girin；

İpuçları: "<" ve ">" metni çıkarmak istediğiniz karakterlerdir, * ise herhangi bir karakter sayısını temsil eden joker karakterdir. Koşulu ihtiyacınıza göre girebilirsiniz.

2.4) "Ekle" düğmesine tıklayarak koşulu "Kural İçeriği" kutusuna ekleyin;

2.5) "Tamam" düğmesine tıklayın. Ekran görüntüsüne bakın:

3. Başka bir "Metni Çıkart" iletişim kutusu açılır. Lütfen sonucu çıkış olarak vereceğiniz bir hücre seçin ve ardından "Tamam" düğmesine tıklayın.

Ardından, belirtilen karakterler arasındaki (karakterler dahil) metin, seçili aralıktaki her hücreden toplu olarak çıkarılır.

3.2 İki kelime arasındaki metni çıkarma

İki karakter arasındaki metni çıkarmanın yanı sıra, iki kelime arasındaki metni de çıkarmanız gerekebilir. Örneğin, aşağıdaki ekran görüntüsünde olduğu gibi, B sütunundaki her hücreden “KTE” ve “feature” kelimeleri arasındaki tüm metin dizilerini çıkarmak için aşağıdaki yöntemlerden birini deneyebilirsiniz.

3.2.1 Formül ile iki kelime arasındaki metni çıkarma

Bir hücredeki iki kelime arasındaki tüm metin dizilerini çıkarmak için MID ve SEARCH fonksiyonlarına 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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya 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;4 sayısı ise "KTE" kelimesinin karakter uzunluğu artı1'i temsil eder.

B sütunundaki her hücreden belirtilen iki kelime arasındaki tüm metin dizilerinin çıkarıldığını görebilirsiniz.

3.2.2 Harika bir araç ile iki kelime arasındaki metni çıkarma

Birçok Excel kullanıcısı için formüller hatırlaması ve uygulaması zor olabilir. Burada, "Kutools for Excel"in "Belirtilen metinler arasındaki dizeyi çıkar" özelliğiyle, yalnızca birkaç tıklama ile iki kelime arasındaki metni kolayca çıkarabilirsiniz.

1. Sonucu çıkış olarak vereceğiniz bir hücre seçin ve ardından "Kutools" > "Formül Yardımcısı" > "Formül Yardımcısı"na tıklayın.

2. "Formül Yardımcısı" iletişim kutusunda aşağıdaki ayarları yapmanız gerekir.

3. Ardından, B5 hücresindeki “KTE” ve “feature” kelimeleri arasındaki tüm metin dizileri çıkarılır. Aynı zamanda bir formül oluşturulur, sonuç hücresini seçip listedeki diğer hücrelerden metin çıkarmak için Otomatik Doldurma Tutamacını aşağıya sürükleyebilirsiniz.

Konuma göre sayıları çıkarma

Alfasayısal dizelerden oluşan bir liste için üç durum olabilir:

1. Sayı metnin başında yer alır;
2. Sayı metnin sonunda yer alır;
3. Sayı metnin herhangi bir yerinde olabilir.

Bu bölümde, yukarıda belirtilen her bir durumda sayıları çıkarmak için kullanılabilecek farklı yöntemler sunacağız.

1 Bir dizenin solundan sayı çıkarma

Bu bölümde, bir hücredeki metinden önce gelen 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 sonucu almak için "Ctrl" + "Shift" + "Enter" veya "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer hücrelerin sayılarını almak için Otomatik Doldurma Tutamacını aşağıya 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ı çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi, bir hücrede metinden sonra gelen 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 sonucu almak için "Ctrl" + "Shift" + "Enter" veya "Enter" tuşuna basın. Sonuç hücresini seçin ve diğer hücrelerin sayılarını almak için Otomatik Doldurma Tutamacını aşağıya 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 dizisindeki herhangi bir konumdan tüm sayıları çıkarma

Yukarıdaki yöntemler, bir metin dizisinin yalnızca solundan veya sağından sayı çıkarmaya yardımcı olur. Bir metin dizisinin herhangi bir yerinden tüm sayıları çıkarmak istiyorsanız, burada bunu yapmanız için üç yöntem sunulmaktadır.

3.1 Formül ile bir dizenin herhangi bir yerinden tüm sayıları çıkarma

Excel'de bir metin dizisinin herhangi bir yerinden tüm sayıları çı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 B5 hücresindeki tüm sayıları almak için "Enter" tuşuna basın.

=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 diğer hücrelerin tüm sayılarını almak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

3.2 VBA ile bir dizenin herhangi bir yerinden tüm sayıları çıkarma

Yukarıdaki formül birçok Excel kullanıcısı için çok uzun ve karmaşık olabilir. Aslında, Excel'de bu işlemi otomatikleştirmek için bir VBA betiği çalıştırabilirsiniz. Aşağıdaki adımları izleyebilirsiniz.

1. "Microsoft Visual Basic for Applications" penceresini açmak için "Alt" + "F11" tuşlarına basın.

2. Açılan "Microsoft Visual Basic for Applications" penceresinde, "Ekle" > "Modül"e tıklayın. Ardından aşağıdaki VBA kodunu Modül kod penceresine kopyalayın.

VBA kodu: Bir metin dizisinin herhangi bir yerinden tüm sayıları çıkarma

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. "F5" tuşuna basarak kodu çalıştırın. Açılan "KutoolsforExcel" iletişim kutusunda, her hücreden tüm sayıları çıkarmak istediğiniz hücre aralığını seçin ve ardından "Tamam" düğmesine tıklayın.

4. Ardından başka bir "KutoolsforExcel" iletişim kutusu açılır. Bu kutuda bir hedef hücre seçin ve "Tamam"a tıklayın.

Ardından, seçili aralıktaki her hücreden tüm sayılar toplu olarak çıkarılır.

4. Belirli bir metinden sonra sayı çıkarma

Aşağıdaki ekran görüntüsünde olduğu gibi, "No." ifadesinden sonraki herhangi bir sayıyı çıkarmak için bu bölümde iki yöntem sunulmaktadır.

4.1 Formül ile belirli bir metinden sonra sayı çıkarma

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 sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya 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ı fonksiyon ile belirli bir metinden sonra sayı çıkarma

Aşağıdaki kullanıcı tanımlı fonksiyon da bir hücredeki belirli bir metinden sonra sayıları çıkarmaya yardımcı olabilir. Lütfen aşağıdaki adımları izleyin.

1. "Microsoft Visual Basic for Applications" penceresini açmak için "Alt" + "F11" tuşlarına basın.

2. "Microsoft Visual Basic for Applications" penceresinde, "Ekle" > "Modül"e tıklayın ve ardından aşağıdaki VBA kodunu Modül kod penceresine kopyalayın.

VBA kodu: Bir hücredeki belirli bir metinden sonra sayı çıkarma

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. "Microsoft Visual Basic for Applications" penceresini kapatmak için "Alt" + "Q" tuşlarına basın.

4. Bir hücre seçin, aşağıdaki formülü girin ve sonucu almak için "Enter" tuşuna basın. Sonuç hücresini seçin ve bu formülü diğer hücrelere uygulamak için Otomatik Doldurma Tutamacını aşağıya sürükleyin.

=GetNumberAfterTheChar(B5,"No. ")

Notlar:

İlgili makaleler: