לפני כמה ימים נשאלתי ע"י קולגה בעבודה איך לחלץ את המספרים או את האותיות מקוד אלפאנומרי (כגון, ABC123). הדרך לבצע את המשימה תלויה האם המספרים מופיעים בקוד מקובצים בקבוצה אחת או אם יש מספר קבוצות מספרים בקוד.
1 – פתרון עם "טקסט לעמודות"
אם לקוד האלפאנומרי תבנית קבועה
נוכל להשתמש ב"טקסט לעמודות":
א. בוחרים את הטווח
ב. ב"טקסט לעמודות" בוחרים באופציה "רוחב קבוע"
ג. מקליקים במקום הרצוי על מנת ליצור את ההפרדה
ד. בשלב האחרון בוחרים האם להציג את שני השדות או לבטל אחד מהם (המספרים או האותיות)
2 – פתרון עם נוסחת מערך
אם אין לקוד תבנית קבועה, נוכל להשתמש בנוסחת מערך זו שהציע Ashish Mathur
=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($1:$8),1)),0),COUNT(1*MID(A2,ROW($1:$8),1)))
זאת נוסחת מערך שמכניסים לתא ע"י לחיצה על המקשים Ctrl+Shift+Enter
כדי לחלץ את האותיות במקום המספרים, יש להחליף את הארגומנט TRUE בפונקציה MATCH בערך FALSE
לנוסחה זו מספר חסרונות:
- היא מסורבלת ויש להתאים אותה לגודל הקוד האלפאנומרי;
- במקרה של מספר קבוצות מספרים או אותיות התוצאה יכול להיות לא נכונה
3 – פתרון עם UDF (פונקציה מוגדרת ע"י המשתמש – מקרו)
פונקציה מוגדרת ע"י המשתמש מאפשרת פתרון כולל לכל צורת תבנית של הקוד. במודול מקרו של העורך VBA מכניסים את הקוד הבא
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | Function ext_num_let(rngCell As Range, tType As Boolean) Dim iX As Integer, strResult As String, temp As String Select Case tType Case 1 For iX = 1 To Len(rngCell) temp = Mid(rngCell, iX, 1) If IsNumeric(temp) Then strResult = strResult & temp End If Next iX Case Else For iX = 1 To Len(rngCell) temp = Mid(rngCell, iX, 1) If Not IsNumeric(temp) Then strResult = strResult & temp End If Next iX End Select ext_num_let = strResult End Function |
לפונקציה שני ארגומנטים
RngCell: התא המכיל את הקוד האלפאנומרי
TType: על מנת לחלץ את המספרים מקלידים 1; 0 על מנת להוציא את האותיות
להלן מספר דוגמאות
