איך להסיר רווחים באקסל: TRIM, CLEAN, SUBSTITUTE

=TRIM(A2) מסירה כל רווח שלפני הטקסט ב-A2 ואחריו, ומקצרת כל רצף של רווחים בין מילים לרווח אחד. " Ana Silva " הופך ל-Ana Silva.

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

B2fx

=TRIM(A2)

	A	B	C	D
1	Name	TRIM	Length before	Length after
2	Ana Silva	Ana Silva	14	9
3	Ben Okafor	Ben Okafor	13	10
4	Chen Wu	Chen Wu	10	7

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

הרווחים בעמודה A בלתי נראים, ובגלל זה יש את עמודות האורך: LEN סופרת אותם. רווחים מיותרים מגיעים בדרך כלל עם נתונים שיובאו או הודבקו, והם שוברים חיפושים והשוואות בלי שום סימן גלוי.

למה רווחים מיותרים שוברים נוסחאות

בשביל אקסל, Ana עם רווח בסוף ו-Ana הם טקסטים שונים. השוואה מחזירה FALSE, COUNTIF לא סופרת את התא, וחיפוש מחזיר #N/A:

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

D3fx

=XLOOKUP("Ana",TRIM(A2:A4),B2:B4,"not found")

	A	B	C	D
1	Name	Score	Is it Ana?	Ana's score
2	Ana	90	FALSE	not found
3	Ben	85	TRUE	90
4	Chen	78

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

C2 הוא FALSE ו-D2 לא מוצא כלום (בלי הארגומנט הרביעי שלה XLOOKUP הייתה מציגה #N/A) כי ב-A2 יש Ana עם רווח. D3 מבצע TRIM לכל עמודת החיפוש בתוך הנוסחה ומוצא 90. זה עובד ב-Microsoft 365 וב-Excel 2021; בגרסאות ישנות יותר, נקו קודם את העמודה. רווחים הם אחת הסיבות הנפוצות ביותר ל-#N/A מחיפוש.

הסרת כל הרווחים

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

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

B2fx

=TRIM(A2)

	A	B	C
1	Code	TRIM	No spaces
2	AB 12 34	AB 12 34	AB1234
3	555 201 3344	555 201 3344	5552013344

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

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

B2fx

	A	B
1	Customer	Clean name
2	Eli Novak
3	Fay Ruiz
4	Gus Lee

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

תורכם: בשמות שבעמודה A יש רווחים מיותרים. ב-B2, החזירו את השם שב-A2 בלי רווחים מסביבו ועם רווח אחד בין מילים. הנוסחה ממולאת למטה עד B4.

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

TRIM לא עובדת: רווחים קשיחים

טקסט שהועתק מדף אינטרנט או מ-PDF מכיל לעתים קרובות רווחים קשיחים, תו 160, במקום רווחים רגילים, תו 32. הם נראים זהים, אבל TRIM מסירה רק את תו 32, ולכן באקסל שום דבר לא משתנה:

A2:             Ana Silva   (a non-breaking space between the names and one at the end)
=LEN(A2)        10
=LEN(TRIM(A2))  10, TRIM removed nothing

הפתרון הוא להפוך קודם כל תו 160 לרווח רגיל עם SUBSTITUTE, ולתת ל-TRIM לטפל בשאר. הטקסט ב-A2 למטה בנוי עם CHAR(160) כדי שתוכלו לראות את התו שדף אינטרנט מדביק:

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

D2fx

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

	A	B	C	D	E
1	Pasted text	Length	Code of 4th character	Fixed	Fixed length
2	Ana Silva	10	160	Ana Silva	9

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

C2 מראה איך מזהים תו מוזר: CODE(MID(A2,4,1)) מחזירה את הקוד של התו הרביעי, כאן 160. ב-D2 יש 9 תווים, ה-10 של A2 פחות הרווח שבסוף.

CLEAN: הסרת ירידות שורה ותווים נסתרים אחרים

CLEAN מסירה את התווים שאינם מודפסים עם קודים מ-0 עד 31: ירידות שורה (10), חזרות לתחילת שורה (13) וטאבים (9). היא לא נוגעת ברווחים, ולכן הצמד הרגיל הוא =TRIM(CLEAN(A2)):

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

B2fx

=CLEAN(A2)

	A	B	C
1	Imported	CLEAN	TRIM(CLEAN)
2	Ana Silva	AnaSilva	AnaSilva

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

CLEAN מוחקת את ירידת השורה לגמרי, ולכן שני השמות נדבקים ל-AnaSilva . כשירידת שורה מפרידה בין מילים, החליפו אותה ברווח במקום, =TRIM(SUBSTITUTE(A2,CHAR(10)," ")); העמוד על ירידת שורה מראה את המקרה הזה.

החלפת העמודה המקורית בטקסט אחרי TRIM

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

1. כתבו =TRIM(A2) בעמודה ריקה ליד הנתונים ומלאו אותה למטה.
2. העתיקו את העמודה (Ctrl+C, או Cmd+C ב-Mac).
3. בחרו את העמודה המקורית והשתמשו ב-בית > הדבק > הדבק ערכים, כדי שבתאים יהיה טקסט ולא נוסחאות.
4. מחקו את עמודת העזר.

חיפוש והחלפה יכול להסיר רווחים בלי נוסחה, אבל הוא לא יודע להבדיל בין רווחים בהתחלה לבין רווחים שבין מילים: החלפה של רווח בכלום מחברת את Ana Silva ל-AnaSilva. החלפה של שני רווחים ברווח אחד, שוב ושוב עד שאקסל לא מוצא מה להחליף, היא הגרסה הידנית של הכלל של TRIM לרווחים פנימיים. רווח בודד בהתחלה או בסוף נשאר.