Metin içinden kelime alma

[Ridan]

Aşağıdaki şekilde uzun bir listem var.. Burada sadece formül ile İLÇE İSİMLERİ ayrı bir sütuna alınacak. ilçe isimleri GÖNEN, BANDIRMA...bu şekilde devam ediyor. Şimdiden teşekkürler.

Çarşı Mah. No: 1 GÖNEN / BALIKESİR
Çarşı Mah. No: 2 GÖNEN / BALIKESİR
Çarşı Mah. No: 3 BANDIRMA / BALIKESİR
Çarşı Mah. No: 4 GÖNEN / BALIKESİR
Çarşı Mah. No: 5 KARESİ / BALIKESİR
Çarşı Mah. No: 6 GÖNEN / BALIKESİR

 

[bzace]

Merhaba,
Bu şekilde deneyiniz.

=KIRP(SOLDAN(YERİNEKOY(PARÇAAL(A1;MBUL(":";A1;1)+4;38);" ";YİNELE(" ";38));38))

 

[Ridan]

bzace teşekkürler...

 

[Erdem Akdemir]

Alternatif,

Kapı numarası tek haneli olmadığı durumlarda da sonuç getirir.

=İNDİS(KIRP(PARÇAAL(YERİNEKOY(" "&$A1;" ";YİNELE(" ";255));SATIR($D$1:D9)*255;255));5)

 

[Ridan]

Eğer bir tane : olursa oluyor ama, iki tane olur ": " olursa olmuyor sayın bzace.. aşağıdaki gibi

SERVİ MAH. HIFZI VELDET VELİDEDEOĞLU CAD. NO: 99 İÇ KAPI NO: 9 BİGADİÇ / BALIKESİR

Sayın erdem, yukarıdaki adreste sadece VELİDEDEOĞLU kelimesini aldı.. Almak istenilen kelime ilçe isimleri.. BİGADİÇ kelimesi alınacak.

 

[alicimri]

Aşağıdaki Kullanıcı Tanımlı Fonksiyonu kullanın

Function al(Metin)
ilk = Split(Metin, "/")
son = Split(Trim(ilk(0)), " ")
say = UBound(son)
al = Trim(son(say))
End Function

 

[tahsinanarat]

Kodlar Sn. Korhan Ayhan hocama aittir

Option Explicit
 
Sub İL_İLÇE_AYIR()
    Dim X, Veri
 
    Application.ScreenUpdating = False
 
    Range("B:C") = ""
 
    For X = 1 To Cells(Rows.Count, 1).End(3).Row
        Veri = Replace(Replace(Cells(X, 1), "   ", " "), "  ", " ")
        Veri = Split(Replace(Replace(Replace(Veri, "/", " "), "-", " "), "  ", " "), " ")
        Cells(X, 2) = Veri(UBound(Veri) - 1)
        Cells(X, 3) = Veri(UBound(Veri))
    Next
 
    Application.ScreenUpdating = True
 
    MsgBox "İşleminiz tamamlanmıştır.", vbInformation
End Sub

 

[Erdem Akdemir]

Eğer bir tane : olursa oluyor ama, iki tane olur ": " olursa olmuyor sayın bzace.. aşağıdaki gibi

SERVİ MAH. HIFZI VELDET VELİDEDEOĞLU CAD. NO: 99 İÇ KAPI NO: 9 BİGADİÇ / BALIKESİR

Sayın erdem, yukarıdaki adreste sadece VELİDEDEOĞLU kelimesini aldı.. Almak istenilen kelime ilçe isimleri.. BİGADİÇ kelimesi alınacak.

İlk sorduğunuz soru ile bunun arasında dağlar kadar fark var.

 

[çıtır]

Alternatif olarak deneyiniz.

=EĞER(A1="";"";KIRP(PARÇAAL(A1;BUL("*";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2));BUL("/";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2))-BUL("*";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2)))))

 

[Ridan]

Kusura bakmayın.. ilk formülü uyguladıktan sonra sorun meydana çıktı..
Sayın alicimri, birkaç satır dışında yüzde 90 işe yaradı.

Üstatlarım.. Excel 2016 kullanıyorum. Bu sürümde hızlı doldurma özelliği ile kendi bilgisayarımda hallediyorum. Ancak, diğer kurumda çalışan arkadaşın kullandığı sürüm 2010.. 2010 sürümünde hızlı doldurma özelliği olmadığı için formülle veya makro ile çözülmesi gerekiyor sanırım.

 

[Erdem Akdemir]

Alternatif formül.

=İNDİS(KIRP(PARÇAAL(YERİNEKOY(" "&KIRP($A1);" ";YİNELE(" ";255));SATIR($1:$99)*255;255));UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-1)

 

[Ridan]

Alternatif olarak deneyiniz.

=EĞER(A1="";"";KIRP(PARÇAAL(A1;BUL("*";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2));BUL("/";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2))-BUL("*";YERİNEKOY(A1;" ";"*";UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-2)))))

Kelimeler arasında tek boşluk olursa sorun yok, ancak çift boşluk olursa olmuyor.

 

[Ridan]

Alternatif formül.

=İNDİS(KIRP(PARÇAAL(YERİNEKOY(" "&KIRP($A1);" ";YİNELE(" ";255));SATIR($1:$99)*255;255));UZUNLUK(KIRP(A1))-UZUNLUK(YERİNEKOY(KIRP(A1);" ";""))-1)

Erdem bey, harika sorunsuz çalıştı.. çok teşekkür ederim.

 

[çıtır]

Alternatif

=EĞER(A1="";"";KIRP(PARÇAAL(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"*";UZUNLUK(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));"")))-UZUNLUK(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"")));BUL("*";YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"*";UZUNLUK(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));"")))-UZUNLUK(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";""))))+1;UZUNLUK(A1))))

 

[Ridan]

Alternatif

=EĞER(A1="";"";KIRP(PARÇAAL(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"*";UZUNLUK(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));"")))-UZUNLUK(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"")));BUL("*";YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";"*";UZUNLUK(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));"")))-UZUNLUK(YERİNEKOY(KIRP(YERİNEKOY(A1;PARÇAAL(A1;BUL("/";A1);UZUNLUK(A1));""));" ";""))))+1;UZUNLUK(A1))))

Sayın çıtır, genelde doğru sonuç verdi.. Mesela aşağıdaki adreste doğru sonuç vermedi. Sanırım iki tane "/" olduğundan dolayı.. İlgi ve alakanız için teşekkür ederim.
ÜÇPINAR MAH. ESKİ MEVKİİ SK. NO: 99/1 KARESİ / BALIKESİR

 

[Ömer BARAN]

Merhaba.

Aşağıdaki formülün, tüm veri seçenekleri için istenilen sonucu vermesi lazım.
Formül; sağdan ilk "/" işareti ile bu "/" işaretinin solundaki ilk BOŞLUK karakteri arasını alır.
(bir'den fazla boşluk, bir'den fazla / karakteri sonucu etkilemez, formülde ":" karakteri ile ilgili işlem yoktur)

=SOLDAN(PARÇAAL(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"@";UZUNLUK(YERİNEKOY(KIRP(A1);" / ";"/"))-UZUNLUK(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"")));BUL("@";YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"@";UZUNLUK(YERİNEKOY(KIRP(A1);" / ";"/"))-UZUNLUK(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";""))))+1;255);BUL("/";PARÇAAL(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"@";UZUNLUK(YERİNEKOY(KIRP(A1);" / ";"/"))-UZUNLUK(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"")));BUL("@";YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";"@";UZUNLUK(YERİNEKOY(KIRP(A1);" / ";"/"))-UZUNLUK(YERİNEKOY(YERİNEKOY(KIRP(A1);" / ";"/");" ";""))))+1;255))-1)