חלץ שמות פרטיים, אמצעיים ומשפחתיים משם מלא ב-Excel

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

• חלץ או קבל שמות פרטיים משמות מלאים ב-Excel
• חלץ או קבל שמות משפחה משמות מלאים ב-Excel
• חלץ או קבל שמות אמצעיים משמות מלאים ב-Excel
• חלץ או קבל שמות פרטיים, אמצעיים ומשפחתיים משם מלא עם תכונה מדהימה

חלץ או קבל שמות פרטיים משם מלא ב-Excel

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

• name: השם המלא או הפניה לתא ממנו ברצונך לחלץ את השם הראשון.

אנא העתק או הזן את הנוסחה הבאה לתא ריק שבו תרצה לקבל את התוצאה:

לאחר מכן, גרור את הידית למילוי למטה לתאים שבהם תרצה להחיל נוסחה זו, וכל השמות הפרטיים חולצו כפי שמוצג בסcreenshot הבא:

הסבר על הנוסחה:

SEARCH(" ",A2)-1: פונקציית SEARCH משמשת כדי לקבל את המיקום של תו הרווח הראשון, חיסור 1 אומר שמספר התווים שאתה רוצה קטן באחד מהמיקום של הרווח. זה יחזיר את התוצאה: 5.

LEFT(A2,SEARCH(" ",A2)-1)= LEFT("Jerry T. Stone",5): פונקציית LEFT תחלץ את הטקסט בשם המלא “Jerry T. Stone” מצד שמאל עם 5 תווים שהוחזרו על ידי פונקציית SEARCH.

חלץ או קבל שמות משפחה משם מלא ב-Excel

כדי לחלץ את שמות המשפחה מהשמות המלאים, שילוב של הפונקציות RIGHT, LEN, FIND, LEN ו-SUBSTITUTE יכול לעזור לך. הסינטקס הכללי הוא:

• name: השם המלא או הפניה לתא ממנו ברצונך לחלץ את שם המשפחה.

אנא העתק או הזן את הנוסחה הבאה לתא ריק שבו תרצה לקבל את שמות המשפחה:

ואז, גרור את הידית למילוי למטה לתאים שבהם תרצה להחיל נוסחה זו, וכל שמות המשפחה חולצו מהשמות המלאים, ראה סcreenshot:

הסבר על הנוסחה:

1. LEN(A2)-LEN(SUBSTITUTE(A2," ","")): חלק זה משמש כדי לקבל את מספר תווי הרווח בתא A2.

• LEN(A2): פונקציית LEN מחזירה את המספר הכולל של התווים בתא A2. היא תחזיר: 14.
• SUBSTITUTE(A2," ",""): פונקציית SUBSTITUTE משמשת כדי להחליף את כל תווי הרווח ללא דבר. ואתה תקבל את התוצאה כך: “JerryT.Stone”.
• LEN(SUBSTITUTE(A2," ",""): קבל את האורך הכולל של השם המלא בתא A2 ללא רווחים.
• LEN(A2)-LEN(SUBSTITUTE(A2,"-","")): חסר את האורך של השם המלא ללא רווח מאורך השם המלא כדי לקבל את מספר תווי הרווח, וזה יקבל 2.

2. SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))= SUBSTITUTE(A2," ","*",2): פונקציית SUBSTITUTE משמשת כדי להחליף את ההופעה האחרונה של רווח אשר הוחזרה על ידי הנוסחה בחלק הראשון עם תו *. ואתה תקבל את התוצאה הזו: “Jerry T.*Stone”.

3. FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))= FIND("*","Jerry T.*Stone" ): פונקציית FIND תחזיר את המיקום של תו * במחרוזת הטקסט שהוחזרה על ידי פונקציית SUBSTITUTE, ואתה תקבל את המספר 9.

4. LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))= LEN(A2)-9: חלק זה של הנוסחה יקבל כמה תווים יש אחרי הרווח האחרון. זה יקבל את המספר 5. זה יוכר כארגומנט num_chars בפונקציית RIGHT.

5. RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))=RIGHT(A2, 5): לבסוף, פונקציית RIGHT משמשת לחלץ 5 תווים שהוחזרו על ידי הנוסחה בשלב 4 מצד ימין של מחרוזת הטקסט בתא A2.

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

חלץ או קבל שמות אמצעיים משם מלא ב-Excel

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

• name: השם המלא או הפניה לתא ממנו ברצונך לחלץ את השם האמצעי.

אנא הזן או העתק את הנוסחה הבאה לתא ריק כדי להוציא את התוצאה:

לאחר מכן, גרור את הידית למילוי למטה לתאים כדי להחיל נוסחה זו, וכל השמות האמצעיים חולצו גם כן, ראה סcreenshot:

הסבר על הנוסחה:

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

1. SEARCH(" ", A2) + 1: פונקציית SEARCH תקבל את המיקום של הרווח הראשון, הוספת 1 אומרת להתחיל את החילוץ מהתו הבא. זה יקבל את המספר 7. חלק זה מזוהה כארגומנט start_num בתוך פונקציית MID.

2. SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1: חלק זה מזוהה כארגומנט num_chars בתוך פונקציית MID.

• SEARCH(" ", A2, SEARCH(" ", A2)+1): חלק זה של הנוסחה משמש כדי לקבל את המיקום של הרווח השני, זה יקבל את המספר 9.
• SEARCH(" ", A2): פונקציית SEARCH תקבל את המיקום של הרווח הראשון בתא A2. זה יקבל את המספר 6.
• SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1=9-6-1: חסר את מיקום הרווח הראשון ממיקום הרווח השני, ולאחר מכן חסר 1 מהתוצאה אומר להסיר רווח מוביל. והתוצאה היא 2.

3. MID(A2, SEARCH(" ", A2) + 1, SEARCH(" ", A2, SEARCH(" ", A2)+1) - SEARCH(" ", A2)-1)=MID(A2,7,2): לבסוף, פונקציית MID תחלץ 2 תווים באמצע תא A2 שמתחילים בתו השביעי.

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

כדי לתקן את הבעיה הזו, עליך להקיף את הנוסחה לעיל בפונקציית IFERROR, אנא השתמש בנוסחה הבאה:

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

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

עוד מאמרים: