Hücre içindeki numarayı alma

[myoverusedpasport]

Merhabalar, günlerdir manuel şekilde yaptığım numara ayırma için bir fonksiyon yada makro var mıdır acaba ?

Yaptığım örnek şu şekilde,

047310227990553017121105536486480

050134505301144834
052767629990553071546674234234
her biri ayrı hücre olarak düşünün hücrelerde bu şekilde numaralar var ama yanında sağında solunda rakamlar var. Bu hücrelerden numara ayıklanır mı var mıdır bunu bir fonksiyonu ? 0531,538 ile başlayandan sayıları al gibi mesela. Yardımcı olursanız çok sevinirim.

 

[çıtır]

Hangi şartı taşıyanları ve kaç rakam almak istiyorsunuz?Bir örnekle açıklar mısınız?

 

[myoverusedpasport]

Benim ihtiyacım olan 10 haneli cep telefon numaraları 5 ile başlayan 532, 501, 545 gibi tüm operatör numraları ile başlayan cep telefon numaraları örnek 5441112233 şu şekilde 10 haneli numara.

 

[çıtır]

Şöyle bir formül deneyin, A1 , A2 vb hücrelerde ise B1 kopyalayıp aşağı çekerek çoğaltınız.

=PARÇAAL(A1;BUL("*";YERİNEKOY(A1;"05";"**";1);1);10)

 

[myoverusedpasport]

Hocam çok teşekkürler işime yaradı, elinize sağlık. Tek sorunum var bazı hücrelerde 0 yerine direkt 5 ile başlıyor numara, bu formüle 05 ile başlayana ek olarak 53 ile başlayanlar diye ekleme yapmak mümkün mü ?

 

[Ömer BARAN]

Merhaba.

Verinin A1 hücresinde olduğu varsayılmıştır.

İki alternatif söyleyeyim, belki katkısı olur.
►1. alternatif;
-- B1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]UZUNLUK[/COLOR](A1)>11;[COLOR="red"]EĞER[/COLOR]([COLOR="red"]EHATALIYSA[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1);11));"";[COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1);11));"")

-- C1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]UZUNLUK[/COLOR](A1)>22;[COLOR="red"]EĞER[/COLOR]([COLOR="red"]EHATALIYSA[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1;[COLOR="red"]BUL[/COLOR]("05";A1)+1);11));"";[COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1;[COLOR="red"]BUL[/COLOR]("05";A1)+1);11));"")

►2'nci alternatif;
-- B1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;1;2)="05";[COLOR="red"]PARÇAAL[/COLOR](A1;1;11);"")

-- C1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;23;2)="05";[COLOR="red"]PARÇAAL[/COLOR](A1;23;11);"")

-- D1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;23;2)="05";[COLOR="Red"]PARÇAAL[/COLOR](A1;23;11);"")

 

[myoverusedpasport]

Merhaba.

Verinin A1 hücresinde olduğu varsayılmıştır.

İki alternatif söyleyeyim, belki katkısı olur.
►1. alternatif;
-- B1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]UZUNLUK[/COLOR](A1)>11;[COLOR="red"]EĞER[/COLOR]([COLOR="red"]EHATALIYSA[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1);11));"";[COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1);11));"")

-- C1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]UZUNLUK[/COLOR](A1)>22;[COLOR="red"]EĞER[/COLOR]([COLOR="red"]EHATALIYSA[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1;[COLOR="red"]BUL[/COLOR]("05";A1)+1);11));"";[COLOR="red"]PARÇAAL[/COLOR](A1;[COLOR="red"]BUL[/COLOR]("05";A1;[COLOR="red"]BUL[/COLOR]("05";A1)+1);11));"")

►2'nci alternatif;
-- B1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;1;2)="05";[COLOR="red"]PARÇAAL[/COLOR](A1;1;11);"")

-- C1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;23;2)="05";[COLOR="red"]PARÇAAL[/COLOR](A1;23;11);"")

-- D1 hücresine.

=[COLOR="red"]EĞER[/COLOR]([COLOR="red"]PARÇAAL[/COLOR](A1;23;2)="05";[COLOR="Red"]PARÇAAL[/COLOR](A1;23;11);"")

1. Alternatif B1 hücresindeki çalıştı sadece ancak

A1 B1
63022499054562900000 05456290000
6307699920541670000000 054167E+21
630924990541670000000 054167E+20
63102999054562900000 05456290000
63115999054562900000 05456290000

Bazı hücreler E+21 şeklinde çıkıyor bunu nasıl halledebilirim ? Elinize sağlık bu arada.

 

[Murat OSMA]

Ömer Bey, bu çözümleri deneyip sonuçları gözlemlediniz mi?
Formülleriniz 0527 - 0501 vs. gibi olmayan operatör kodlarını da alıyor..

Ayrıca operatör kodlarından önce 0 olmayan gruplar da mevcut. Üyemiz de 5. mesajında onu belirtmiş. Tekrar gözden geçirmenizde fayda var.

Saygılar
​

 

[Ömer BARAN]

1. Alternatif B1 hücresindeki çalıştı sadece ancak

A1 B1
63022499054562900000 05456290000
6307699920541670000000 054167E+21
630924990541670000000 054167E+20
63102999054562900000 05456290000
63115999054562900000 05456290000

Bazı hücreler E+21 şeklinde çıkıyor bunu nasıl halledebilirim ? Elinize sağlık bu arada.

Ben öyle bir hata almıyorum.
Fikrim o ki; tüm durumları kapsayacak %100 bir çözüm biraz zor.
Veri türleri, uzunlukları filan öngörülemiyor.

Konu sahibinin, cevabımın altındaki İMZA bölümünü okuyarak bir örnek belge yüklemesinde yarar var.


Yazdığınızı geç fark ettim Sayın OSMA.
Operatör kodlarının bir listesi yok elimizde, ayrıca operatör kodlarındaki (başında 0 var veya yok hali ayrı bir sıkıntı zaten) rakamlar,
telefon numaralarında da aynı şekilde yer alıyor olabilir.
Tabii ki, konu sahibinin verdiği örnek verilerle deneme yaptım.
Ezbere cevap yazmadığımı biliyorsunuzdur ama ben operatör kodlarının tam lisetisini bilemiyorum.

 

[myoverusedpasport]

http://s6.dosya.tc/server11/xyvqx2/C_003_excel_1.xlsx.html

Ekte görebilirsiniz, eğer hata görünmüyor ise ekran görüntüsü atabilirim.
Şuan kalan problemler 5 ile başlayan 10 haneli numaraları almıyor ve az önceki yorumda belirttiğim + hatası. Ayrıca bazı hücrelerde 13 haneli alıyor numaraları.

 

[çıtır]

Telefon kodlarını tam bilmiyorum.Söyle bir formül düşündüm.

=EĞERHATA(PARÇAAL(A1;BUL("*";YERİNEKOY(YERİNEKOY(YERİNEKOY(A1;"0553";"****");"0532";"****");"53";"***"));11);"")

 

[myoverusedpasport]

Ömer bey,

=EĞER(UZUNLUK(A341)>10;EĞER(EHATALIYSA(PARÇAAL(A341;BUL("5";A341);11));"";PARÇAAL(A341;BUL("5";A341);10));"")

Bu formülde 05 ile başlayana ek olarak 5 ile başlayanları almayı ben ekleyemedim formül hata veriyor yardımcı olabilir misiniz ? Çok uzattım ancak ufak problem kaldı çözülebilirse çok güzel olacak.

 

[antonio]

Merhaba,
Makro ile aşağıdaki kodu deneyiniz.

Sub cep_tlf_varsa_bul()
Dim sh As Worksheet, z As Object, veri As Object, ss As Long

Set sh = Sheets(Sheets(1).Name)
ss = sh.Range("A56789").End(3).Row
Set z = CreateObject("vbscript.regexp")
    z.Global = True
    z.Pattern = "[COLOR="Red"][B]5(0([5-9])|3([0-9])|4([1-9])|5([1-9]))[0-9]{7}[/B][/COLOR]"
    sh.Range("B:H").ClearContents
    For i = 1 To ss
        If sh.Range("A" & i) <> "" Then
            Set veri = z.Execute(sh.Range("A" & i))
            
            If veri.Count > 0 Then
                For d = 0 To veri.Count - 1
                    sh.Cells(i, d + 2).Value = veri(d)
                Next d
            End If
        End If
    Next i
    MsgBox "İşlem tamamlandı.", vbInformation, "antonio"
End Sub

 

[myoverusedpasport]

Merhaba,
Makro ile aşağıdaki kodu deneyiniz.

Sub cep_tlf_varsa_bul()
Dim sh As Worksheet, z As Object, veri As Object, ss As Long

Set sh = Sheets(Sheets(1).Name)
ss = sh.Range("A56789").End(3).Row
Set z = CreateObject("vbscript.regexp")
    z.Global = True
    z.Pattern = "5[0345][0-9]{8}"
    sh.Range("B:H").ClearContents
    For i = 1 To ss
        If sh.Range("A" & i) <> "" Then
            Set veri = z.Execute(sh.Range("A" & i))
            
            If veri.Count > 0 Then
                For d = 0 To veri.Count - 1
                    sh.Cells(i, d + 2).Value = veri(d)
                Next d
            End If
        End If
    Next i
    MsgBox "İşlem tamamlandı.", vbInformation, "antonio"
End Sub

Hocam makro çok güzel çalıştı, bazı gördüğüm problemleri ekli dosyada görebilirsiniz. Bilginiz olsun diye yazıyorum telefon numarası olmayanları da alıyor ayrıca bazı 2 numara olanları B ve C ye yazmıyor.

http://s5.dosya.tc/server5/v2fwf6/Yeni_Microsoft_Excel_Calisma_Sayfasi.xlsx.html

 

[antonio]

Geçerli operatör olmayanları da alacağını tahmin ediyordum. ilk rakam 5 olmalı, ikinci rakamlar 0, 3, 4, 5 üçüncü rakamlar 0-9 arasında olmalı diye kod yazdım.
durum böyle olunca 500 gibi olmayanları da listeler. Yinede bunlar hemen göze çarparlar.
Kodların çalışmadığı hücrelerin formatında sorun vardır.
Metin Formatındaki hücreler olmalı.
Zaten gördüğüm kadarıyla siz de gereken biçimlendirmeleri yapmışsınız.
Kolay gelsin.

 

[Murat OSMA]

Örneğin bir hücre içerisinde bu rakamlar var; 052681719995805760423436384042549905514258098487
Bu sayılar içerisinden hem 549 ile hem de 551 ile başlayan alternatif numaralar da alınmak istenebilir.

Sizin almak istediğiniz bir tanesi ise Sn. antonio'nun verdiği cevap yeterli olacaktır. 500 ile başlayan sayılarla ilgili durumu saymıyorum.
Şimdiye kadar manuel olarak yapılan bir işlem için kanımca bu kadarı da yeterli olacaktır.

Eğer belirttiğim tüm telefon numarası oluşturma ihtimali olan verileri almak ise o zaman ekstra bir iki püf noktası gerekiyor. ​

 

[antonio]

Geçerli operatör telefon numaraları dışında hatalı veri getirmemesi için, 13 No'lu Mesajımdaki kodları güncelledim (kırmızı işaretli kısım)
Durumu gözlemleyiniz.

Dikkatimden kaçan operatör numaraları varsa bildirirseniz gereken düzeltmeyi yapabilirim.
Örnek: 530 şeklinde bir operatör var ama 540, 550 yok sanıyorum.

 

[myoverusedpasport]

Geçerli operatör telefon numaraları dışında hatalı veri getirmemesi için, 13 No'lu Mesajımdaki kodları güncelledim (kırmızı işaretli kısım)
Durumu gözlemleyiniz.

Dikkatimden kaçan operatör numaraları varsa bildirirseniz gereken düzeltmeyi yapabilirim.
Örnek: 530 şeklinde bir operatör var ama 540, 550 yok sanıyorum.

Şimdi bakma fırsatım oldu elinize sağlık 13 numaralı cevabınız en doğru çözüm yolu oldu. Çok teşekkür ederim.

 

[antonio]

Rica ederim.

 

[Murat OSMA]

Zannediyorum bu kodlar daha etkili ve doğru olacaktır. Birilerinin işine yarar düşüncesiyle paylaşıyorum.

[SIZE="2"]Sub Emre()
    Dim evn As Range, i&, e&, kalıp$, tel(), Reg As Object
    On Error Resume Next
    Range("B1:M500").ClearContents
    Range("A:A").NumberFormat = "@"
    For Each evn In Range("A1:A" & Range("A65536").End(3).Row)
        evn.Select
        DoEvents
        SendKeys "{F2}", True
        SendKeys "{ENTER}", True
    Next evn
    tel = Array("505", "506", "507", "530", "531", "532", "533", "534", "535", _
    "536", "537", "538", "539", "540", "541", "542", "543", "544", "545", "546", _
    "547", "548", "549", "551", "553")
    For i = 1 To Range("A65536").End(3).Row
        For e = 0 To UBound(tel)
            kalıp = "(" & tel(e) & "[0-9]{7})"
            Set Reg = CreateObject("VBScript.RegExp")
            Reg.Global = True
            Reg.Pattern = kalıp
            Set bir = Reg.Execute(Cells(i, 1))
            For a = 1 To bir.Count
                Cells(i, Columns.Count).End(1).Offset(0, 1).Value = _
                Reg.Execute(Cells(i, 1)).Item(a - 1)
            Next a
            Set bir = Nothing
            Set Reg = Nothing
        Next e
    Next i
    i = Empty: e = Empty: kalıp = "": Set Reg = Nothing: Set evn = Nothing: Erase tel
End Sub[/SIZE]