metin içinden kriterli parça alma

[sunkid]

Merhaba arkadaşlar elimde 30 bin satırlık bir veri var yapmak istediğim hücre içinde sicil numarasını alabilmek.

Aşağıda bir hücrede yazan örnek bir veriyi gönderiyorum.
A1 Hücresi
Detay : 1245 kılıç kalkan %100-02 ÇOCUK HASTALİKLARİ
1245 sicil numarası.

Tüm veriler verdiğim örnek gibi
Detay : 1245 kılıç kalkan %100-02 ÇOCUK HASTALİKLARİ

Yalnız bir kriter var sicil numaraları 2 ile 5 hane arasında değişiyor.
Yani hücrede;

Detay : 12456 kılıç kalkan %100-02 ÇOCUK HASTALİKLARİ
de yazabilir.

Formül kullanarak hücre içindeki sicil numaralarını nasıl A2 hücresine yazdırabilirim?

 

[Mahir64]

Merhaba,

Sicil numaraları her seferinde başta sonrasında boşluk mu geliyor?

 

[Mahir64]

Deneyin.

=PARÇAAL(A1;1;BUL(" ";A1)-1)*1

 

[bekir38]

sanırım bu açıklama işinizi görür,,,

http://office.microsoft.com/tr-tr/excel-help/HA010102340.aspx

sabit genişlikte olduğunu varsayıyorum,,,

 

[sunkid]

Deneyin.

=PARÇAAL(A1;1;BUL(" ";A1)-1)*1

Üstad formülü yazdım #DEĞER! hatası verdi..

veriler hücrede aşağıdaki gibi başlıyor;

Detay : 1425 (arada boşluklar var)

 

[sunkid]

sanırım bu açıklama işinizi görür,,,

http://office.microsoft.com/tr-tr/excel-help/HA010102340.aspx

sabit genişlikte olduğunu varsayıyorum,,,

hocam hücrede bir dünya veri yazıyor hepsini parçalıyor bu şekilde benim istediğim bir formül ile direk o parçayı çekebilmek.

 

[dEmir1]

Dener misiniz

=PARÇAAL(A1;BUL(" : ";A1;1)+3;UZUNLUK(A1))

 

[bzace]

Alternatif olarak;
Verinizi A1 hücresine yazıp deneyiniz.

=YERİNEKOY(SAĞDAN(PARÇAAL(" "&YERİNEKOY($A1;" ";YİNELE(" ";50));1;50*3);50);" ";"")

 

[Murat OSMA]

Emre Bey, formülünüz hatalı sonuç üretiyor.

A1 hücresine;

Detay : 1245 kılıç kalkan %100-02 ÇOCUK HASTALİKLARİ

yazıp

A2 hücresine formülünüz girildiğinde "kalkan" sonucunu üretiyor.


Ayrıca 30.000 satır veri için formül yerine makro kullanılması daha uygun olur.

Bu kodlar kullanılabilir;

[FONT="Trebuchet MS"]Sub Emre()
    Dim Rky As Object, i%
    Set Rky = CreateObject("VBScript.RegExp")
    Rky.Global = True
    Rky.Pattern = "[COLOR="Red"]([0-9]{2,5})[/COLOR]"
    On Error Resume Next
    For i = [COLOR="red"]1 [/COLOR]To Range("[COLOR="red"]A65536[/COLOR]").End([COLOR="Red"]3[/COLOR]).Row
        Cells(i, "[COLOR="red"]B[/COLOR]") = CLng(Rky.Execute(Cells(i, "[COLOR="red"]A[/COLOR]")).Item([COLOR="red"]0[/COLOR]))
    Next i
    i = Empty: Set Rky = Nothing
End Sub[/FONT]

 

[bzace]

Murat bey,
Bende herhangi bir sorun yok.
Görsel

 

[Murat OSMA]

Murat bey,
Bende herhangi bir sorun yok.
Görsel

Emre Bey, gönderdiğiniz resmi göremiyorum ama bende sonuç bu şekilde;

 

[bzace]

Murat bey,
Linkdeki dosyayı indirip bakar mısınız
buradan

Altın üye olmadığımdan sizin paylaştığınız resimi görme şansım yok.

 

[Murat OSMA]

Buyrun resme buradan bakabilirsiniz;

Sizce formülünüzün farklı kullanıcılarda farklı sonuçlar üretmesinin sebebi nedir ?

 

[bzace]

Murat bey, açıkcası bir fikrim yok formül değerlendirme seçeneğinden formülü irdelediğimde bir sorun görünmoyur.
KALKAN sonucunu şu şekilde değiştirerek sonuca ulaşıyorum.

;YİNELE(" ";50));1;50*[COLOR="Red"][B]5[/B][/COLOR]);50);" ";"")

Şu şekilde deneyip sonucu bildirir misiniz ?

=YERİNEKOY(PARÇAAL(SAĞDAN(" "&YERİNEKOY(A1;" ";YİNELE(" ";50));50*6);1;50);" ";"")

Bendeki sonuç 1245

Not: Murat bey, KALKAN sonucunu aldığınız dosyayı bana gönderir misiniz? Buradan bizi aydınlatacak biri çıkmazsa Microsoft destek birimine bir danışmak istiyorum.

 

[Mahir64]

Üstad formülü yazdım #DEĞER! hatası verdi..

veriler hücrede aşağıdaki gibi başlıyor;

Detay : 1425 (arada boşluklar var)

Detay : 1425 -- Kırmızı alan yokmuş gibi düşündüm.

Altarnatif cevaplar gelmiş. İyi çalışmalar.

 

[Murat OSMA]

Murat bey, açıkcası bir fikrim yok formül değerlendirme seçeneğinden formülü irdelediğimde bir sorun görünmoyur.
KALKAN sonucunu şu şekilde değiştirerek sonuca ulaşıyorum.

;YİNELE(" ";50));1;50*[COLOR="Red"][B]5[/B][/COLOR]);50);" ";"")

Şu şekilde deneyip sonucu bildirir misiniz ?

=YERİNEKOY(PARÇAAL(SAĞDAN(" "&YERİNEKOY(A1;" ";YİNELE(" ";50));50*6);1;50);" ";"")

Bendeki sonuç 1245

Not: Murat bey, KALKAN sonucunu aldığınız dosyayı bana gönderir misiniz? Buradan bizi aydınlatacak biri çıkmazsa Microsoft destek birimine bir danışmak istiyorum.

İstediğiniz değişiklikleri yapmamam rağmen doğru sonuca ulaşamıyorum.

Bilginize.

Resme bakabilirsiniz;

Dosyayı da buradan indirebilirsiniz;
http://www.dosya.tc/server31/jEPXqY/SicilNo_yuAl.xlsx.html

 

[bzace]

Murat bey,
Gönderdiğiniz dosyada şu şekilde sonuca ulaşıyorum.
Detay*:*1245*kılıç kısmındaki boşlukları silip daha sonra tekrar boşlukları eklediğimde sonuca ulaşabiliyorum.

Gönderdiğiniz dosyada A1 hücresindeki veriye göre
B1 hücresine;

=YERİNEKOY(A1;" ";"")

yazıp, hücre uzunluğuna baktığımda 47 karakter
C1 hücresine;

=YERİNEKOY(A1;DAMGA(160);"")

yazıp, hücre uzunluğuna baktığımda 50 karakter

Ben bu gibi durumlarda Ticari bir programdan Excel'e direk kopyalama yaptığımda yaşıyorum.

Bana göre sorun; :1245kılıç kısmındaki boşlukların ne şekilde verildiği.

 

[Erdem Akdemir]

Biraz uzun bir formül oldu ama metindeki ilk sayıyı buluyor,ilk sayıdan başlayarak yeni metin üretiyor.Yeni metindeki ilk damga(160) yada normal boşluğun sıra numarasını bulup ondan 1 çıkartarak sicil numarasını elde ediyor.

=--PARÇAAL(PARÇAAL(A1;EĞER(ESAYIYSA(ARA(9,99999999999999E+307;BUL({1;2;3;4;5;6;7;8;9};A1)));((MİN(BUL({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))-1))+1;0);99);1;EĞERHATA(MBUL(DAMGA(160);PARÇAAL(A1;EĞER(ESAYIYSA(ARA(9,99999999999999E+307;BUL({1;2;3;4;5;6;7;8;9};A1)));((MİN(BUL({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))-1))+1;0);99);1);MBUL(" ";PARÇAAL(A1;EĞER(ESAYIYSA(ARA(9,99999999999999E+307;BUL({1;2;3;4;5;6;7;8;9};A1)));((MİN(BUL({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))-1))+1;0);99);1))-1)

 

[Murat OSMA]

Erdem Bey şu an cepten giriyorum ve hâliyle pc'ye göre ekran baya bir küçük, yazdığınız formülün sonu ne zaman gelecek diye hevesle inceledim.
Bu arada formülü çözene kadar kahvem bitti, borcunuz olsun.

Selâmlar

 

[Korhan Ayhan]

Bir alternatifte ben vermek istedim.

=--YERİNEKOY(PARÇAAL(A1;BUL(":";A1)+1;6);DAMGA(160);"")