נתונים שמגיעים מטפסים, מייצוא והעתקה והדבקה כמעט אף פעם לא מסודרים. כמה פונקציות טקסט מתקנות את הבעיות הרגילות: רווחים מיותרים, אותיות גדולות וקטנות שגויות ותווים מוזרים. אחר כך חיבור מחבר מחדש את החלקים הנקיים.
קודם לנקות, אחר כך לחבר
נניח ש-A2 מכיל את הטקסט " jordan REYES ", עם רווחים בשני הקצוות ושלושה רווחים באמצע. TRIM מסירה את הרווחים החיצוניים ומכווצת כל רצף רווחים פנימי לרווח אחד. PROPER מתקנת אחר כך את האותיות. קינון שלהן מנקה את התא בצעד אחד:
=PROPER(TRIM(A2))
מחזיר Jordan Reyes.
עובדים מבפנים החוצה: הפונקציה הקרובה ביותר לתא רצה ראשונה. תא שנראה נקי אבל מתנהג לא יפה מסתיר בדרך כלל תו שאי אפשר לראות. TRIM נבנתה לרווח הרגיל, קוד 32, ולא לשום דבר אחר. דפי אינטרנט מביאים לעיתים רווח שאינו נשבר, קוד 160, ש-TRIM משאירה במקומו. מחליפים אותו ברווח רגיל קודם בעזרת SUBSTITUTE ואז מנקים: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
CLEAN מטפלת בקבוצה אחרת: התווים שאינם מודפסים, כמו שבירות שורה שמתגנבות מאימייל מועתק או מייצוא ממסד נתונים. הפעלתה על תא מסירה אותם, ואפשר לעטוף אותה סביב TRIM כשבתא יש את שתי הבעיות.
החלפת טקסט ושינוי אותיות
SUBSTITUTE מחליפה כל התאמה לטקסט הישן, והיא רגישה לאותיות גדולות וקטנות: החלפה של "ana" ב-"Ana ana" נותנת "Ana X", כי האות הגדולה A לא מתאימה. כדי למחוק משהו, מחליפים אותו בטקסט ריק: =SUBSTITUTE(B2," ","") מוחק את כל הרווחים. UPPER ו-LOWER הן הכלים הנכונים לקודים ולכתובות אימייל.
PROPER הוא כלי גס. היא מגדילה את האות הראשונה של כל מילה ומקטינה את השאר, כך ש-"o'neil mcdonald" הופך ל-"O'Neil Mcdonald" ו-"jean-luc" הופך ל-"Jean-Luc". היא לא יכולה לדעת שהשם השני צריך להיות "McDonald". לשמות עם אותיות גדולות יוצאות דופן, כדאי לבדוק את התוצאה בעין.
חיבור חלקים יחד
האופרטור & מדביק טקסטים יחד. עם עיר ב-B2 וקוד מדינה ב-C2, =B2&", "&C2 נותן טקסט כמו "Cork, IE". למספרים ולתאריכים יש להיזהר: תאריך שמחברים עם & מציג את המספר הסידורי שלו, לכן עוטפים אותו קודם ב-TEXT, למשל ="עד "&TEXT(D2,"d mmm yyyy").
לטווח שלם, TEXTJOIN קצרה יותר. =TEXTJOIN(", ",TRUE,D2:D9) שמה כל ערך של D2:D9 בשורה אחת, מופרד בפסיק ורווח. ה-TRUE שבאמצע מדלג על תאים ריקים. עם FALSE, התאים הריקים נשארים, ומתקבלים מפרידים כפולים: טווח שמכיל "a", תא ריק ו-"b" נותן "a, , b" במקום "a, b". מפריד ריק גורם לה להתנהג כמו שרשור רגיל.
TEXTJOIN מקבלת עד 252 ארגומנטים של טקסט, וכל אחד מהם יכול להיות טווח שלם, והיא מחזירה #VALUE! אם התוצאה ארוכה מ-32,767 התווים שתא יכול להכיל. היא קיימת ב-Excel 2019 ומעלה וב-Microsoft 365. גרסאות ישנות יותר צריכות & או CONCATENATE.
טעויות נפוצות
מחיקת העמודה המקורית כשהנוסחאות עדיין מצביעות עליה. העתק את העמודה הנקייה והדבק אותה קודם כערכים.
לחשוב ש-TRIM מסירה רווחים שאינם נשברים או שבירות שורה. היא לא עושה זאת. משתמשים ב-SUBSTITUTE וב-CLEAN.
לשכוח שתוצאה של פונקציית טקסט היא טקסט. מספר מנוקה כמו "042" נשאר טקסט עד שממירים אותו ב-VALUE, ואז האפס המוביל נעלם.