איך להפריד שמות באקסל: TEXTSPLIT ונוסחאות

=TEXTBEFORE(A2," ") מחזירה את כל מה שלפני הרווח הראשון, כך ש-Ana Silva נותן Ana, ו-=TEXTAFTER(A2," ") מחזירה את כל מה שאחריו, Silva. מלאו את שתי הנוסחאות למטה וכל שם מלא מתפצל לשתי עמודות.

נוסחאותאיפוס

B2fx

=TEXTBEFORE(A2," ")

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu
5	Dara Kelly	Dara	Kelly
6	Eli Novak	Eli	Novak

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

שנו שם בעמודה A ושתי העמודות מתעדכנות. TEXTBEFORE, TEXTAFTER ו-TEXTSPLIT דורשות Microsoft 365 או Excel 2024. בשביל Excel 2021 וגרסאות ישנות יותר, הגרסה עם LEFT ו-FIND נמצאת בהמשך.

TEXTSPLIT: פיצול תא לעמודות

=TEXTSPLIT(A2," ") חותכת את הטקסט בכל רווח ושופכת כל חלק לתא משלו. נוסחה אחת ממלאת כמה עמודות שיש חלקים:

נוסחאותאיפוס

B2fx

=TEXTSPLIT(A2," ")

	A	B	C	D	E
1	Text
2	Ana Maria Silva	Ana	Maria	Silva
3	Paris, Lyon, Nice	Paris	Lyon	Nice

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

• המפריד יכול להיות יותר מתו אחד: ", " מפצל בפסיק שאחריו רווח, ולכן לחלקים אין רווח בהתחלה.
• השאירו מקום לתוצאה. אם תא מימין אינו ריק, הנוסחה מציגה #SPILL! במקום.
• החלקים הם תמיד טקסט, גם ספרות, ולכן SUM מדלגת עליהם. שימו שני סימני מינוס לפני הנוסחה, =--TEXTSPLIT(A2,","), כשאתם צריכים מספרים.

הארגומנט השלישי הוא מפריד שורות. עם שניהם, TEXTSPLIT בונה טבלה קטנה מתא אחד: פסיקים מפרידים בין העמודות ונקודה פסיק בין השורות.

נוסחאותאיפוס

A4fx

=TEXTSPLIT(A2,",",";")

	A	B
1	Teams
2	Ana,Red;Ben,Blue;Chen,Green
3
4	Ana	Red
5	Ben	Blue
6	Chen	Green

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

עוד שתי צורות שעובדות באקסל, מוצגות כאן כטקסט:

=TEXTSPLIT(A2,,", ")        one piece per row: the column delimiter is left out
=TEXTSPLIT(A2,{";",","})    several delimiters in braces: splits at ; or ,

הראשונה הופכת את excel, formulas, text לעמודה של שלושה תאים, וכך הופכים רשימה שהוקלדה בתא אחד לשורות. השנייה הופכת את red;green,blue ל-red, green, blue.

שמות אמצעיים ו-"Last, First"

TEXTAFTER(A2," ") מחזירה את כל מה שאחרי הרווח הראשון, ולכן עבור Ana Maria Silva היא מחזירה Maria Silva. כדי לקבל את המילה האחרונה, ספרו מהסוף עם מספר מופע -1. בשמות שנכתבים כ-Silva, Ana, פצלו בפסיק וברווח במקום:

נוסחאותאיפוס

C2fx

=TEXTAFTER(A2," ",-1)

	A	B	C
1	Full name	First	Last
2	Ana Maria Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Li Wu	Chen	Wu
5	Cher	Cher
6
7	Last, First	First	Last
8	Silva, Ana	Ana	Silva

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

כשהמפריד חסר, TEXTBEFORE ו-TEXTAFTER מחזירות #N/A, ולשם של מילה אחת כמו Cher אין רווח. שורה 5 משתמשת בארגומנט השישי, הערך שיוחזר במקום: =TEXTBEFORE(A5," ",,,,A5) מחזירה את השם כולו ו-TEXTAFTER(...,"") מחזירה תא ריק.

נוסחאותאיפוס

C2fx

	A	B	C
1	Email	User	Domain
2	ana.silva@coddy.tech	ana.silva

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

תורכם: ב-C2, החזירו את הדומיין של כתובת האימייל שב-A2: כל מה שאחרי ה-@.

בדיקההצגת התשובה

הפרדת שמות בגרסאות ישנות של אקסל

בלי TEXTBEFORE ו-TEXTAFTER, מצאו את המיקום של הרווח עם FIND וחתכו סביבו עם LEFT, MID ו-RIGHT:

נוסחאותאיפוס

B2fx

=LEFT(A2,FIND(" ",A2)-1)

	A	B	C
1	Full name	First	Last
2	Ana Silva	Ana	Silva
3	Ben Okafor	Ben	Okafor
4	Chen Wu	Chen	Wu

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

• FIND(" ",A2) היא המיקום של הרווח: 4 ב-Ana Silva.
• LEFT(A2,4-1) לוקחת את 3 התווים שלפניו.
• MID(A2,4+1,100) לוקחת עד 100 תווים שמתחילים אחרי הרווח. 100 פירושו פשוט "יותר מכל שם"; =RIGHT(A2,LEN(A2)-FIND(" ",A2)) נותנת את אותה תוצאה עם אורך מדויק.

הנוסחאות האלה מחזירות #VALUE! כשאין רווח. עטפו אותן ב-IFERROR, =IFERROR(LEFT(A2,FIND(" ",A2)-1),A2), כדי להחזיר את התא כולו במקום.

נוסחאותאיפוס

B2fx

	A	B
1	Code	Prefix
2	KT-1043-BLUE

לחצו על תא כדי לראות את הנוסחה שלו. שנו מספר או נוסחה והגיליון יחושב מחדש.

תורכם: החזירו ב-B2 את החלק של קוד המוצר שלפני המקף הראשון, עם LEFT ו-FIND.

בדיקההצגת התשובה

טקסט לעמודות ומילוי הבזק

כדי לפצל עמודה פעם אחת, בלי נוסחאות:

1. בחרו את עמודת השמות המלאים. ודאו שהעמודות שמימין לה ריקות, כי טקסט לעמודות כותב עליהן.
2. נתונים > טקסט לעמודות.
3. בחרו מופרד, לחצו על הבא, סמנו רווח (או פסיק, או הקלידו תו אחר ב-אחר), ולחצו על הבא.
4. החלק הראשון מחליף את השמות המלאים. כדי לשמור אותם, הגדירו את יעד לתא הריק שמימינם, ואז לחצו על סיום.

מילוי הבזק מהיר יותר כשיש תבנית: הקלידו Ana ליד Ana Silva, הקישו Enter, ואז Ctrl+E או נתונים > מילוי הבזק, ואקסל ממלא את השמות הפרטיים של שאר השורות. שני הכלים כותבים ערכים קבועים. אם השמות ישתנו אחר כך, הפיצול לא ישתנה; נוסחה כן.