Excel'de Dizeden Sayıları ve Metni Bölme

Anonim

Çoğu zaman analiz için sahadan ve sunucudan karışık veriler alıyorum. Bu veriler genellikle kirlidir, sütun sayı ve metinle karıştırılmıştır. Analiz öncesi veri temizliği yaparken sayıları ve yazıları ayrı sütunlara ayırıyorum. Bu yazıda size bunu nasıl yapabileceğinizi anlatacağım.

Senaryo:
Exceltip.com'daki bir arkadaşımız bu soruyu yorumlar kısmında sormuş. “Bir metinden önce gelen sayıları ve metnin sonundaki sayıları excel Formula kullanarak nasıl ayırırım. Örneğin 125EvenueStreet ve LoveYou3000 vb.”

Metin çıkarmak için SAĞ, SOL, ORTA ve diğer metin işlevlerini kullanırız. Sadece ayıklanacak metinlerin sayısını bilmemiz gerekiyor. Ve burada ilk önce aynısını yapacağız.
Sayı Dizenin Sonundayken Bir Dizeden Sayı ve Metin Çıkarma
Yukarıdaki örnek için bu sayfayı hazırladım. A2 hücresinde dizeye sahibim. B2 hücresinde metin bölümünü ve C2 hücresinde Sayı Bölümünü istiyorum.

Bu yüzden sadece sayının başladığı konumu bilmemiz gerekiyor. Sonra Sol ve diğer işlevleri kullanacağız. İlk sayının konumunu elde etmek için aşağıdaki genel formülü kullanıyoruz:
Dizedeki İlk Sayının Konumunu Almak için Genel Formül:

=MIN(ARAMA({0,1,2,3,4,5,6,7,8,9},String_Ref&"0123456789")

Bu, ilk sayının konumunu döndürür.
Yukarıdaki örnek için bu formülü herhangi bir hücreye yazın.

=MIN(ARAMA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))

Metin Bölümünü Çıkart

Metinde 15. konumda bulunan ilk sayı 15 olarak dönecektir. Daha sonra açıklayacağım.

Şimdi, Metin almak için soldan dizgeden 15-1 karakter almamız gerekiyor. Yani kullanacağız
Metni çıkarmak için LEFT işlevi.
Metni Soldan Çıkarma Formülü

=SOL(A5,MIN(ARAMA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))-1)


Burada MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")) tarafından döndürülen sayıdan 1 çıkardık.
Numara Parçasını Çıkar

Şimdi sayıları almak için sadece bulunan 1. sayıdan sayı karakterlerini almamız gerekiyor. Böylece toplam uzunluğunu hesaplıyoruz sicim ve bulunan ilk sayının konumunu çıkarın ve ekleyin 1 ona. Basit. Evet, sadece karmaşık bir ses, basit.
Sayıları Sağdan Çıkarma Formülü

=SAĞ(A5,UZUNLUK(A5)-DAK(ARA({0,1,2,3,4,5,6,7,8,9},A5&"0123456789"))+1)

Burada LEN işlevini kullanarak toplam dize uzunluğunu elde ettik ve ardından ilk bulunan sayının konumunu çıkardık ve sonra ona 1 ekledik. Bu bize toplam sayı sayısını verir. Excel'in SOL ve SAĞ işlevlerini kullanarak metin çıkarma hakkında buradan daha fazla bilgi edinin.

Yani SOL ve SAĞ işlev kısmı basittir. Zor kısım MIN ve bize ilk bulunan sayının konumunu veren ARAMA Kısmıdır. Bunu anlayalım.
Nasıl çalışır
SOL ve SAĞ işlevinin nasıl çalıştığını biliyoruz. Bu formülün bulunan ilk sayının konumunu alan ana kısmını keşfedeceğiz ve bu: MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},String&"0123456789) ")
SEARCH işlevi, dizedeki bir metnin konumunu döndürür. SEARCH('text','string') işlevi iki argüman alır, ilk önce aramak istediğiniz metin, ikinci olarak içinde aramak istediğiniz dize.

    • Burada, ARAMA'da, metin konumunda 0'dan 9'a kadar bir sayı dizisine sahibiz. & Şebeke. Niye ya? Sana anlatacağım.
    • {0,1,2,3,4,5,6,7,8,9} dizisindeki her eleman, verilen dizgede aranacak ve dizi biçiminde dizgedeki konumunu dizide aynı dizinde döndürecektir.
    • Herhangi bir değer bulunamazsa, bir hataya neden olur. Bu nedenle, tüm formül bir hatayla sonuçlanacaktır. Bunu önlemek için metinde "0123456789" sayılarını birleştirdik. Böylece dizedeki her sayıyı her zaman bulur. Bu sayılar sonundadır, dolayısıyla herhangi bir soruna neden olmaz.
    • Şimdi MIN işlevi, SEARCH işlevi tarafından döndürülen diziden en küçük değeri döndürür. Bu en küçük değer, dizedeki ilk sayı olacaktır. Şimdi bu SAYI ve SOL ve SAĞ işlevini kullanarak metin ve dize bölümlerini ayırabiliriz.

Örneğimizi inceleyelim. A5'te sokak adı ve ev numarası olan dizgemiz var. Onları farklı hücrelerde ayırmamız gerekiyor.
İlk önce dizedeki ilk sayı konumumuzu nasıl elde ettiğimizi görelim.

    • MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A5&"0123456789")): bu, MIN(SEARCH({0,1,2,3, 4,5,6,7,8,9}”Monta270123456789”))

Şimdi, açıkladığım gibi arama, {0,1,2,3,4,5,6,7,8,9} dizisindeki her bir sayıyı arayacaktır. Monta270123456789 ve konumunu bir dizi biçiminde döndürür. Döndürülen dizi {8,9,6,11,12,13,14,7,16,17} olacaktır. Nasıl?
0 string içinde aranacaktır. 8 konumunda bulunur. Dolayısıyla ilk öğemiz 8'dir. Orijinal metnimizin yalnızca 7 karakter uzunluğunda olduğunu unutmayın. Anla. 0 bir parçası değil Monta27.
Sonraki 1, dizgede aranacak ve aynı zamanda orijinal dizginin bir parçası değil ve 9 konumunu alıyoruz.
Sonraki 2 aranacaktır. Orijinal dizenin bir parçası olduğu için dizinini 6 olarak alıyoruz.
Benzer şekilde, her eleman bir pozisyonda bulunur.

    • Şimdi bu dizi MIN işlevine MIN({8,9,6,11,12,13,14,7,16,17}) olarak geçirilir. MIN, orijinal metinde bulunan ilk sayının konumu olan 6'yı döndürür.
      Ve bundan sonraki hikaye oldukça basit. SOL ve SAĞ İşlevini kullanarak bu sayıyı metin ve sayıları çıkarırız.

Sayı Dizenin Başındayken Bir Dizeden Sayı ve Metin Çıkarma
Yukarıdaki örnekte, Sayı dizenin sonundaydı. Sayı başındayken sayı ve metni nasıl çıkarıyoruz.

Yukarıdakine benzer bir tablo hazırladım. Sadece başında numara var.

Burada farklı bir teknik kullanacağız. Sayıların uzunluğunu (burada 2 olan) sayacağız ve bu sayıda karakteri String'in solundan çıkaracağız.
Yani yöntem =LEFT (dize, sayı sayısı)
Karakter Sayısını Saymak için Formül budur.
Sayı Sayısını Saymak İçin Genel Formül:

=TOPLA(UZUNLUK(dize)-UZUNLUK(YEDEK(dize,{"0","1","2","3","4","5","6","7","8") ,"9"},"))

Buraya,

      • DEĞİŞTİR işlevi, bulunan her sayıyı “” (boş) ile değiştirir. Yerine bir sayı bulunursa ve diziye yeni dize eklenecekse, aksi takdirde diziye orijinal dize eklenecektir. Bu şekilde 10 karakterlik bir dizimiz olacak.
      • Şimdi LEN işlevi, bu dizelerin bir dizisindeki karakterlerin uzunluğunu döndürür.
      • Ardından, orijinal dizelerin uzunluğundan, SUBSTITUTE işlevi tarafından döndürülen her bir dizenin uzunluğunu çıkaracağız. Bu yine bir dizi döndürür.
      • Şimdi SUM tüm bu sayıları ekleyecektir. Bu, dizedeki sayıların sayısıdır.

Sayı Parçasını Dizeden Çıkart

Artık sayıların uzunluğunu bildiğimize göre sicim, bu işlevi SOL'da değiştireceğiz.
Dizimiz bir A11'e sahip olduğumuz için:

SOLDAN Sayıları Çıkarma Formülü

=SOL(A11,TOPLA(UZUNLUK(A11)-UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","6","7")) ,"8","9"},""))))


Dizeden Metin Bölümünü Çıkart

Sayıların sayısını bildiğimiz için, dizedeki sayı alfabelerini almak için onu toplam dize uzunluğundan çıkarabilir ve ardından bu sayıda karakteri dizenin sağından çıkarmak için doğru işlevi kullanabiliriz.

SAĞDAN Metin Çıkarma Formülü

=SAĞ(A11,UZUNLUK(A2)-TOPLA(UZUNLUK(A11)-UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","") ","7","8","9"},""))))


Nasıl çalışır
Her iki formülün de ana kısmı TOPLA(UZUNLUK(A11)-UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","6",")) şeklindedir. 7","8","9"},")))), bir sayının ilk oluşumunu hesaplar. Ancak bunu bulduktan sonra, SOL işlevini kullanarak metin ve sayıyı bölebiliriz. Öyleyse şunu anlayalım.

      • İKAME(A11,{"0","1","2","3","4","5","6","7","8","9"},""): Bu kısım, bu sayıları hiçbir şey/boş (“”) ile değiştirdikten sonra A11'de bir dizi dizi döndürür. İçin 27Monta {"27Monta","27Monta","7Monta","27Monta","27Monta","27Monta","27Monta","2Monta","27Monta","27Monta"} döndürür.
      • UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","6","7","8","9"}),"" )): Şimdi SUBSTITUTE kısmı UZUNLUK işleviyle sarılır. SUBTITUTE işlevi tarafından döndürülen dizideki metinlerin bu dönüş uzunluğu. Sonuç olarak, {7,7,6,7,7,7,7,6,7,7} elde ederiz.
      • UZUNLUK(A11)-UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","6","7","8","9) "},"")): Burada, yukarıdaki kısım tarafından döndürülen her sayıyı gerçek dizenin uzunluğundan çıkarıyoruz. Orijinal metnin uzunluğu 7'dir. Dolayısıyla {7-7,7-7,7-6,… .} olacaktır. Sonunda {0,0,1,0,0,0,0,1,0,0} elde edeceğiz.
      • TOPLA(UZUNLUK(A11)-UZUNLUK(YEDEK(A11,{"0","1","2","3","4","5","6","7","8"), "9"},"")): Burada, fonksiyonun yukarıdaki kısmı tarafından döndürülen diziyi toplamak için SUM kullandık. Bu, 2'yi verecektir. Dizedeki sayıların sayısı budur.

Şimdi bunu kullanarak metinleri ve sayıları çıkarabilir ve farklı hücrelere bölebiliriz. Bu yöntem, sayı başında ve sonunda olduğunda, her iki metin türüyle de çalışır. SOL ve SAĞ İşlevini uygun şekilde kullanmanız yeterlidir.
Bir Dizeden Sayıları ve Metinleri Bölmek için SplitNumText işlevini kullanın
Yukarıdaki yöntemler biraz karmaşıktır ve metin ve sayılar karıştırıldığında kullanışlı değildir. Metin ve sayıları bölmek için bu kullanıcı tanımlı işlevi kullanın.

Sözdizimi:

=SplitNumText(dize, işlem)

Sicim: Bölmek istediğiniz Dize.
İşlem: bu boole. 0 veya YANLIŞ metin bölümünü almak için. Sayı kısmı için, geçmek NS veya 0'dan büyük herhangi bir sayı.
Örneğin, dize A20'deyse,
Dizeden sayıları çıkarmak için formül:

=SplitNumText(A20,1)

Ve
Dizeden metin çıkarmak için formül:

=SplitNumText(A20,0)

Yukarıdaki formülün çalışmasını sağlamak için VBA modülünde aşağıdaki kodu kopyalayın.

Fonksiyon SplitNumText(str As String, op As Boolean) num = "" txt = "" For i = 1 To Len(str) If IsNumeric(Mid(str, i, 1)) Then num = num & Mid(str, i) , 1) Else txt = txt & Mid(str, i, 1) End If Next ise i op = True ise SplitNumText = num Else SplitNumText = txt End If End Function

Bu kod, bir sayı olup olmadığını, dizedeki her karakteri kontrol eder. Eğer bir sayı ise, txt değişkeninde num değişkeninde saklanır. Kullanıcı op için true değerini geçerse, num döndürülür, aksi takdirde txt döndürülür.

Bence bir dizgeden sayı ve metni ayırmanın en iyi yolu bu.
Dilerseniz çalışma kitabını buradan indirebilirsiniz.

Evet arkadaşlar, metin ve sayıları farklı hücrelere ayırmanın yolları bunlar. Aşağıdaki yorumlar bölümünde herhangi bir şüpheniz veya daha iyi bir çözümünüz varsa bana bildirin. Erkeklerle etkileşim kurmak her zaman eğlencelidir.

Çalışma dosyasını indirmek için aşağıdaki bağlantıya tıklayın:

Bir Hücreden Sayı ve Metni Böl

Popüler Makaleler:
Verimliliğinizi Arttıracak 50 Excel Kısayolu
Excel'de DÜŞEYARA İşlevi
Excel 2016'da EĞERSAY
Excel'de SUMIF İşlevi Nasıl Kullanılır