FORMÜLLE METNİ SÜTUNLARA DÖNÜŞTÜRÜN (Text to Columns)

FORMÜLLE METNİ SÜTUNLARA DÖNÜŞTÜR

Excel’de belli ayırıcılarla birleştirilmiş bir metni sütunlara ayırıp tablo formatına dönüştürmek için genellikle Veri / Data menüsündeki Metni Sütunlara Dönüştür  / Text to Columns özelliğini kullanıyoruz. Bu yazımızda bu işlemi formüllerle nasıl yapabileceğimizi aktarıyor olacağım.

İlk etapta şöyle bir tablomuz olduğunu düşünelim;

Bu tablodaki Ad Soyad, Adet, Stok vb. gibi verileri tablodaki ilgili alanlara aktarmak istiyoruz.
O hâlde bunu yapmanın, özellikle formüllerle iç içe olan kişiler için güzel bir yöntemini paylaşayım. Bu işi; çok geniş ve uzun bir formül yazmaya gerek kalmadan üç tane formülü iç içe kullanarak çözebiliyoruz.

Kullanılacak formüller sırasıyla bunlar olacak;

  • DEVRİKDÖNÜŞÜM  > TRANSPOSE
  • XMLFİTRELE > FILTERXML
  • YERİNEKOY > SUBSTITUTE

Bu işin sırrı aslında; XMLFİLTRELE formülü ile sanal diyebileceğimiz bir xml yapısı oluşturup oradan veri almak diyebiliriz.
Şimdi web sitelerinde ya da online hmtl editörlerde tr ve td tagları ile oluşturduğumuz table (tablo) yapılarını düşünün. Bu tablo yapısını Excel‘deki her bir satır için formülle basitçe oluşturacağız.

Tabii ki bu arada tablo yapısını tr ve td tagları ile nasıl oluşturulduğu ile ilgili ufak bir örnek göstereyim;

metin1metin2metin3

”  işte biz de bu yapıyı formülle oluşturduktan sonra XMLFİLTRELE formülü ile her bir td tagındaki veriyi alıp sütunlara aktarıyor olacağız.

Baktığımız zaman, ana tablomuzdaki metin içerisinde kullanılan ayırıcımız noktalı virgül olarak görünüyor, biz de bu ayırıcı yerine tablo yapısındaki td taglarını yazdırır isek yukarıdaki xml yapısını oluşturmuş oluruz. Noktalı virgül yerine td taglarını yazabilmek için mâlumunuz YERİNEKOY / SUBSTITUTE fonksiyonunu kullanmak gerekecek.

Oluşacak formülü adımlayacak olursak öncelikle bu şekilde başlamak iyi olacak.

=”

“&YERİNEKOY(A2;”;”;””)&”

Yukarıdaki formülü tamamladığımızda bize bu şekilde bir çıktı verecek.

 

Ali ÖZCAN5012000AA101B

 

İşte bu bizim veri alacağımız verinin tam hâlidir; bu noktadan sonra geriye, XMLFİLTRELE fonksiyonu ile genel metindeki td taglarının içerisindeki verileri alacağımızı belirtmek ve tüm metni satır boyunca aktarılmasını sağlamak kalıyor.

Aşağıdaki formülü kullanarak td taglarındaki verileri hücrelere aktarmış oluruz.

=XMLFİLTRELE(“

“&YERİNEKOY(A2;”;”;””)&”

“;”//td”))

Son aşamada artık alt alta dökülen verileri, yan yana sütunlara ayırmak için ise DEVRİK_DÖNÜŞÜM / TRANSPOSE fonksiyonunu genel formülün başına yazıyor olacağız.

=DEVRİK_DÖNÜŞÜM(XMLFİLTRELE(“

“&YERİNEKOY(A2;”;”;””)&”

“;”//td”))

Ve nihayetinde verileri tıpkı Metni Sütunlara Dönüştür / Text to Columns özelliği ile yatığımız gibi formüllerle de verileri bu şekilde sütunlara ayırmış olduk.