Genel formül
(=TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),"")))
Özet
Bir metin dizesinden sayısal karakterleri kaldırmak için METİNBİRLEŞTİR işlevine dayalı bir formül kullanabilirsiniz. Gösterilen örnekte, C5'teki formül şöyledir:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Not: Bu bir dizi formülüdür ve Excel 365 dışında control + shift + enter ile girilmelidir.
Açıklama
Excel'in bir metin dizesindeki harfleri doğrudan formüldeki bir diziye çevirmenin bir yolu yoktur. Geçici bir çözüm olarak bu formül, aynı sonucu elde etmek için SATIR ve DOLAYLI işlevlerinin yardımıyla ORTA işlevini kullanır. Aşağıya kopyalanan C5'teki formül:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,ROW(INDIRECT("1:100")),1)+0),MID(B5,ROW(INDIRECT("1:100")),1),""))
Bu oldukça karmaşık görünüyor, ancak işin özü, B5'te tüm karakterlerden oluşan bir dizi oluşturmamız ve sayı olup olmadığını görmek için her karakteri test etmemizdir. Öyleyse, değeri atarız ve boş bir dizeyle ("") değiştiririz. Değilse, sayısal olmayan karakteri "işlenmiş" diziye ekleriz. Son olarak, boş değerleri göz ardı ederek tüm karakterleri bir araya getirmek için METİNBİRLEŞTİR işlevini (Excel 2019'da yeni) kullanıyoruz.
İçten dışa doğru çalışarak, MID işlevi B5'teki metni her seferinde bir karakter olmak üzere çıkarmak için kullanılır. Buradaki anahtar SATIR ve DOLAYLI snippet'tir:
ROW(INDIRECT("1:100"))
100 sayı içeren bir diziyi şöyle döndürür:
(1,2,3,4,5,6,7,8… .99,100)
Not: 100, işlenecek maksimum karakteri temsil eder. Verilerinize uyacak şekilde değiştirin veya aşağıda açıklandığı gibi UZUNLUK işlevini kullanın.
Bu dizi, MID işlevine başlangıç_sayısı bağımsız değişkeni olarak gider . İçin karakter_sayısı , biz 1 kullanıyoruz.
MID işlevi şöyle bir dizi döndürür:
("3";"4";"6";"5";"3";" ";"J";"i";"m";" ";"M";"c";"D";"o";"n";"a";"l";"d";"";"";"";… )
Not: dizideki ekstra öğeler okunabilirlik için kaldırıldı.
Bu diziye sıfır ekliyoruz. Bu, Excel'i metni bir sayıya zorlamaya zorlayan basit bir numaradır. "1", "2", "3", "4" vb. Gibi sayısal metin değerleri hatasız olarak dönüştürülür, ancak sayısal olmayan değerler başarısız olur ve # DEĞER hatası verir. Bu hataları yakalamak için IF işlevini ISERR işleviyle birlikte kullanıyoruz. Bir hata gördüğümüzde, sayısal olmayan bir karakterimiz olduğunu biliyoruz, bu yüzden bu karakteri başka bir MID işlevi ile işlenen diziye getiriyoruz:
MID(B5,ROW(INDIRECT("1:100")),1)
Bir hata almazsanız, bir sayımız olduğunu biliyoruz, bu yüzden diziye sayı yerine boş bir dizge ("") ekleriz.
Son dizi sonucu, metin1 bağımsız değişkeni olarak METİNBİRLEŞTİR işlevine gider. Sınırlayıcı için boş bir dizge ("") kullanıyoruz ve ignore_empty için TRUE veriyoruz. TEXTJOIN daha sonra dizideki boş olmayan tüm değerleri birleştirir ve sonucu döndürür.
Kesin dizi uzunluğu
DOLAYLI 100 gibi bir sayıyı kodlamak yerine, hücrede şu şekilde gerçek karakter sayısı ile bir dizi oluşturmak için UZUNLUK işlevini kullanabilirsiniz:
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
UZUNLUK, hücredeki karakter sayısını 100 yerine kullanılan bir sayı olarak döndürür. Bu, formülün otomatik olarak herhangi bir sayıda karaktere kadar ölçeklenmesini sağlar.
Fazla alanı kaldırmak
Sayısal karakterleri çıkardığınızda, fazladan boşluk karakterleri kalmış olabilir. Baştaki ve sondaki boşlukları çıkarmak ve sözcükler arasındaki boşlukları normalleştirmek için bu sayfada gösterilen formülü KIRP işlevinin içine kaydırabilirsiniz:
=TRIM(formula)
SEQUENCE ile
Excel 365'te, yeni SIRA işlevi yukarıdaki SATIR + DOLAYLI kodun yerini alabilir:
=TEXTJOIN("",TRUE,IF(ISERR(MID(B5,SEQUENCE(LEN(B5)),1)+0),MID(B5,SEQUENCE(LEN(B5)),1),""))
Burada, tek adımda doğru uzunlukta bir dizi oluşturmak için SEQUENCE + LEN kullanıyoruz.
LET ile
Bu formülü LET işlevi ile daha da düzene sokabiliriz. Dizi yukarıda SEQUENCE ve LEN ile iki kez oluşturulduğundan, diziyi bir değişken olarak tanımlayabilir ve yalnızca bir kez oluşturabiliriz:
=LET(array,SEQUENCE(LEN(B5)),TEXTJOIN("",TRUE,IF(ISERR(MID(B5,array,1)+0),MID(B5,array,1),"")))
Burada dizi değeri yalnızca bir kez ayarlanır, ardından MID işlevi içinde iki kez kullanılır.
