נוסחת Excel: המרת אות למספר

לפעמים, ברצונך להמיר את האות a ל-1, b ל-2, c ל-3 וכך הלאה בגיליון Excel. עם זאת, המרת כל אות בנפרד היא בזבוז זמן. בטיפ זה, אני מציג כמה נוסחאות Excel לפתרון הבעיה.

המרת אות יחידה למספר בכל תא

כדי להמיר אות יחידה למספר בכל תא של Excel, באפשרותך להשתמש בנוסחה שלהלן.

נוסחה כללית:

ארגומנטים

כיצד עובדת נוסחה זו

ניקח דוגמה כדי להסביר את הנוסחה. כאן תמיר את האות בתא C3 למספר. אנא השתמש בנוסחה זו.

לחץ על Enter כפתור.

הסבר

פונקציית INDIRECT: פונקציה זו משמשת להמרת מחרוזת טקסט להתייחסות חוקית. כאן INDIRECT(C3&1) ניתן לראות כך:
  INDIRECT(C3&1)
=INDIRECT(s&1)
=INDIRECT(s1)

COLUMN function: הפונקציה COLUMN מחזירה את מספר העמודה של ההתייחסות הנתונה. כעת הנוסחה היא COLUMN(s1) אשר מחזירה 19.

הערות:

1. נוסחה זו מחזירה את מספר העמודה, מה שאומר שאם יש יותר מאות אחת בתא, היא תחזיר כמפורט בדוגמה להלן.

2. הנוסחה אינה רגישה לגודל אותיות.

המרת מספר אותיות למחרוזת מספרים בכל תא

אם ברצונך להמיר אותיות בכל תא למספרים כפי שמוצג בסcreenshot להלן, תזדקק לנוסחה מורכבת מסוג מערך.

נוסחה כללית:

ארגומנטים

כיצד עובדת נוסחה זו

נניח שברצונך להמיר את האותיות בטווח B3:B8 למספרים, אנא עשה כך:

ראשית, צור טבלה שתציג את כל האותיות ואת המספרים הקשורים להם.

לאחר מכן, בחר את טווח הטבלה והמשך אל תיבת השם (מצד ימין לשורת הנוסחה) כדי לתת לו שם טווח, כאן שם הטווח הוא Rtable. ראה את הסcreenshot:

כעת השתמש בנוסחה זו:

לחץ על Enter כפתור, כל האותיות הראשונות של כל מילה בתא A1 מועתקות.

הסבר

ROW(INDIRECT("1:"&LEN(B3))): ה פונקציית ROW מחזירה את מספר השורה, זוהי נוסחת מערך, ואז היא מחזירה {1;2;3}. MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1): ה פונקציית MID מחזירה את התו במיקום מסוים של המחרוזת הנתונה. נוסחה זו יכולה להיחשב כך:
=MID(B3,{1;2;3},1)
=MID(“acd”,{1;2;3},1)
= {"a";"c";"d"}

VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0): הפונקציה VLOOKUP משמשת למציאת המספרים המתאימים של "a", "c","d" בעמודה השנייה של טווח Rtable. לאחר מכן היא מחזירה {1;3;4}.

TEXTJOIN("",1,VLOOKUP(T(IF(1,MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1))),Rtable,2,0)): הפונקציה TEXTJOIN היא פונקציה חדשה ב-Excel 2019 ו-365, והיא משמשת לאיחוד כל הטקסטים עם תו מפריד. כאן היא מחזירה 134.

הערות:

נוסחה זו אינה רגישה לגודל אותיות.

הערה:

נניח ש-a=1,b=2,c=3,d=4, באפשרותך להשתמש בנוסחה הבאה:

לחץ על Enter כפתור.

נוסחה זו רגישה לגודל אותיות.

קובץ לדוגמה

לחץ כדי להוריד קובץ לדוגמה

נוסחאות קשורות

• הסרת N התווים הראשונים מתא
  כאן מוצגת נוסחה להסרת תווים מצד שמאל של מחרוזת טקסט ב-Excel.
• הסרה מצד ימין של הטקסט
  כאן מוצגת נוסחה להסרת תווים מצד ימין של מחרוזת טקסט ב-Excel.
• חלץ את המילה האחרונה ממחרוזת טקסט ב-Excel
  הדרכה זו מספקת נוסחה לחלץ את המילה האחרונה מהמחרוזת הנתונה.
• חלץ את המילה הראשונה ממחרוזת טקסט ב-Excel
  הדרכה זו מספקת נוסחה לחלץ את המילה הראשונה מהמחרוזת הנתונה.

פונקציות קשורות

• פונקציית INDIRECT
  המרת מחרוזת טקסט להתייחסות חוקית.
• פונקציית COLUMN
  החזר את מספר העמודה שבה מופיעה הנוסחה או את מספר העמודה של ההתייחסות הנתונה.
• פונקציית ROW
  החזר את מספר השורה של ההתייחסות.
• MID
  החזר תווים ספציפיים מתוך מרכז מחרוזת הטקסט.