Excel'de metin verileriyle çalışırken, belirli bir karakter veya metin dizesinden önce veya sonra metni kaldırmanız gerekebilir.
Örneğin, kişilerin ad ve atama verilerine sahipseniz, virgülden sonraki atamayı kaldırmak ve yalnızca adı saklamak (veya tam tersi, atamayı sakladığınız ve adı kaldırdığınız yerde) isteyebilirsiniz.
Bazen basit bir formülle veya hızlı Bul ve Değiştir ile yapılabilir ve bazen daha karmaşık formüllere veya geçici çözümlere ihtiyaç duyar.
Bu derste, Excel'de belirli bir karakterden önce veya sonra metnin nasıl kaldırılacağını (farklı örnekler kullanarak) göstereceğim.
O halde bazı basit örneklerle başlayalım.
Bul ve Değiştir'i Kullanarak Bir Karakterden Sonra Metni Kaldırma
Belirli bir metin dizesinden sonra (veya bir metin dizesinden önce) tüm metni hızlı bir şekilde kaldırmak istiyorsanız, bunu Bul ve Değiştir ve joker karakterleri kullanarak yapabilirsiniz.
Aşağıda gösterildiği gibi bir veri kümeniz olduğunu ve virgül karakterinden sonraki gösterimi kaldırmak ve metni virgülden önce tutmak istediğinizi varsayalım.
Bunu yapmak için gereken adımlar aşağıdadır:
- A sütunundan B sütununa verileri kopyalayıp yapıştırın (bu, orijinal verileri de tutmak içindir)
- Sütun B'deki hücreler seçiliyken, Giriş sekmesine tıklayın
- Düzenleme grubunda Bul ve Seç seçeneğine tıklayın
- Açılır menüde görünen seçeneklerde Değiştir seçeneğine tıklayın. Bu, Bul ve Değiştir iletişim kutusunu açacaktır.
- 'Neyi bul' alanına şunu girin: ,* (yani, virgül ve ardından bir yıldız işareti)
- 'Şununla değiştir' alanını boş bırakın
- Tümünü Değiştir düğmesine tıklayın
Yukarıdaki adımlar, veri kümesindeki virgülü bulur ve virgülden sonraki tüm metni (virgül dahil) kaldırır.
Bu, seçili hücrelerdeki metni değiştireceğinden, metni başka bir sütuna kopyalamak ve ardından bu bul ve değiştir işlemini gerçekleştirmek veya orijinal verilerin bozulmadan kalması için verilerinizin yedek bir kopyasını oluşturmak iyi bir fikirdir.
Bu nasıl çalışıyor?
* (yıldız işareti), herhangi bir sayıda karakteri temsil edebilen bir joker karakterdir.
Virgülden sonra ("Bul" alanında) kullandığımda ve ardından Tümünü Değiştir düğmesine tıkladığımda, hücredeki ilk virgülü buluyor ve bunu bir eşleşme olarak kabul ediyor.
Bunun nedeni, yıldız işaretinin (*) işaretinin virgülden sonraki tüm metin dizesiyle eşleşmesi olarak kabul edilmesidir.
Yani tümünü değiştir düğmesine bastığınızda, virgül ve ardından gelen tüm metinlerin yerini alıyor.
Not: Bu yöntem iyi çalışır, o zaman her hücrede yalnızca bir virgül bulunur (örneğimizde olduğu gibi). Birden fazla virgülünüz olması durumunda, bu yöntem her zaman ilk virgülü bulur ve ondan sonraki her şeyi kaldırır. Yani ikinci virgülden sonraki tüm metni değiştirmek ve ilkini olduğu gibi tutmak istiyorsanız bu yöntemi kullanamazsınız.Virgülden önceki tüm karakterleri kaldırmak istemeniz durumunda, virgülden önce yıldız işareti koyarak (,* yerine *) bul alanı girişini değiştirin.
Formülleri Kullanarak Metni Kaldırma
Belirli bir karakterden önce veya sonra metni nasıl bulacağınız ve değiştireceğiniz konusunda daha fazla kontrole ihtiyacınız varsa, Excel'deki yerleşik metin formüllerini kullanmak daha iyidir.
Aşağıdaki veri setine sahip olduğunuzu varsayalım. Virgülden sonraki tüm metni kaldırmak istediğiniz yer.
Bunu yapmak için formül aşağıdadır:
=SOL(A2,BUL(",",A2)-1)
Yukarıdaki formül, hücredeki virgülün konumunu bulmak için BUL işlevini kullanır.
Bu konum numarası daha sonra virgülden önceki tüm karakterleri çıkarmak için LEFT işlevi tarafından kullanılır. Sonucun bir parçası olarak virgül istemediğim için Bul formülünün sonuç değerinden 1 çıkardım.
Bu basit bir senaryoydu.
Biraz karmaşık bir tane alalım.
İkinci virgülden sonra tüm metni kaldırmak istediğim aşağıdaki veri setine sahip olduğumu varsayalım.
İşte bunu yapacak formül:
=SOL(A2,BUL("!",YERİ(A2,",","!",2))-1)
Bu veri kümesinde birden fazla virgül bulunduğundan, ilk virgülün konumunu almak ve solundaki her şeyi çıkarmak için BUL işlevini kullanamıyorum.
Bir şekilde ikinci virgülün konumunu bulmam ve ardından ikinci virgülün solundaki her şeyi çıkarmam gerekiyor.
Bunu yapmak için, ikinci virgülü bir ünlem işaretiyle değiştirmek için YERİNE GEÇ işlevini kullandım. Şimdi bu bana hücrede benzersiz bir karakter veriyor. Artık ikinci virgülün solundaki her şeyi çıkarmak için ünlem işaretinin konumunu kullanabilirim.
Ünlem işaretinin bu konumu, ikinci virgülden önceki her şeyi çıkarmak için SOL işlevinde kullanılır.
Çok uzak çok iyi!
Ama ya veri setinde tutarsız sayıda virgül varsa.
Örneğin, aşağıdaki veri setinde bazı hücrelerde iki virgül, bazı hücrelerde üç virgül vardır ve son virgülden önce tüm metni çıkarmam gerekiyor.
Bu durumda, bir şekilde virgülün son oluşumunun konumunu bulmam ve ardından solundaki her şeyi çıkarmam gerekiyor.
Bunu yapacak formül aşağıda
=SOL(A2,BUL("!",YEDEK(A2,",","!",UZUNLUK(A2)-UZUNLUK(YEDEK(A2,",",""))))-1)
Yukarıdaki formül, hücredeki metnin toplam uzunluğunun yanı sıra virgülsüz metnin uzunluğunu bulmak için UZUNLUK işlevini kullanır.
Bu iki değeri çıkardığımda bana hücredeki toplam virgül sayısını veriyor.
Yani, bana A2 hücresi için 3 ve A4 hücresi için 2 verecekti.
Bu değer daha sonra son virgülü bir ünlem işaretiyle değiştirmek için SUBSTITUTE formülünde kullanılır. Ve sonra sol işlevini kullanabilirsiniz, ünlem işaretinin solundaki (son virgülün olduğu yer olan) her şeyi ayıklayın
Örneklerde görebileceğiniz gibi, metin formüllerinin bir kombinasyonunu kullanmak, birçok farklı durumla başa çıkmanıza olanak tanır.
Ayrıca, sonuç orijinal verilerle bağlantılı olduğundan, orijinal verileri değiştirdiğinizde sonuç otomatik olarak güncellenir.
Hızlı Doldurma Kullanarak Metni Kaldırma
Hızlı Doldurma, Excel 2013'te tanıtılan ve bundan sonraki tüm sürümlerde kullanılabilen bir araçtır.
Verileri manuel olarak girdiğinizde ve ardından tüm sütun için verileri size vermek için tahminde bulunduğunuzda kalıpları belirleyerek çalışır.
Bu nedenle, belirli bir karakterden önce veya sonra metni kaldırmak istiyorsanız, flash periye sonucun nasıl görüneceğini göstermeniz yeterlidir (birkaç kez manuel olarak girerek) ve flash dolgu otomatik olarak deseni anlar ve size verir. Sonuçlar.
Bunu size bir örnekle göstereyim.
Aşağıda, virgülden sonraki tüm metni kaldırmak istediğim veri setine sahibim.
Hızlı Doldurma kullanarak bunu yapmak için adımlar şunlardır:
- Verilerimizin bitişik sütunu olan B2 hücresine manuel olarak 'Jeffery Haggins' girin (beklenen sonuç budur)
- B3 hücresine 'Tim Scott' yazın (ikinci hücre için beklenen sonuçtur)
- B2:B10 aralığını seçin
- Ana Sayfa sekmesine tıklayın
- Düzenleme grubunda, Doldur açılır seçeneğine tıklayın
- Hızlı Doldur'a tıklayın
Yukarıdaki adımlar size aşağıda gösterildiği gibi sonucu verecektir:
Ayrıca Hızlı Doldurma klavye kısayolunu da kullanabilirsiniz. Kontrol + E sonuç sütunundaki hücreleri seçtikten sonra (örneğimizde Sütun B)
Hızlı Doldurma harika bir araçtır ve çoğu durumda kalıbı tanıyabilir ve size doğru sonucu verebilir. ancak bazı durumlarda kalıbı doğru tanıyamayabilir ve size yanlış sonuçlar verebilir.
Bu nedenle, Hızlı Doldurma sonuçlarınızı iki kez kontrol ettiğinizden emin olun.
Flash dolgu kullanarak belirli bir karakterden sonraki tüm metni kaldırdığımız gibi, belirli bir karakterden önceki metni kaldırmak için de aynı adımları kullanabilirsiniz. yan sütunda sonucun internetim gibi nasıl görünmesi gerektiğini flash dolguyu göster, gerisini o halleder.
VBA (Özel İşlev) Kullanarak Metni Kaldırma
Metni belirli bir karakterden önce veya sonra kaldırma kavramının tamamı, o karakterin konumunu bulmaya bağlıdır.
Yukarıda görüldüğü gibi, bu karakterin son tekrarını bulmak, formüllerin bir karışımını kullanmak anlamına gelir.
Bu oldukça sık yapmanız gereken bir şeyse, VBA (Kullanıcı Tanımlı İşlevler olarak adlandırılır) kullanarak özel bir işlev oluşturarak bu işlemi basitleştirebilirsiniz.
Bir kez oluşturulduktan sonra bu işlevi tekrar tekrar kullanabilirsiniz. Ayrıca kullanımı çok daha basit ve kolaydır (ağır kaldırma işlemlerinin çoğu arka uçtaki VBA kodu tarafından yapıldığından).
Excel'de özel işlevi oluşturmak için kullanabileceğiniz VBA kodunun altında:
Function LastPosition(rCell As Range, rChar As String) 'Bu fonksiyon belirtilen karakterin son konumunu verir' Bu kod Sumit Bansal tarafından geliştirilmiştir (https://trumpexcel.com) Dim rLen As Integer rLen = Len(rCell) i = rLen için 1 Adım -1 ise Mid(rCell, i - 1, 1) = rChar ise LastPosition = i - 1 Çıkış Fonksiyonu Sonraki ise Son i End Fonksiyonu
VBA kodunu VB Editöründeki normal bir modüle veya Kişisel Makro Çalışma Kitabına yerleştirmeniz gerekir. Orada bulunduktan sonra, onu çalışma kitabındaki diğer normal çalışma sayfası işlevi olarak kullanabilirsiniz.
Bu işlev 2 bağımsız değişken alır:
- Belirli bir karakterin veya metin dizesinin son tekrarını bulmak istediğiniz hücre başvurusu
- Konumunu bulmanız gereken karakter veya metin dizisi
Şimdi aşağıdaki veri setine sahip olduğunuzu ve son virgülden sonraki tüm metni kaldırmak ve yalnızca son virgülden önceki metne sahip olmak istediğinizi varsayalım.
Bunu yapacak formül aşağıdadır:
=SOL(A2,SonKonum(A2,",")-1)
Yukarıdaki formülde son virgülün yerini bulmayı belirttim. Başka bir karakterin veya metin dizesinin konumunu bulmak istemeniz durumunda, bunu işlevde ikinci argüman olarak kullanmalısınız.
Gördüğünüz gibi, önceki bölümde kullandığımız uzun metin formülüne kıyasla bu çok daha kısa ve kullanımı daha kolay.
VBA kodunu bir çalışma kitabındaki bir modüle koyarsanız, bu özel işlevi yalnızca o belirli çalışma kitabında kullanabilirsiniz. Bunu sisteminizdeki tüm çalışma kitaplarında kullanmak istiyorsanız, bu kodu Kişisel Makro Çalışma Kitabına kopyalayıp yapıştırmanız gerekir.
Bunlar, Excel'de belirli bir karakterden önce veya sonra metni hızlı bir şekilde kaldırmak için kullanabileceğiniz bazı postalardır.
Tek seferlik basit bir şeyse, bunu bul ve değiştir kullanarak yapabilirsiniz. biraz daha karmaşıksa, yerleşik Excel formüllerinin bir kombinasyonunu kullanmanız veya hatta VBA kullanarak kendi özel formülünüzü oluşturmanız gerekir.
Umarım bu öğreticiyi faydalı bulmuşsunuzdur.
