Excel, harikalar yaratabilecek bir dizi METİN İşlevine sahiptir. Bu fonksiyonları kullanarak her türlü metin dilimleme ve zar işlemlerini yapabilirsiniz.
Metin verileriyle çalışan kişilerin ortak görevlerinden biri, Excel'de bir alt dize çıkarmaktır (yani, bir hücreden metnin psrt'sini almak).
Ne yazık ki, Excel'de bunu kolayca yapabilen bir alt dize işlevi yoktur. Ancak, bu yine de metin formülleri ve diğer bazı yerleşik Excel özellikleri kullanılarak yapılabilir.
Önce bu eğitimde kullanacağımız bazı metin işlevlerine bir göz atalım.
Excel METİN İşlevleri
Excel, Excel'deki orijinal metinden bir alt dize çıkarmayı gerçekten kolaylaştıracak bir dizi metin işlevine sahiptir. Bu eğitimde kullanacağımız Excel Metin işlevleri şunlardır:
- SAĞ işlevi: Metin dizesinin sağından belirtilen sayıda karakteri çıkarır.
- LEFT işlevi: Metin dizesinin solundan belirtilen sayıda karakteri çıkarır.
- MID işlevi: Bir metin dizesinde belirtilen başlangıç konumundan belirtilen sayıda karakteri çıkarır.
- BUL işlevi: Metin dizesinde belirtilen metnin başlangıç konumunu bulur.
- UZUNLUK işlevi: Metin dizesindeki karakter sayısını döndürür.
İşlevleri Kullanarak Excel'de Bir Alt Dize Ayıklayın
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu varsayalım:
Bunlar bazı rastgele (ama süper kahramanvari) e-posta kimlikleridir (benimki hariç) ve aşağıdaki örneklerde, Excel'deki Metin işlevlerini kullanarak kullanıcı adını ve alan adını nasıl çıkaracağınızı göstereceğim.
Örnek 1 - Kullanıcı Adlarını E-posta Kimliklerinden Çıkarma
Metin işlevlerini kullanırken, bir desen (varsa) belirlemek önemlidir. Bu, bir formül oluşturmayı gerçekten kolaylaştırır. Yukarıdaki durumda, kalıp, kullanıcı adı ile alan adı arasındaki @ işaretidir ve kullanıcı adlarını almak için bunu referans olarak kullanacağız.
Kullanıcı adını almak için formül:
=SOL(A2,BUL("@",A2)-1)
Yukarıdaki formül, kimlikteki @ işaretinin konumunu belirleyerek kullanıcı adını çıkarmak için SOL işlevini kullanır. Bu, @ konumunu döndüren BUL işlevi kullanılarak yapılır.
Örneğin, [email protected] durumunda, BUL(“@”,A2) metin dizesindeki konumu olan 11 değerini döndürür.
Şimdi, dizgenin solundan 10 karakter çıkarmak için LEFT işlevini kullanıyoruz (LEFT işlevi tarafından döndürülen değerden bir eksik).
Örnek 2 - Alan Adını E-posta Kimliklerinden Çıkarma
Yukarıdaki örnekte kullanılan mantık, alan adını almak için kullanılabilir. Buradaki küçük bir fark, karakterleri metin dizesinin sağından çıkarmamız gerektiğidir.
İşte bunu yapacak formül:
=SAĞ(A2,UZUNLUK(A2)-BUL("@",A2))
Yukarıdaki formülde aynı mantığı kullanıyoruz, ancak doğru dizgiyi aldığımızdan emin olmak için onu ayarlıyoruz.
Yine [email protected] örneğini ele alalım. FIND işlevi, bu durumda 11 olan @ işaretinin konumunu döndürür. Şimdi @ işaretinden sonraki tüm karakterleri çıkarmamız gerekiyor. Böylece dizenin toplam uzunluğunu belirleriz ve @'ye kadar olan karakter sayısını çıkarırız. Bize sağdaki alan adını kapsayan karakter sayısını verir.
Artık alan adını almak için SAĞ işlevini kullanabiliriz.
Örnek 3 - Alan Adını E-posta Kimliklerinden Çıkarma (.com olmadan)
Bir metin dizesinin ortasından bir alt dize çıkarmak için, işaretçinin alt dizeden hemen önce ve sonra konumunu belirlemeniz gerekir.
Örneğin, aşağıdaki örnekte, .com kısmı olmadan alan adını almak için işaretçi @ (alan adından hemen öncedir) ve . (ki ondan hemen sonra).
İşte sadece alan adını çıkaracak formül:
=ORTA(A2,BUL("@",A2)+1,BUL(".",A2)-BUL("@",A2)-1)
Excel MID işlevi, belirtilen başlangıç konumundan belirtilen sayıda karakteri çıkarır. Yukarıdaki bu örnekte FIND(“@”,A2)+1 başlangıç konumunu belirtir (@ işaretinden hemen sonradır) ve FIND(“.”,A2)-BUL(“@”,A2)-1 başlangıç konumunu belirtir. arasındaki karakter sayısı '@' ve '.‘
Güncelleme: Okuyuculardan biri William19, e-posta kimliğinde nokta(.) olması durumunda (örneğin, [email protected]) yukarıdaki formülün çalışmayacağını belirtti. İşte bu gibi durumlarla başa çıkmanın formülü:
=ORTA(A1,BUL("@",A1)+1,BUL(".",A1,BUL("@",A1))-BUL("@",A1)-1)
Excel'de Bir Alt Dize Ayıklamak için Metni Sütunlara Dönüştürme
Excel'de bir alt diziyi ayıklamak için işlevlerin kullanılması, dinamik olma avantajına sahiptir. Orijinal metni değiştirirseniz, formül sonuçları otomatik olarak günceller.
Bu, ihtiyacınız olmayan bir şeyse, Metni Sütunlara Dönüştür özelliğini kullanmak, metni belirtilen işaretçilere göre alt dizelere bölmenin hızlı ve kolay bir yolu olabilir.
Bunu nasıl yapacağınız aşağıda açıklanmıştır:
- Metnin bulunduğu hücreleri seçin.
- Veri -> Veri Araçları -> Metni Sütunlara Dönüştür seçeneğine gidin.
- Metni Sütuna Çevirme Sihirbazı Adım 1'de, Sınırlandırılmış'ı seçin ve İleri'ye basın.
- 2. Adımda, Diğer seçeneğini işaretleyin ve sağdaki kutuya @ girin. Bu, Excel'in metni alt dizelere bölmek için kullanacağı sınırlayıcımız olacaktır. Aşağıda Veri önizlemesini görebilirsiniz. İleri'ye tıklayın.
- 3. Adımda, Genel ayar bu durumda iyi çalışır. Ancak sayıları/tarihleri bölüyorsanız farklı bir biçim seçebilirsiniz. Varsayılan olarak, hedef hücre, orijinal verilere sahip olduğunuz yerdir. Orijinal verileri olduğu gibi tutmak istiyorsanız, bunu başka bir hücreyle değiştirin.
- Bitir'e tıklayın.
Bu, bu örnekte kullanılan her e-posta kimliği için size anında iki alt dizi seti verecektir.
Metni daha fazla bölmek istiyorsanız (örneğin, batman.com'u batman ve com'a ayırın), onunla aynı işlemi tekrarlayın.
Excel'de Bir Hücreden Metin Çıkarmak için BUL ve DEĞİŞTİR'i Kullanma
BUL ve DEĞİŞTİR, Excel'de metinle çalışırken güçlü bir teknik olabilir. Aşağıdaki örneklerde, Excel'de harika şeyler yapmak için joker karakterlerle BUL ve DEĞİŞTİR'i nasıl kullanacağınızı öğreneceksiniz.
Ayrıca bakınız: Excel'de Joker Karakterler Hakkında Her Şeyi Öğrenin.
Aynı E-posta kimliği örneklerini ele alalım.
Örnek 1 - Kullanıcı Adlarını E-posta Kimliklerinden Çıkarma
Bul ve Değiştir işlevini kullanarak E-posta Kimliklerinden kullanıcı adlarını çıkarma adımları şunlardır:
- Orijinal verileri kopyalayıp yapıştırın. Bul ve Değiştir, uygulandığı verileri çalıştığı ve değiştirdiği için, orijinal verilerin bir yedeğini almak en iyisidir.
- Verileri seçin ve Ana Sayfa -> Düzenleme -> Bul ve Seç -> Değiştir seçeneğine gidin (veya Ctrl + H klavye kısayolunu kullanın).
- Bul ve Değiştir iletişim kutusuna aşağıdakileri girin:
- Ne buldun: @*
- Şununla değiştirin: (bunu boş bırakın)
- Tümünü Değiştir'e tıklayın.
Bu, e-posta kimliklerinde @'den önceki tüm metni anında kaldıracaktır. Sonucu aşağıda gösterildiği gibi alacaksınız:
Bu nasıl çalışır? - Yukarıdaki örnekte @ ve * kombinasyonunu kullandık. Yıldız işareti (*), herhangi bir sayıda karakteri temsil eden bir joker karakterdir. Bu nedenle, @*, @ ile başlayan ve ondan sonra herhangi bir sayıda karakter alabilen bir metin dizesi anlamına gelir. Örneğin, [email protected]'da @*, @batman.com olur. @*'ı boş ile değiştirdiğimizde, @'den sonra (@ dahil) tüm karakterleri kaldırır.
Örnek 2 - Alan Adını E-posta Kimliklerinden Çıkarma
Aynı mantığı kullanarak, alan adını almak için 'Neyi bul' kriterlerini değiştirebilirsiniz.
İşte adımlar:
- Verileri seçin.
- Ana Sayfa -> Düzenleme -> Bul ve Seç -> Değiştir seçeneğine gidin (veya Ctrl + H klavye kısayolunu kullanın).
- Bul ve Değiştir iletişim kutusuna aşağıdakileri girin:
- Ne buldun: *@
- Şununla değiştirin: (bunu boş bırakın)
- Tümünü Değiştir'e tıklayın.
Bu, e-posta kimliklerinde @'den önceki tüm metni anında kaldıracaktır. Sonucu aşağıda gösterildiği gibi alacaksınız:
