נוסחת Excel: פיצול משפט למילים

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

נוסחה כללית:

ארגומנטים

איך הנוסחה עובדת

נניח שהמשתמש בתא B3, תוכל להשתמש בנוסחה הבאה:

לחץ על Shift + Ctrl + Enter כדי לחלץ את המילה הראשונה, ולאחר מכן גרור את הידית למילוי ימינה עד שכל המילים חולצו.

הסבר

הפונקציה MID מחלצת תת-מחרוזת מטקסט על סמך מיקום ההתחלה והמספר של התווים שצוינו.

הפונקציה ROW מחזירה את מספר השורה, כאן הגדרנו אותה להחזיר מערך מ-1 עד 99.

לאחר מכן הנוסחהMID(" "&$B2&" ", ROW($1:$99), 1)
=MID(" Class A Grade 3 Lily Core", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13; 14; 15; 16; 17; 18; 19; 20; 21; 22; 23; 24; 25; 26; 27; 28; 29; 30; 31; 32; 33; 34; 35; 36; 37; 38; 39; 40; 41; 42; 43; 44; 45; 46; 47; 48; 49; 50; 51; 52; 53; 54; 55; 56; 57; 58; 59; 60; 61; 62; 63; 64; 65; 66; 67; 68; 69; 70; 71; 72; 73; 74; 75; 76; 77; 78; 79; 80; 81; 82; 83; 84; 85; 86; 87; 88; 89; 90; 91; 92; 93; 94; 95; 96; 97; 98; 99}, 1)
={" ";"C";"l";"a";"s";"s";" ";"A";" ";"G";"r";"a";"d";"e";" ";"3";" ";"L";"i";"l";"y";" ";"C";"o";"r";"e";" ";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";
"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

SEARCH הפונקציה מחזירה את המיקום של תו או מחרוזת מסוימים מהטקסט הנתון. אם המחרוזת לא נמצאה, הפונקציה מחזירה ערך שגיאה.

הנוסחה SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")
={1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!;
#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;
#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;1;
1;1;1;1;1}

ה פונקציה ISERROR מחזירה TRUE אם הערך הוא שגיאה.

לאחר מכן הנוסחה ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " "))
={FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;
TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;
FALSE}

הפונקציה IF בודקת תנאי מסוים. יש לה שלושה ארגומנטים, הראשון חייב להיות ביטוי לוגי, אם הארגומנט הראשון הוא “TRUE”, אז הארגומנט השני מוצג, ואם הארגומנט הראשון הוא ”FALSE”, הארגומנט השלישי מוצג.

הנוסחה IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99))
={1;"";"";"";"";"";7;"";9;"";"";"";"";"";15;"";17;"";"";"";"";22;"";"";"";"";27;28;29;30;31;32;33;34;35;
36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59;60;61;62;63;
64;65;66;67;68;69;70;71;72;73;74;75;76;77;78;79;80;81;82;83;84;85;86;87;88;89;90;91;92;
93;94;95;96;97;98;99}

ה פונקציה SMALL מחזירה את הערך הקטן ביותר במערך.

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

לאחר מכן
SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))
={1}

לאחר מכן הנוסחה
SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-  SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))
={7-1}
={6}

לאחר מכן הנוסחה
MID(" "&$B3&" ", SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)+1), COLUMN(A1)), SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1)+1)-SMALL(IF(ISERROR(SEARCH(MID(" "&$B3&" ", ROW($1:$99), 1), " ")), "", ROW($1:$99)), COLUMN(A1))-1)
=MID(" "&$B3&" ",2,6-1)
={Class}

קובץ לדוגמה

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

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

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

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

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