23 Ağustos 2012 Perşembe

Microsoft Excel de yazıları büyük harf , küçük harf, ilk harfleri büyük harfe çevirmek



Microsoft Excel de yazıları büyük harf, küçük harf, ilk harfleri büyük harfe çevirmeyi
İki yöntem farklı yöntem ile yapabiliriz;
  •  Excel formülleri yardımıyla
  •  Excel Macro(VBA) yardımıyla 
 

Excel Formülleri yardımıyla;

Formüller
·         =KÜÇÜKHARF(A1)
·         =BÜYÜKHARF(A1)
·         =YAZIM.DÜZENİ(A1)




Örnek video 


Vba Yardımıyla;



Örnek uygulama

1-Excel dosyasını açın.

2- Görünüm sekmesinden sağ taraftaki makrolar tıklayın.
 3-makro adını yazıp oluştur tıklayın.
 4- sub ile başlayan kodları silin .


  
5-aşağıdaki verilen kodu kopyalayıp yapıştırın  sonra Excel sayfasına geri dönün. 
Vba kodumuz
    Sub buyukyap()
Selection.Replace What:="a", Replacement:="A"
Selection.Replace What:="b", Replacement:="B"
Selection.Replace What:="c", Replacement:="C"
Selection.Replace What:="ç", Replacement:="Ç"
Selection.Replace What:="d", Replacement:="D"
Selection.Replace What:="e", Replacement:="E"
Selection.Replace What:="f", Replacement:="F"
Selection.Replace What:="g", Replacement:="G"
Selection.Replace What:="ğ", Replacement:="Ğ"
Selection.Replace What:="h", Replacement:="H"
Selection.Replace What:="ı", Replacement:="I"
Selection.Replace What:="i", Replacement:="İ"
Selection.Replace What:="j", Replacement:="J"
Selection.Replace What:="k", Replacement:="K"
Selection.Replace What:="l", Replacement:="L"
Selection.Replace What:="m", Replacement:="M"
Selection.Replace What:="n", Replacement:="N"
Selection.Replace What:="o", Replacement:="O"
Selection.Replace What:="ö", Replacement:="Ö"
Selection.Replace What:="p", Replacement:="P"
Selection.Replace What:="r", Replacement:="R"
Selection.Replace What:="s", Replacement:="S"
Selection.Replace What:="ş", Replacement:="Ş"
Selection.Replace What:="t", Replacement:="T"
Selection.Replace What:="u", Replacement:="U"
Selection.Replace What:="ü", Replacement:="Ü"
Selection.Replace What:="v", Replacement:="V"
Selection.Replace What:="y", Replacement:="Y"
Selection.Replace What:="z", Replacement:="Z"
Selection.Replace What:="q", Replacement:="Q"
Selection.Replace What:="w", Replacement:="W"
Selection.Replace What:="x", Replacement:="X"
End Sub
Sub kucukyap()
Selection.Replace What:="A", Replacement:="a"
Selection.Replace What:="B", Replacement:="b"
Selection.Replace What:="C", Replacement:="c"
Selection.Replace What:="Ç", Replacement:="ç"
Selection.Replace What:="D", Replacement:="d"
Selection.Replace What:="E", Replacement:="e"
Selection.Replace What:="F", Replacement:="f"
Selection.Replace What:="G", Replacement:="g"
Selection.Replace What:="Ğ", Replacement:="ğ"
Selection.Replace What:="H", Replacement:="h"
Selection.Replace What:="I", Replacement:="ı"
Selection.Replace What:="İ", Replacement:="i"
Selection.Replace What:="J", Replacement:="j"
Selection.Replace What:="K", Replacement:="k"
Selection.Replace What:="L", Replacement:="l"
Selection.Replace What:="M", Replacement:="m"
Selection.Replace What:="N", Replacement:="n"
Selection.Replace What:="O", Replacement:="o"
Selection.Replace What:="Ö", Replacement:="ö"
Selection.Replace What:="P", Replacement:="p"
Selection.Replace What:="R", Replacement:="r"
Selection.Replace What:="S", Replacement:="s"
Selection.Replace What:="Ş", Replacement:="ş"
Selection.Replace What:="T", Replacement:="t"
Selection.Replace What:="U", Replacement:="u"
Selection.Replace What:="Ü", Replacement:="ü"
Selection.Replace What:="V", Replacement:="v"
Selection.Replace What:="Y", Replacement:="y"
Selection.Replace What:="Z", Replacement:="z"
Selection.Replace What:="Q", Replacement:="q"
Selection.Replace What:="W", Replacement:="w"
Selection.Replace What:="X", Replacement:="x"
End Sub
Sub ılkharfbuyukyap()
For Each cell In Selection
If Not cell.HasFormula Then
cell.Value = Application.WorksheetFunction.Proper(cell.Value)
End If
Next cell
End Sub



6- Excel sayfasında değiştirmek istediğiniz hücreleri seçin. 
7-makrolar penceresini açın ve yapmak istediğiniz işleme göre 3 makrodan birini seçip makroyu çalıştırınız seçtiğiniz hücrelere çalıştırdığınız makroya göre harfler değişecektir. 








































Hiç yorum yok:

Yorum Gönder