נתונים אמיתיים דוחסים לעיתים כמה עובדות לתא אחד, כמו קוד הזמנה INV-2041-DE. פונקציות שלוקחות טקסט מהסוף, מוצאות תו וממירות טקסט למספרים מאפשרות לפרק אותו לחלקים שימושיים.
לקיחת טקסט מהקצוות ומהאמצע
LEFT לוקחת תווים מההתחלה, RIGHT מהסוף, ו-MID ממיקום שמציינים. עם הקוד INV-2041-DE ב-A2, שני התווים האחרונים הם המדינה:
=RIGHT(A2,2)
מחזיר DE.
מיקומים קבועים עובדים רק כששום קוד לא משתנה בצורתו. כשהאורך משתנה, מאתרים את המפריד במקום זאת.
מציאת מיקום עם SEARCH
SEARCH(find, within, start) מחזירה את המיקום של טקסט אחד בתוך טקסט אחר. ב-INV-2041-DE המקף הראשון נמצא במיקום 4, לכן =SEARCH("-",A2) מחזיר 4. מוסיפים 1 כדי להתחיל מיד אחריו: =MID(A2,SEARCH("-",A2)+1,4) מחזיר "2041", ארבעת התווים מהמיקום 5.
הארגומנט השלישי האופציונלי קובע מאיפה להתחיל לחפש. =SEARCH("-",A2,5) מדלג על המקף הראשון ומוצא את השני במיקום 9. כך מגיעים לחלק השני של קוד בן שלושה חלקים.
כדי לקחת הכול אחרי המקף הראשון, מפעילים את LEN כדי לספור: =RIGHT(A2,LEN(A2)-SEARCH("-",A2)) מחזיר "2041-DE".
SEARCH מתעלמת מאותיות גדולות וקטנות ומקבלת את התווים הכלליים ? ו-*. הפונקציה הדומה לה, FIND, רגישה לאותיות גדולות וקטנות ואינה מקבלת תווים כלליים. אם הטקסט לא נמצא, שתיהן מחזירות #VALUE!, לכן כדאי לעטוף את הקריאה ב-IFERROR כשחלק מהשורות לא מכילות את המפריד.
הפיכת טקסט למספרים
כל מה ש-LEFT, RIGHT ו-MID מחזירות הוא טקסט, גם כשהוא נראה כמו מספר. "2041" לא יתחבר ויתמיין במקום הלא נכון. VALUE ממירה אותו: =VALUE(MID(A2,5,4)) מחזיר את המספר 2041. מינוס כפול עושה את אותה עבודה בדרך קצרה יותר, =--MID(A2,5,4), אבל VALUE אומרת בדיוק מה שמתכוונים אליו.
אפשר לזהות מספרי טקסט לפי היישור לשמאל ולפי המשולש הירוק הקטן בפינה. VALUE קוראת את הטקסט לפי ההגדרות האזוריות של המחשב, כך שפסיק עשרוני או נקודה עשרונית יכולים לשנות תוצאה כשמשתפים קבצים בין מדינות. אם הטקסט אינו מספר כלל, מקבלים #VALUE!.
התאמת אותיות גדולות וקטנות
UPPER ו-LOWER עוזרות כשקודים מגיעים באותיות מעורבות, מה ששובר חיפושים שמשווים בדיוק. שלב אותן עם החילוץ בנוסחה אחת:
=LOWER(RIGHT(A2,2))
מחזיר de.
אם צריך רק לפצל עמודה פעם אחת, טקסט לעמודות או מילוי מהיר עשויים להיות מהירים יותר. נוסחאות עדיפות כשהנתונים ממשיכים להשתנות, כי הן מתעדכנות לבד.
הרגל שימושי: כדאי לבנות את הנוסחה צעד אחר צעד בתאים נפרדים, קודם המיקום, אחר כך החלק, ואחר כך ההמרה. כשהיא עובדת, משלבים את הצעדים לנוסחה אחת.