Excel'de telefon numaralarını tek bir standarda getirme

Excel'de 8 yerine +7 önekiyle telefon numarasını biçimlendirmek için önce hücreyi gereksiz karakterlerden (parantezler, boşluklar, tireler) temizlemek, ardından ilk "8" rakamını "+7" ile değiştirmek gerekir. En güvenilir yöntem, verileri normalleştirmek için bir formül kullanmaktır, örneğin: =EĞER(SOLDAN(TEMİZLE_VERİ;1)="8"; "+7"&SAĞDAN(TEMİZLE_VERİ;10); TEMİZLE_VERİ). Bu, CRM sistemlerine, mesajlaşma uygulamalarına ve santral sistemlerine doğru aktarımı sağlar.

Aşağıda farklı senaryolar için ayrıntılı talimatlar verilmiştir: hızlı formül düzeltmelerinden, verilerin kendisini değiştirmeden görsel görünümü ayarlamaya kadar.

Neden tek bir format önemlidir

Numaraların dağınık şekilde saklanması (bazen 8-900, bazen +7(900), bazen de 7900) teknik arızalara yol açar:

• Entegrasyon sorunları: CRM sistemleri ve toplu mesajlaşma servisleri, numara ülke koduyla başlamıyorsa genellikle onu tanımaz.
• Sıralama hataları: Liste sıralanırken, "+" metni ve "8" rakamı karakter tablosunda farklı kodlara sahip olduğu için farklı önekli numaralar karışır.
• Arama sorunları: Kullanıcı numarayı farklı bir formatta girdiyse, kişiye göre numara ile arama yapmak zorlaşır.

Yöntem 1. Formüllerle tam normalleştirme (Önerilen)

Bu yöntem, tüm numaraları +7 ile başlayan bir metin haline getirerek hücre içeriğini değiştirir. Veritabanlarını dışa aktarmaya hazırlamak için uygundur.

Adım 1. Gereksizlerden temizleme

Önce artı işareti hariç tüm sayısal olmayan karakterleri kaldıracağız. A sütununda ham veriler varsa, temizleme için yardımcı bir sütun oluşturalım.

Boşlukları, parantezleri ve tireleri kaldırmak için formül (Excel'in eski sürümleri için):

=YERINEKOY(YERINEKOY(YERINEKOY(YERINEKOY(A2;" ";"");"("; "");")"; "");"-"; "")

Excel 365 veya 2021+ kullanıyorsanız daha kompakt metin işlevleri kullanılabilir, ancak iç içe YERINEKOY her yerde çalışır.

Adım 2. 8'i +7 ile değiştirme

Şimdi temizlenmiş numarayı işleyelim. Mantık şu şekildedir: numara 8 ile başlıyorsa, onu +7 ile değiştiririz. Zaten 7 veya +7 ile başlıyorsa, olduğu gibi bırakırız (yoksa artı ekleriz).

Temizlenmiş numaranın (sadece rakamlar) B2 hücresinde olduğunu varsayalım. Dönüşüm formülü:

=EĞER(SOLDAN(B2;1)="8"; "+7"&SAĞDAN(B2;10); EĞER(SOLDAN(B2;1)="7"; "+7"&SAĞDAN(B2;10); B2))

Nasıl çalışır:

1. İlk karakteri kontrol eder.
2. Bu 8 ise, son 10 rakamı alır ve başına +7 ekler.
3. Bu 7 ise (artı olmadan), aynısını yapar.
4. Orada zaten metin veya başka bir format varsa, olduğu gibi bırakır (elle kontrol gerektirir).

Yöntem 2. Görsel biçimlendirme (Verileri değiştirmeden)

Numaranın güzel görünmesini istiyorsanız (örneğin 8 (900) 000-00-00), ancak hesaplama veya sıralama için hücre içinde sadece rakamlar kalması gerekiyorsa, özel format kullanın.

1. Hücrelerde sadece rakamların yazılı olduğundan emin olun (örneğin 79001234567 veya 89001234567). "Bul ve Değiştir" (Ctrl+H) aracılığıyla fazlalıkları kaldırın: ( bulun, boşlukla değiştirin ve diğer özel karakterler için aynı işlemi tekrarlayın.
2. Numaraları içeren aralığı seçin.
3. Ctrl+1 tuşlarına basın (veya Düzen -> Hücre Biçimlendirme).
4. Sayı sekmesini seçin -> (Tüm Formatlar).
5. "Tür" alanına aşağıdaki kodlardan birini girin:

Önemli: 8'den +7'ye görsel olarak toplu geçiş için [<=...] koşullu özel formatın ayarlanması zordur. Verileri Yöntem 1'deki formülle tek bir sayısal görünüme (7 ile başlayan) getirmek ve ardından +7 (000) 000-00-00 formatını uygulamak daha kolaydır.

Yöntem 3. Anında Doldurma (Flash Fill)

Excel'in modern sürümlerinde (2013 ve üzeri) kalıpları tanıyan bir yapay zeka özelliği bulunmaktadır.

1. A sütununda karışık numaralar listesiniz var: 8-903-111-22-33, +79031112234, 7 (903) 111 22 35.
2. Bitişik B sütununa ilk numarayı mükemmel biçimde elle yazın: +7 (903) 111-22-33.
3. Aynı stilde ikinci numarayı yazmaya başlayın.
4. Ctrl+E tuşlarına basın. Excel mantığı anlamaya çalışacak ve kalan satırları otomatik olarak dolduracaktır.

Biçimlendirmede sık yapılan hatalar

• Artı işaretinden sonra fazla boşluk: Bazı sistemler +7 9... numarasını okumaz. Belirli bir servis gerektirmiyorsa, +7 ile operatör kodu arasında boşluk olmadığından emin olun.
• Kesme işaretli metin formatı: Bazen kullanıcılar baştaki sıfırı korumak için numaranın önüne ' koyar (örneğin, '8900...; RF için geçerli değil ama sık görülen bir alışkanlık). Bu, sayıyı metne dönüştürür ve formüllere engel olabilir. Temizlemek için KARAKTERTEMIZLE işlevini kullanın.
• Farklı uzunluk: Numara 11 rakam içermelidir (ülke kodu + 10 haneli numara). Temizlemeden sonra 10 veya 12 rakam çıkıyorsa, bu numara büyük olasılıkla bozuktur ve elle kontrol gerektirir.

Sıkça Sorulan Sorular (SSS)

8900... girildiğinde otomatik olarak +7 eklenmesi nasıl sağlanır? Makrolar (VBA) olmadan bunu giriş sırasında otomatik yapmak zordur. Tüm verileri girmek daha kolaydır, ardından bitişik sütunda "Yöntem 1"deki normalleştirme formülünü uygulamak yeterlidir.

Excel'den telefon rehberine kopyalarken neden artı işareti kaybolur? Kopyalama sırasında genellikle sadece görünen değer kopyalanır. Özel format (Yöntem 2) kullandıysanız, "çıplak" sayı kopyalanır. Artı işaretinin hücre metninin bir parçası olması için "Yöntem 1"i kullanın veya ara bellek olarak "Not Defteri" üzerinden kopyalayın.

Doğrulama başarısız olan tüm numaralar nasıl silinir? Sütunu dize uzunluğuna göre sıralayın. +7XXXXXXXXXX formatındaki tüm geçerli Rus numaraları 12 karakter uzunluğunda olacaktır (artı işareti + 11 rakam). Farklı uzunluktaki satırları seçip silin veya elle düzeltin.