Excel Alt Dize Teknikleri: Metni Çıkarın ve Biçimlendirin

Excel Alt Dize İşlevlerini Kullanarak Konuma Göre Metin Çıkarma

Çoğu zaman, konumlarına göre bir kelime veya karakter dizisi gibi bir metin dizisinin belirli kısımlarını çıkarmak isteriz. Excel, metin dizelerinin belirli bölümlerini çıkarmanızı sağlayan birkaç yerleşik işlev sunar. En yaygın işlevlerden bazıları LEFT(), RIGHT(), MID(), TEXTBEFORE() ve TEXTAFTER()'dır. Her birine bakalım. 

LEFT() Excel alt dize işlevini kullanma

LEFT() işlevini, bir metin dizesinin başından itibaren karakterleri çıkarmak için kullanabilirsiniz. Sözdizimi şöyledir:

=LEFT(text, [num_chars])

Burada: 

• text, özgün metin dizesinin bulunduğu hücre adresini ifade eder.

• num_chars, çıkarılacak karakter sayısını ifade eder. Varsayılan değeri 1'dir.

Bir örneğe bakalım. Burada bazı ürün kodlarım var ve bunlardan ilk üç karakteri çıkarmak istiyorum. 

=LEFT(A2,3)

LEFT() işlevini kullanarak ilk üç karakteri alın. Görsel: Yazar.

RIGHT() Excel alt dize işlevini kullanma

Excel'deki RIGHT() işlevi, bir metin dizesinin sonundan itibaren karakterleri çıkarır. Sözdizimi şöyledir:

=Right(text, [num_chars])

Burada: 

• text, özgün metin dizesinin bulunduğu hücre adresini ifade eder.

• num_chars, çıkarılacak karakter sayısını ifade eder. Varsayılan değeri 1'dir.

Bir örneğe bakalım. Burada, son üç karakteri çıkarmak için RIGHT() işlevini kullanıyorum.

=RIGHT(A2, 3)

RIGHT() işlevini kullanarak son üç karakteri alın. Görsel: Yazar.

MID() Excel alt dize işlevini kullanma

MID() işlevi, bir metin dizesinin ortasından karakterler çıkarır. Sözdizimi şöyledir:

=MID(text, start_num, num_chars)

Burada:

• text, özgün metin dizesinin bulunduğu hücre adresini ifade eder.

• num_chars, çıkarılacak karakter sayısını ifade eder. Varsayılan değeri 1'dir.

• start_num, metin dizesi içindeki başlangıç konumunu ifade eder.

Bu örnekte, dizede dördüncü karakterden başlayarak ortadaki değeri çıkarmak için MID() formülünü kullanıyorum.

=MID(A2,4,3)

MID() işlevini kullanarak ortadaki üç karakteri alın. Görsel: Yazar. 

Ve bu kadar. =MID(A2,4,3), A2 hücresindeki metinden 4. karakterden başlayarak üç karakter çıkarır.

FIND() Excel alt dize işlevini kullanma

Excel'deki FIND() işlevi, bir metin dizesi içinde bir alt dizeyi bulur ve alt dizenin ilk karakterinin konumunu döndürür. Sözdizimi şöyledir:

FIND(find_text, within_text, [start_num]) 

Burada:

• find_text, aramak istediğimiz metni ifade eder.

• within_text, aradığımız metni içeren metindir.

• start_num, aramaya başlanacak konumu belirtir; varsayılanı 1'dir.

Excel'in FIND() işlevinin nasıl çalıştığını göstermek için basit bir veri kümesini ele alalım. Bu örnekte, Text sütununda DataCamp adı var ve Camp alt dizisinin konumunu bulmak istiyorum. Bu, Camp metni beşinci karakterden başladığı için 5 döndürür.

=FIND("Camp", A2)

Metnin konumunu çıkarmak için FIND() işlevini kullanma. Görsel: Yazar.

SUBSTITUTE() Excel alt dize işlevini kullanma

SUBSTITUTE() işlevi, belirli bir alt diziyi bir başkasıyla değiştirir. Sözdizimi şöyledir:

 SUBSTITUTE(text, old_text, new_text, [instance_num])

Burada:

• text, değiştirmek istediğiniz metni içerir.

• old_text, değiştirmek istediğiniz metni ifade eder.

• new_text, eski metnin yerine koymak istediğiniz metindir.

• instance_num, hangi yinelemenin değiştirileceğini belirtir. Belirtilmezse tüm yinelemeler değiştirilir. 

Bunu anlamak için, belirli ayırıcılar içeren metin dizelerini barındıran basit bir veri kümesine bakalım. Text sütunu, ayırıcı olarak noktalı virgülle biçimlendirilmiş adlar ve yaşları listeler. Bunu temizlemek için şu formülü yazıyorum:

=SUBSTITUTE(A2, ";" , ",")

Burada:

• A2 özgün metni içeren hücreyi ifade eder.

• ";" değiştireceğimiz eski metni belirtir.

• "," eski metnin yerine koymak istediğimiz metindir.

Eski metni değiştirmek için substitute işlevini kullanma. Görsel: Yazar.

Ve bu kadar. Formülümüzü B2 hücresine uyguladığımızda Excel, noktalı virgülü virgülle değiştirir.

Excel Alt Dize İşlevlerini Kullanarak Ayırıcıya Göre Metin Çıkarma

Ayırıcılar, iki metin dizesini ayıran belirli karakterlerdir; örneğin noktalı virgül veya virgül. Excel'de, ayırıcılar mevcut olduğunda metni çıkarmak için TEXTBEFORE() ve TEXTAFTER() işlevlerini kullanabilirsiniz.  

TEXTBEFORE() Excel alt dize işlevini kullanma 

Adından da anlaşılacağı gibi, TEXTBEFORE() işlevi belirtilen bir karakterin veya alt dizenin önündeki metni çıkarır. Sözdizimi şöyledir:

=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Burada: 

• text özgün metni ifade eder.

• delimiter genellikle bir virgül veya noktalı virgüldür.

• instance_num, ayırıcının istenen yinelemesidir. Varsayılan değeri 1'dir.

• match_mode, ayırıcı aramasının büyük/küçük harf duyarlı (TRUE, varsayılan) mı yoksa duyarsız (FALSE) mı olacağını belirtir.

• match_end , metin dizesinin sonunun ayırıcı olarak değerlendirilip değerlendirilmeyeceğini belirler. TRUE ise, ayırıcı bulunamazsa işlev özgün metni döndürür.

• if_not_found, ayırıcı bulunamazsa döndürülecek özel bir değeri belirtir.

Şimdi bu formülün nasıl çalıştığını görmek için bir örnek yapalım. Text sütununda çalışanların adları, departmanları ve şube konumları dahil bilgileri var. Buradan yalnızca adları ve departmanları çıkarmak istiyorum.

=TEXTBEFORE(A2, ",", 2, 1, 1)

Veri çıkarmak için TEXTBEFORE() işlevini kullanma. Görsel: Yazar 

Bu şöyle çalışır: 

• A2, içinden metin çıkaracağım hücredir. 

• "," özgün verimdeki metni bölen ayırıcıdır. 

• 2 işlevin ayırıcının ikinci yinelemesini dikkate alacağı anlamına gelir.

• 1 büyük/küçük harf duyarlı örnekleri (varsa) yok sayar.

• Sonundaki 1, ayırıcı yoksa özgün metni döndürür.

TEXTAFTER() Excel alt dize işlevini kullanma

TEXTAFTER() işlevi, TEXTBEFORE() işlevine benzer—tek fark sonuçtadır. TEXTAFTER() işlevi, ayırıcıdan sonra gelen metni çıkarır. Sözdizimi şöyledir:

=TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found]

Burada: 

• text özgün metni ifade eder.

• delimiter bir virgül veya noktalı virgüldür. 

• instance_num, ayırıcının istenen yinelemesidir. Varsayılan değeri 1'dir.

• match_mode, ayırıcı aramasının büyük/küçük harf duyarlı (TRUE, varsayılan) mı yoksa duyarsız (FALSE) mı olacağını belirtir.

• match_end , metin dizesinin sonunun ayırıcı olarak değerlendirilip değerlendirilmeyeceğini belirler. TRUE ise, ayırıcı bulunamazsa işlev özgün metni döndürür.

• if_not_found, ayırıcı bulunamazsa döndürülecek özel bir değeri belirtir.

Burada, çalışanların adları, departmanları ve şube konumları dahil bilgileri içeren bir Text sütunum var. Buradan çalışanların konumlarını ve kimlik numaralarını çıkarmak istiyorum. 

=TEXTAFTER(A2,",",2,1,1)

Veri çıkarmak için TEXTAFTER() işlevini kullanma. Görsel: Yazar 

Bu şöyle çalışır: 

• A2, içinden metin çıkaracağım hücredir. 

• ","  özgün verimdeki metni bölen ayırıcıdır. 

• 2 işlevin ayırıcının ikinci yinelemesini dikkate alacağı anlamına gelir. 

• 1 büyük/küçük harf duyarlı örnekleri (varsa) yok sayar.

• 1 (sonda) ayırıcı yoksa özgün metni döndürür.

Belirli Excel Alt Dize Kullanım Senaryoları

Artık alt dizilerin temellerini bildiğinize göre, gelişmiş yöntemleri öğrenme zamanı. Bu yöntemler, daha karmaşık metin işlemlerine olanak tanıyarak veri işleme görevlerinizi daha da verimli hale getirir. 

Farklı uzunluktaki adlarla başa çıkma

Tüm isimlerin yer aldığı bir veri kümem var, ancak yalnızca ilk adı çıkarmak istiyorum.

Tam adları içeren bir tablo. Görsel: Yazar.

Muhtemelen bunun basit olduğunu düşünüyorsunuz—ilk adı almak için LEFT() işlevini kullanın. Ben de şu formülle denedim:

=LEFT(A2,4)

İlk adı almak için LEFT() işlevini kullanma. Görsel: Yazar.

Ancak ilk adı almak yerine, num_char değerini 4 olarak belirlediğim için adın ilk dört karakterini aldım; Jane'in adında dört karakter olduğunu görmüştüm. Formülü kopyalayıp yapıştırdığımda, diğer adlar için istenen çıktıyı vermiyor.

Daha esnek bir çözüme ihtiyacım var. Uzunluktan bağımsız olarak ilk adı çıkarmak için LEFT() işlevini FIND() işleviyle birleştirebilirsiniz. Tam ad içindeki ilk boşluğun konumunu bulmak için FIND() işlevini kullanıyorum; bu boşluk, ad ve soyadı ayırır. Ardından, o konuma kadar olan karakterleri çıkarmak için LEFT() işlevini kullanıyorum. 

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

İlk adı çıkarmak için FIND() ve LEFT() işlevlerini birleştirin. Görsel: Yazar.

Formülü nasıl çalıştığını anlamanıza yardımcı olmak için açıklayayım:

• A2 hücresi tam adı içerir.

• FIND(" ", A2, 1), A2 hücresindeki metin dizesinde ilk karakterden başlayarak ilk boşluğun konumunu bulur .

• -1 boşluğu kaldırmak için kullanılır.

• LEFT(A2, ...), metin dizesinin solundan belirtilen sayıda karakteri çıkarır.

E-posta adresinden etki alanı çıkarma

Başka bir örnek daha yapalım; bakalım LEFT() ve FIND() farklı bir senaryonun üstesinden gelebiliyor mu. E-posta adreslerinden oluşan farklı bir veri kümem var ve buradan etki alanı adını çıkarmam gerekiyor.

E-posta adresleri içeren veriler. Görsel: Yazar.

Bunu yapmak için Email Domain adlı farklı bir sütun oluşturuyor ve aşağıdaki formülü yazmak için farklı bir hücre seçiyorum; ardından bunu diğer üç hücreye kopyalıyorum:

=RIGHT(A2, LEN(A2) - FIND("@",A2))

Etki alanı adını RIGHT(), LEN() ve FIND() ile çıkarın. Görsel: Yazar.

İşte böyle—tüm etki alanı adlarını aldım. Ancak Excel'in istediğim sonuçları nasıl elde ettiğini anlayalım:

• FIND("@", A2), e-posta adresindeki @ sembolünün konumunu bulur.

• LEN(A2), e-posta adresinin toplam uzunluğunu hesaplar.

• LEN(A2) - FIND("@", A2), @ sembolünden sonraki karakter sayısını hesaplar.

• Son olarak RIGHT(A2, LEN(A2) - FIND("@", A2)), dizenin sağ ucundan bu sayıda karakteri çıkarır.

LEFT(), MID() ve RIGHT() işlevlerini birleştirerek ürün kodları çıkarma

Şimdiye kadar, LEFT(), MID() ve RIGHT() işlevlerinin ayrı ayrı nasıl çalıştığını açıkladım. Ancak bu işlevleri birleştirerek çok daha fazlasını yapabilirsiniz. Belirli desenlere göre bir metin dizesinin farklı bölümlerini çıkarabilirler.

Burada, bir liste ürün kodum var ve bunlardan belirli bir bölümü çıkarmak istiyorum. 

• İlk bölümden bir karakter istiyorum.
• İkinci bölümden tüm dört karakteri istiyorum.
• Son bölümden son üç karakteri çıkarmak istiyorum.

Bir ürün kodu listesi. Görsel: Yazar.

Bunu başarmak için LEFT(), RIGHT() ve MID() işlevlerini birleştiriyor ve şu formülü giriyorum:  

=LEFT(A2, 1) & "-"& MID(A2, FIND("-", A2) + 1, 4) & "-" & RIGHT(A2, 3)

Veri almak için MID(), LEFT() ve RIGHT() işlevlerini birleştirin. Görsel: Yazar.

Gördüğünüz gibi, bu birleşik formül kodların her bölümünden istenen karakterleri çıkardı. Şöyle:

• LEFT(A2, 1) ilk karakteri çıkarır.

• MID(A2, FIND("-", A2) + 1, 4) ilk kısa çizgiyi bulur, bir karakter sağa gider ve dört karakter çıkarır.

• "-" bir kısa çizgi ekler.

• RIGHT(A2, 3) son üç karakteri çıkarır.

• & karakterleri birleştirir. 

Dinamik metin çıkarımı için SUBSTITUTE() ile MID() kullanma 

Şimdi, bir metin dizesinin belirli bölümlerini çıkarmak ve ardından çıkarılan bölüm içindeki karakterleri veya alt dizeleri değiştirmek için MID() işlevini SUBSTITUTE() işleviyle nasıl birleştirebileceğinize bakalım.

Örneğin, müşteri Sipariş Ayrıntılarım var ve sipariş kimliğini Order confirmed metniyle değiştirmek istiyorum.

Sipariş ayrıntılarını içeren bir tablo. Görsel: Yazar.

Şu formülü kullanıyorum:

=SUBSTITUTE(A2, MID(A2,1,19), "Order confirmed")

Metni değiştirmek için SUBSTITUTE() ve MID() işlevleri. Görsel: Yazar.

Hepsi bu! Bu formülün, istediğim gibi metin dizelerinin belirli bölümlerini çıkardığını görebilirsiniz.

• MID(A2, 1, 19), A2 hücresindeki metinden ilk 19 karakteri çıkarır.

• SUBSTITUTE(text, old_text, new_text), old_text oluşumlarını new_text ile değiştirir.

Başka bir örneğe bakalım: Burada bir Product Detail sütunum var ve köşeli parantez içinden ürün boyutunu çıkarmak istiyorum.

=SUBSTITUTE(MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), "[", "")

Köşeli parantez içinden ürün boyutunu çıkarma. Görsel: Yazar.

Ve oldu—tüm ayrıntıları çıkardı.

• FIND("[", A2), açılış köşeli parantezinin başlangıç konumunu bulur.

• FIND("]", A2), kapanış köşeli parantezinin bitiş konumunu bulur.

• MID(A2, FIND("[", A2)+1, FIND("]", A2)-FIND("[", A2)-1), iki parantez arasındaki metni çıkarır.

• SUBSTITUTE(...,"[", ""), çıkarılan metinden açılış köşeli parantezini kaldırır.

Veri girişlerini temizleme

Bazen verilerimiz dağınıktır, ancak bu tür verileri işlemlerinizde kullanmak zorunda değilsiniz. Burada, bir liste telefon numarası birden çok biçimde var, ancak girişlerdeki tutarsızlıkları temizleyerek bunları tutarlı bir biçimde standartlaştırmak istiyorum.

Biçimlendirilmemiş telefon numaraları. Görsel: Yazar.

Bunu standartlaştırmak için Clean data adlı farklı bir sütun oluşturuyorum. İkinci hücreye aşağıdaki formülü yazıyorum ve sonucu görmek istediğim tüm hücrelere kopyalıyorum:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "-", ""), "(", ""), ")", ""), ".", "")

Burada: 

• SUBSTITUTE(A2, "-", "") kısa çizgileri kaldırır.

• SUBSTITUTE(..., "(", "") parantezleri kaldırır.

• SUBSTITUTE(..., ")", "") kapanış parantezlerini kaldırır.

• SUBSTITUTE(..., ".", "") noktaları kaldırır.

Bu, birden çok biçimi aşağıda gösterildiği gibi sürekli bir rakam dizisine dönüştürür. Artık veriler temizlenmiş, güzelce biçimlendirilmiş ve kullanıma hazır. İsterseniz bu şekilde bırakabilirsiniz.

SUBSTITUTE() işlevini kullanarak verileri temizleyin. Görsel: Yazar.

Raporlar için metni biçimlendirme

Veri temizlemeden sonra bir adım daha ileri gidip uygun şekilde biçimlendirmek isterseniz, farklı alt dize işlevlerini de kullanabilirsiniz.

Önceki örnekte telefon numaralarını temizlemiştim. Şimdi, numaraların biçimini değiştirerek sadece bir rakam dizisi gibi değil, daha çok telefon numarası gibi görünmelerini istiyorum. 

=TEXT(LEFT(B2, 3), "000") & "-" & TEXT(MID(B2, 4, 3), "000") & "-" & TEXT(RIGHT(B2, 4), "0000")

Birden çok alt dize kullanılarak standartlaştırılmış sayı biçimi. Görsel: Yazar.

Ve oldu. Artık istediğim tüm numaralara sahibim. Bu formül şöyle çalıştı:

• LEFT(B2, 3) ilk üç rakamı çıkarır.

• MID(B2, 4, 3), dördüncü konumdan başlayarak sonraki üç rakamı çıkarır.

• RIGHT(B2, 4) son dört rakamı çıkarır.

• TEXT(..., "000") ve TEXT(..., "0000") her bölümü, gerekirse baştaki sıfırları da içerecek şekilde doğru hane sayısıyla biçimlendirir.

• & "-" & biçimlendirilmiş bölümleri kısa çizgilerle birleştirir.

Son Düşünceler

Artık Excel'in LEFT(), RIGHT(), MID(), TEXTBEFORE() ve TEXTAFTER() gibi alt dize işlevlerini metin verilerini işlemek için nasıl kullanacağınızı öğrendiniz. İster bir dizenin belirli kısımlarını çekmek ister dağınık verileri temizlemek olsun, bu işlevler çalışmanızı çok daha kolay ve verimli hale getirebilir.

Ancak Excel ile öğrenecek her zaman daha fazlası var. Yeni başlıyorsanız, Introduction to Excel kursu mükemmel bir sonraki adımdır. Veri analizi hakkında daha fazla öğrenmek isterseniz, Data Analysis in Excel kursunu ve buna çok yakışan Data Manipulation in Excel Cheat Sheet'i deneyin. Bunlar güçlü bir temel oluşturmanıza ve becerilerinizi bir üst seviyeye taşımanıza yardımcı olacaktır.