הפרד שם משפחה ושם משפחה ב- Excel (פיצול שמות באמצעות נוסחאות)

צפה בוידאו - פיצול שמות ב- Excel (לשם פרטי, בינוני ושם משפחה)

אקסל הוא כלי מדהים בכל הקשור לחיתוך ולחתוך נתוני טקסט.

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

אחת השאלות הנפוצות ביותר שאני מקבל לגבי מניפולציה של נתוני טקסט היא - "כיצד להפריד בין שמות משפחה ושם משפחה (או שמות פרטיים, אמצעיים ושמות משפחה) ב- Excel?“.

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

אז בואו נתחיל ונראה דרכים שונות לפיצול שמות ב- Excel.

פיצול שמות באמצעות טקסט לעמודות

פונקציונליות טקסט לעמודות ב- Excel מאפשרת לך לפצל במהירות ערכי טקסט לתאים נפרדים בשורה.

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

להלן השלבים להפרדת השם הפרטי ושם המשפחה באמצעות טקסט לעמודות:

1. בחר את כל השמות בעמודה (A2: A10 בדוגמה זו)
2. לחץ על הכרטיסייה 'נתונים'
3. בקבוצה 'כלי נתונים', לחץ על האפשרות 'טקסט לעמודות'.
4. בצע את השינויים הבאים באשף המרת טקסט לעמודה:
   1. שלב 1 מתוך 3: בחר מופרד (זה מאפשר לך להשתמש במרחב כמפריד) ולחץ על הבא
   2. שלב 2 מתוך 3: בחר באפשרות רווח ולחץ על הבא
   3. שלב 3 מתוך 3: הגדר B2 כתא היעד (אחרת הוא יחליף את הנתונים הקיימים)
5. לחץ על סיום

השלבים שלעיל היו מפצלים באופן מיידי את השמות לשם פרטי ושם משפחה (עם שמות פרטיים בעמודה B ושם משפחה בעמודה C).

הערה: במקרה שיש נתונים כלשהם בתאים (אלה שבהם צפוי פלט טקסט לעמודות), Excel יראה לך אזהרה שתודיע לך שיש נתונים מסוימים בתאים. אתה יכול לבחור להחליף את הנתונים או לבטל טקסט לעמודות ולהסיר אותם ידנית תחילה.

לאחר שתסיים, תוכל למחוק את נתוני השם המלא אם תרצה.

כמה דברים שכדאי לדעת בעת שימוש בטקסט לעמודות להפרדת שמות משפחה ושם משפחה ב- Excel:

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

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

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

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

הפרד שמות ראשונים, אמצעיים ושמות משפחה באמצעות נוסחאות

נוסחאות מאפשרות לך לחתוך ולחתוך את נתוני הטקסט ולחלץ מה שאתה רוצה.

בחלק זה אשתף נוסחאות שונות בהן תוכל להשתמש להפרדת נתוני שמות (על סמך אופן בניית הנתונים שלך).

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

נוסחה לקבלת השם הפרטי:

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

נוסחה לקבלת השם האמצעי:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

נוסחה לקבלת שם המשפחה:

= ימינה (A15, LEN (A15) -Search ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

קבל את השם הפרטי

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

הנוסחה שלהלן תיתן לך את השם הפרטי:

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

הנוסחה שלעיל משתמשת בפונקציית SEARCH כדי לקבל את המיקום של תו הרווח בין השם הפרטי ושם המשפחה. הפונקציה LEFT משתמשת במספר מיקום רווח זה כדי לחלץ את כל הטקסט שלפניו.

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

כעת, בואו נתקדם מעט יותר עם כל דוגמא.

קבל את שם המשפחה

נניח שיש לך אותו מערך נתונים והפעם עליך לקבל את שם המשפחה.

הנוסחה שלהלן תוציא את שם המשפחה ממערך הנתונים שלמעלה:

= ימינה (A2, LEN (A2)-חיפוש ("", A2))

שוב, די פשוט.

הפעם, אנו מוצאים תחילה את מיקום תו הרווח, המשמש לאחר מכן לברר את מספר התווים שנותרו אחרי רווח (שיהיה שם המשפחה).

זה מושג על ידי הפחתת ערך המיקום של תו הרווח עם המספר הכולל של התווים בשם.

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

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

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

= ימינה (A15, LEN (A15) -Search ("@", SUBSTITUTE (A15, "", "@", LEN (A15) -LEN (SUBSTITUTE (A15, "", ""))))))

עכשיו, זה התחיל להיות קצת מורכב … לא?

תן לי להסביר איך זה עובד.

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

לאחר מכן הפונקציה SUBSTITUTE משמשת להחלפת תו הרווח האחרון בסמל '@' (ניתן להשתמש בכל סמל - דבר שלא סביר להתרחש כחלק מהשם).

לאחר שהסמל @ הוחלף במקום תו הרווח האחרון, תוכל למצוא בקלות את המיקום של סמל @ זה. זה נעשה באמצעות פונקציית SEARCH.

כל שעליך לעשות הוא לחלץ את כל הדמויות מימין לסמל @ זה. זה נעשה באמצעות הפונקציה RIGHT.

קבל את השם האמצעי

נניח שיש לך את מערך הנתונים כפי שמוצג להלן ואתה רוצה לחלץ את השם האמצעי.

הנוסחה הבאה תעשה זאת:

= MID (A2, SEARCH ("", A2)+1, SEARCH ("", SUBSTITUTE (A2, "", "@", 1))-SEARCH ("", A2))

הנוסחה לעיל משתמשת בפונקציית MID, המאפשרת לך לציין מיקום התחלה ואת מספר התווים שיש לחלץ ממיקום זה.

קל למצוא את עמדת ההתחלה באמצעות פונקציית SEARCH.

החלק הקשה הוא למצוא כמה תווים לחלץ לאחר עמדת התחלה זו. כדי להשיג זאת, עליך לזהות כמה תווים יש ממיקום ההתחלה ועד לתו הרווח האחרון.

ניתן לעשות זאת באמצעות הפונקציה SUBSTITUTE ולהחליף את תו הרווח האחרון בסמל '@'. לאחר שתעשה זאת, תוכל להשתמש בפונקציית SEARCH בקלות כדי למצוא את המיקום של תו החלל האחרון.

כעת, כשיש לך את מיקום ההתחלה ואת המיקום של החלל האחרון, אתה יכול בקלות להביא את השם האמצעי באמצעות הפונקציה MID.

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

הפרד שמות באמצעות חיפוש והחלפה

אני אוהב את הגמישות שמגיעה עם 'מצא והחלף' - מכיוון שאתה יכול להשתמש בו בתווים כלליים.

תן לי להסביר קודם כל מהו דמות של תווים כלליים.

תו כללי הוא משהו שאתה יכול להשתמש בו במקום כל טקסט. לדוגמה, תוכל להשתמש בסמל כוכבית (*) והוא ייצג כל מספר תווים ב- Excel. כדי לתת לך דוגמה, אם אני רוצה למצוא את כל השמות שמתחילים באלף בית A, אני יכול להשתמש ב- A* בחיפוש והחלפה. זה ימצא ויבחר את כל התאים שבהם השם מתחיל בא.

אם אתה עדיין לא ברור, אל תדאג. המשיכו לקרוא והדוגמאות הבאות יבהירו מהן תווים כלליים וכיצד להשתמש בהם להפרדה מהירה של שמות (או כל ערכי טקסט ב- Excel).

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

קבל את השם הפרטי

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

להלן השלבים לביצוע פעולה זו:

1. העתק את נתוני השם בעמודה A והדבק אותם בעמודה B.
2. כשהנתונים בעמודה B נבחרים, לחץ על הכרטיסייה דף הבית
3. בקבוצת העריכה, לחץ על מצא ובחר.
4. לחץ על החלף. פעולה זו תפתח את תיבת הדו -שיח 'מצא והחלף'.
5. בתיבת הדו -שיח 'מצא והחלף', הזן את הדברים הבאים
   1. מצא מה: * (תו רווח ואחריו סמל הכוכבית)
   2. החלף ב: השאר את זה ריק
6. לחץ על החלף הכל.

השלבים שלעיל יתנו לך את השם הפרטי ותסיר הכל אחרי השם הפרטי.

זה עובד גם אם יש לך שמות שיש להם שם אמצעי.

טיפ למקצוענים: קיצור המקשים לפתיחת תיבת הדו -שיח Find and Replace הוא שליטה + ח (החזק את מקש הבקרה ולאחר מכן הקש על מקש H).

קבל את שם המשפחה

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

להלן השלבים לביצוע פעולה זו:

1. העתק את נתוני השם בעמודה A והדבק אותם בעמודה B.
2. כשהנתונים בעמודה B נבחרים, לחץ על הכרטיסייה דף הבית
3. בקבוצת העריכה, לחץ על מצא ובחר.
4. לחץ על החלף. פעולה זו תפתח את תיבת הדו -שיח 'מצא והחלף'.
5. בתיבת הדו -שיח 'מצא והחלף', הזן את הדברים הבאים
   1. מצא מה: * (סמל כוכבית ואחריו תו רווח)
   2. החלף ב: השאר את זה ריק
6. לחץ על החלף הכל.

השלבים שלעיל יתנו לך את שם המשפחה ותסיר הכל לפני השם הפרטי.

זה עובד גם אם יש לך שמות שיש להם שם אמצעי.

הסר את השם האמצעי

במקרה שאתה רק רוצה להיפטר מהשם האמצעי ויש לך רק את שם המשפחה ואת שם המשפחה, אתה יכול לעשות זאת באמצעות Find and Replace.

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

להלן השלבים לביצוע פעולה זו:

1. העתק את נתוני השם בעמודה A והדבק אותם בעמודה B.
2. כשהנתונים בעמודה B נבחרים, לחץ על הכרטיסייה דף הבית
3. בקבוצת העריכה, לחץ על מצא ובחר.
4. לחץ על החלף. פעולה זו תפתח את תיבת הדו -שיח 'מצא והחלף'.
5. בתיבת הדו -שיח 'מצא והחלף', הזן את הדברים הבאים
   1. מצא מה: * (תו רווח ואחריו סמל הכוכבית ואחריו תו הרווח)
   2. החלף ב: (רק שים כאן תו רווח)
6. לחץ על החלף הכל.

השלבים שלעיל יסירו את השם האמצעי בשם מלא. במקרה שלשמות מסוימים אין שם אמצעי, הם לא ישתנו.

שמות נפרדים באמצעות מילוי פלאש

Flash fill הוצג ב- Excel 2013 ומקל מאוד על שינוי או ניקוי מערך נתוני טקסט.

וכאשר מדובר בהפרדת נתוני שמות, הוא נמצא ממש בסמטה של ​​Flash Fill.

הדבר החשוב ביותר שיש לדעת בעת שימוש ב- Flash Fill הוא שצריך תבנית שמילוי Flash יכול לזהות. לאחר שזיהה את התבנית, הוא יעזור לך בקלות לפצל שמות ב- Excel (תקבל יותר בהירות על זה כשתעבור על כמה דוגמאות להלן).

קבל את השם הפרטי או את שם המשפחה מהשם המלא

נניח שיש לך מערך נתונים כפי שמוצג להלן ואתה רוצה לקבל רק את השם הפרטי.

1. בתא הסמוך, הקלד את השם הפרטי באופן ידני מהשם המלא. בדוגמה זו, הייתי מקליד את ריק.
2. בתא השני, הקלד ידנית את השם הפרטי בשם התא הסמוך. בזמן שאתה מקליד, תראה Flash Fill מראה לך רשימה של השם הפרטי באופן אוטומטי (באפור).
3. כאשר אתה רואה את השמות באפור, העבר במהירות דרך כדי לוודא שהוא מציג את השמות הנכונים. אם אלה נכונים, הקש על מקש enter ו- Flash Fill ימלא אוטומטית את שאר התאים בשם הפרטי.

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

אבל ברגע שאתה מתחיל להזין את השם הפרטי בתא השני, Flash Fill מבין את התבנית ומראה לך הצעה. אם ההצעה נכונה, פשוט לחץ על מקש enter.

ואם זה לא נכון, תוכל לנסות להזין ידנית בעוד כמה תאים ולבדוק אם Flash Fill מסוגל להבחין בתבנית או לא.

לפעמים, ייתכן שלא תראה את הדפוס באפור (כפי שמוצג בשלב 2 למעלה). אם זה המקרה, בצע את השלבים שלהלן כדי לקבל את התוצאה מילוי פלאש:

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

השלבים שלעיל יתנו לך את התוצאה ממילוי Flash (בהתבסס על התבנית שהיא הסיקה).

תוכל גם להשתמש ב- Fill Fill כדי לקבל את שם המשפחה או את השם האמצעי. בשני התאים הראשונים, הזן את שם המשפחה (או את השם האמצעי) ומילוי פלאש יוכל להבין את התבנית

סדר מחדש את השם באמצעות מילוי פלאש

Flash Fill הוא כלי חכם והוא יכול לפענח גם תבנית מעט מורכבת

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

להלן השלבים לביצוע פעולה זו:

1. בתא הסמוך, הקלד באופן ידני נובאק, ריק
2. בתא השני, הקלד ידנית קונור, סוזן. בזמן שאתה מקליד, תראה Flash Fill מראה לך רשימת שמות באותו פורמט (באפור).
3. כאשר אתה רואה את השמות באפור, העבר במהירות דרך כדי לוודא שהוא מציג את השמות הנכונים. אם אלה צודקים, הקש על מקש enter ו- Flash Fill ימלא אוטומטית את שאר התאים בשמות באותו פורמט.

הסר את השם האמצעי (או פשוט קבל את השם האמצעי)

תוכל גם להשתמש ב- Flash Fill כדי להיפטר מהשם האמצעי או לקבל רק את השם האמצעי.

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

להלן השלבים לביצוע פעולה זו:

1. בתא הסמוך, הקלד באופן ידני ריק נובאק
2. בתא השני, הקלד ידנית סוזן קונור. בזמן שאתה מקליד, תראה Flash Fill מראה לך רשימת שמות באותו פורמט (באפור).
3. כאשר אתה רואה את השמות באפור, העבר במהירות דרך כדי לוודא שהוא מציג את השמות הנכונים. אם אלה נכונים, הקש על מקש enter ו- Flash Fill ימלא אוטומטית את שאר התאים בשמות ללא השם האמצעי.

באופן דומה, אם ברצונך לקבל רק את שמות האמצע, הקלד את השם האמצעי בשני התאים הראשונים והשתמש במילוי Flash כדי לקבל את השם האמצעי מכל השמות הנותרים.

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