טיפ לחילוץ נתונים רצויים מתא באקסל
פעמים רבות יש לנו נתונים בתאים באקסל המכילים מידע שאנו צריכים לשלוף רק חלק ממנו.
לדוגמא :
לאחת מתלמידותיי ,נורית, יש עמודת נתונים המכילה נתונים של תאריך ושעה במבנה של 6 ספרות תאריך, מקף , 6 ספרות שעה לדוגמא : 150612-062304
בצד שמאל 6 ספרות המייצגות את התאריך 15/6/12
בצד ימין 6 ספרות המייצגות את השעה 06:23:04
הבקשה הייתה לשלוף בנפרד את 6 הספרות של התאריך ובנפרד את 6 הספרות של השעה.
לצורך כך נשתמש בפונקציות טקסט ,
תא הנתונים הראשון בו נמצאים הנתונים הוא C2
הפונקציה Left לשליפה של מספר תווים רצוי מתחילת התא
הפונקציה מקבלת :
Text – מחרוזת ממנה רוצים לשלוף
Num chars מספר התווים לשליפה
נקבל 150612
הפונקציה Right לשליפה של מספר תווים רצוי מסוף התא
הפונקציה מקבלת :
Text – מחרוזת ממנה רוצים לשלוף
Num chars מספר התווים לשליפה
נקבל 062304
נורית מאד שמחה , אבל תוך כמה דקות כבר רצתה משהו נוסף,
רצתה שלאחר השליפה התאריך יראה תאריך 15/06/12
והשעה תראה שעה 06:23:04
זה מחייב לשלוף כל פעם 2 ספרות ממקום ידוע ולשרשר תאריך עם תו מפריד "/" ושעה עם תו מפריד ":"
לשרשור נשתמש בתו & המתקבל מלחיצה על Shift + 7
נצטרך להשתמש בפונקצית טקסט נוספת Mid היודעת לשלוף מכל מיקום רצוי במחרוזת
הפונקציה מקבלת :
Text – מחרוזת ממנה רוצים לשלוף
Start num מאיזה מיקום במחרוזת להתחיל את השליפה
Num chars מספר התווים לשליפה
לקבלת התאריך הנוסחה תהיה :
=MID(C2,1,2)& "/" &MID(C2,3,2)& "/" &MID(C2,5,2)
נקבל: 15/6/12
לקבלת השעה הנוסחה תהיה :
=MID(C2,8,2)& ":" &MID(C2,10,2)& ":" &MID(C2,12,2)
נקבל: 06:23:04
אם ברצונך להיות מומחה בפונקציות טקסט תאריך וזמן אני מזמינה אותך
טיפ שמושי מאוד.
האם יש דרך לחלץ מתא המכיל ספרות ואותיות, את הספרות בלבד? או האותיות בלבד?
תודה
שלום יאיר,
אם אתה לא יודע מיקום של הספרות אז אפשר רק באקסל VBA
רעיון איך עושים את זה בvba?
היי שוקי,
גם ב VBA יש פונקציות טקסט,
ואם פונקציה רצויה אין, ניתן לגשת לפונקציות אקסל באמצעות Application.Worksheetfunction
תודה רבה רבה ממש עזר לי
תבורכו משמיים:)
מיכל
שלום וברכה
יש לי טבלה המכילה כתובות כמו "אבני נזר 12 " האם יש נוסחא להפריד בין האותיות למספרים לשתי עמודות נפרדות
תודה רבה מראש
יעל
אם המספרים מופיעים קבוע בסוף ניתן באמצעות פונקציות טקסט
שילוב של RIGHT ושל FIND
איך אפשר לחלץ מחרוזת מתוך טקסט ?
AS89|890|AL2015|6745|GF2435
מתוך המחרוזת נדרש החילוץ של AL2015 כאשר אין עקביות במספר התווים התנאי הוא חילוץ לפי ספירת הופעות של התו "|" בתחילת ואחריו הטקסט
עופר נראה לי לפי ההסבר שלך שאפשר רק באקסל VBA
כדי שאבדוק אם אפשר בכל זאת באקסל
שלח לי דוגמא בקובץ אקסל עם הסבר לרצוי
אפשר לשלב פונקציאת find למצוא את התו ולהשתמש במספר של ה find כדי לעשות mid מדם
שלום, האם יש דרך לחלץ מילה ולא תו?
אני רוצה את המילה הראשונה בכל תא
תודה רבה!
שלום רחל,
מילה ראשונה ניתן לחלץ באקסל בשילוב הפונקציות LEFT FIND
לדוגמא :
=LEFT(A2,FIND(" ",A2)-1)
חבל הזמן, הסבר מעולה ויעיל מאד !!!
שלום רחל,
האם אני יכול לשלוף מתוך תא המורכב ממספר מילים מופרדות על ידי רווח את שתי המילים הראשונות או 3 המילים הראשונות ? יעזור לי מאוד
שלום יהונתן
בתא A23 יש משפט
לשליפת 2 מילים ראשונות
=LEFT(A23,FIND(" ",A23,FIND(" ",A23)+1)-1)
לשליפת 3 מילים ראשונות
=LEFT(A23,FIND(" ",A23,FIND(" ",A23,FIND(" ",A23)+1)+1)-1)
בהצלחה!
אשמח לדעת איך אני אוכל לחלץ ממספר 36-654-78 רק את המספרים ללא ההפרדות. לדוגמא שיצא ככה 3665478 אשמח לתשובה
שרית , תשתמשי ב"החלפה"
ctrl+H
החלף את : –
החלף ל: להשאיר ריק
בהצלחה!
שלום!
אני מופתע כל פעם מחדש!
בהמשך לנושא שליפת מילה ראשונה, האם אפשר גם לשלוף מילה אחרונה, כי הרי הפקודה find סופרת רק מהתחלה, לכן האם יש נוסחה שסופרת מהסוף?
תודה מראש
שלום אברהם,
הפונקציה RIGHT יודעת לשלוף מסוף מחרוזת מספר תוים רצוי.
שלום,
אשמח לדעת איך אני מוציאה את הVALUE של תא מקושר באקסל VBA?
אני כותבת ACTIVECELL.VALUE והוא תמיד מציג לי EMPTY.
השורות שאני רוצה להוציא מהן את הערך מקושרות לגליון בחוברת עבודה אחרת.
תודה מראש,
חנה
חנה שלום,
שלחי לי בבקשה דוגמא באקסל לאימייל
[email protected]
היי מיכל, שאלה קצת שונה:
איזה נוסחה תתאים עבור השוואת טקסט מתא אחד לחלק מטקסט הקים בתא אחר?
לדוג': תא A3 = תפוח.
תא B3 = שמן, קמח, וניל. (תוצאה: FALSE)
תא B4 = שמן, וניל, תפוח. (תוצאה: TRUE)
תא B5 = תפוח. (תוצאה: TRUE)
המון תודה!
הפונקציה FIND תחזיר לך את מספר התו הראשון במחרוזת שאתה מחפש בתוך מחרוזת החיפוש.
אם לא מוצא מחזיר שגיאה.
בהצלחה!
שלום,
יש לי תא שמכיל מחרוזת עם אותיות עברית ואנגלית גם מספרים ואני רוצה לחלץ רק את אותיות האנגלית האם זה אפשרי באקסל?
לדוגמא – התא מכיל רתמה harness 35
ואני רוצה לחלץ את harness
תודה
יסמין , האם מספר התוים קבוע?
האם תידיש רווח בין האותיות למספרים?
היי
יש לי תא שמכיל את המלל הבא – 02/06/2016 16:41:26
וזה ממשיך ככה ל24 אלף תאים..
אני רוצה לבודד את התאריך בלבד ושהשעה תמחק ..
איך אני עושה את זה?
אני מנסה להגיע לחלון שנמצא בהסבר למעלה ואני לא מצליח..
האם מישהו יכול לעזור לי?
אתה צריך לבחור עיצוב תאריך בלי זמן
בעיצוב תאים, מספר, תאריך קצר
מיכל שלום
במידה ואני רוצה לחלץ מתא המכיל אותיות וספרות רק את הספרות מה עליי לעשות?
אין חוקיות בתא מספר אותיות ומספרים שונה כל פעם.
תודה..
שלום גיא,
אם אין חוקיות לא תוכל לחלץ רק את הספרות באקסל,
באקסל VBA אפשר
מיכל שלום
בקצוני לחץ מספרים מתא המכיל ממילים ומספרים (בכל תא כמות מילים ומספרים שונה)
איך אני מזהה את הספרות בתא?
תודה
שלום גיא
באקסל VBA יש אפשרות לעבור תו תו ולבדוק אם ספרה.
שלום מיכל.
תודה רבה על המדריך המעולה!
אשמח אם תעזרי לי לפתח אותו טיפה.
אני צריך לעשות את הפעולה המדוברת – קרי: לפצל תא המכיל 10 ספרות לחמש תאים נפרדים – כל תא 2 ספרות אחרות.
הבנתי כבר איך אני עושה את זה מהמדריך שלך.
השאלה איך אני עושה את זה באופן גנרי.
כי יש לי 100 תאים כאלה – אז אני רוצה שהוא לבד ישנה את ההגדרות בהתאמה, לדוגמא:
מתא A1 ישלוף את ה-2 ספרות הראשונות לתא B1 ואת 2 הספרות הבאות לתא C1 וכן הלאה.
ומתא A2 שישלוף את אותו הדבר בסדר עוקב – 2 ספרות ראשונות לתא B2. ואת 2 הספרות הבאות לC2
וכן הלאה עד הסוף בלי שאצטרך לכתוב את הנוסחה כל פעם מחדש.
אודה לך מאד לעזרתך.
תודה רבה מראש.
הסתדרתי!!! תודה רבה
מצוין שהסתדרת משה!
איך אני יכולה להפריד נתונים המופיעים בתא בודד כך שבחיפוש המערכת תראה אותם כשתי טקסטים שונים למרות שהם מופיעים בתא אחד. הנותנים בתוך טבלה. לדוגמא: שמואל, יוסף.
מופיעים בתא אחד שהמערכת תזהה אותם בחיפוש בתור :1. שמואל 2. יוסף 3. שמואל יוסף 4. יוסף שמואל
תודה.
בחיפוש באקסל כברר מחדל אקסל מחפש תוכן תא או חלק ממנו.
חיפוש 2 מילים ימצא רק אותן ובסדר שבחיפוש ולא מילה בודדה מתוך המילים בחיפוש.
להפרדתמילים בתא למספר תאים ניתן להשתמש ב"טקסט לעמודות" שנמצא בכרטיסיית נתונים באקסל
יש לי תא שמכיל מספר (15) ואני רוצה להמיר אותו לשעה (15:00). איך אני עושה זאת?
עדן נניח שבתא A1 יש לך את ה15
בתא B1 תכניס את הנוסחה הבאה: =A1&":00"
הסמן = בצד שמאל
בהצלחה!
בוקר טוב
אשמח אם ניתן לקבל הדרכה איך אני יכול ללמוד בנוסחאות של VLOOKUP
בקורס חיפוש ואיתור מידע באקסל ניתן ללמוד את כל מה שקשור ב VLOOKUP ובפונקציות נלוות כגון INDEX MATCH
http://eso.co.il/vlookup
שלום,
האם יש דרך להחזיר רק את הספרות שאחרי הנקודה?
אני רוצה לעשות נוסחת IF ו-RUOND לפי גדול או קטן מ-0.5
תודה רבה
היי אורן,
אתה יכול לקבל רק את הערך שאחרי הנקודה העשרונית כך:
=A1-INT(A1)
שווה התא פחות INT של התא
את כל זה אתה יכול להכניס לפונקציה ROUND או כל דבר אחר שאתה רוצה.
בהצלחה!
מועיל מאוד.
אם עוניין להתשמש בMID כך שישלוף מתו מסויים (נאמר הרביעי) ועד הסוף. כלומר לא להגדיר לו את אורך המחרוזת. מה להגדיר?
אני שמחה שמועיל לך , זו מטרת הטיפים 🙂
MID רוצה מספר תוים לשליפה , חובה!
כדי למצוא את מספר התוים לשליפה תשתמש בפונקציה LEN שמחזירה את מספר התוים במחרוזת
פחות מספר התוים שאתה לא רוצה, אם אתה רוצה מתו רביעי זה יהיה 3
בהצלחה!
יש שורה של תוצאה מחשבון מלא של תשלומי שכר באחוזים והתוצאה שלהם היא מספר אם פסיק כמו 156.23 והאקסל פפלוס אני ממיר אותם למילים להדפסת שיקים ואני לא רוצה שיצא לי התוצאה שאחרי הפסיק הפעולה של עיצוב תהים בלי פסיק עשיתי אבל הנוסחה של המרת מילים לוקה את המספר שיש בתא אפילו הוא מופיע אייך אני פשוט מוחק אותם כי זה שורה ענקית ואני לא יכול לעבור תא תא
בעמודה ליד אתה משתמש בפונקציה ROUNDDOWN
וב NUM DIGITS אתה שם 0
בסוף תדביק כערכים.
בהצלחה!
איך אני מוציאה את המספר מתוך תא שרשום "100 $" ?
היי מעיין,
השאלה אם סימן $ בגלל עיצוב מטבע או שזה טקסט.
אם עיצוב מטבע פשוט משנים עיצוב,
אם טקסט משתמשים בפונקציות טקסט כדי לשלוף,
בודקים באיזה צד סימן $ לפי זה מחליטים אם LEFT או RIGTH
בהצלחה!
מיכל, את מדהימה….
אני צריכה לשלוף מתוך טקסט את המילים הרביעית והחמישית.
יש אפשרות לעשות את זה?
תודה מראש,
שרון.
היי שרון,
אפשר באמצעות פונקציות טקסט באקסל
MId find Len
בהצלחה!
היי מיכל תודה רבה, יש לי שתי עמודות טקסט וברצוני לבדוק האם תא אחד מכיל טקסט של תא שני , למרות שהם לא בדיוק אותו הדבר אלא רק מילה או שתיים משותפות לשני התאים! תודה רבה ראש!
הפונקציה FIND באקסל יודעת לזהות מחרוזת בתוך מחרוזת אחרת.
בהצלחה!
כיצד ניתן לשלוף מתוך תא שמכיל מספרים וסימנים ללא סדר קבוע , רק את המספרים ?
מוטי זה ניתן רק באמצעות תכנות באקסל, אקסל VBA
שלום מיכל
בדרך כלל איני מגיב!
את פשוט מעולה !!! אני קורא את השיעורים שלך
וכאשר אני חושב שאני יודע מסתבר לי שזה לא נכון . ההדרכות מקצרות לי עבודה רבה . אני עובד עם גליונות נתונים ענקיים ועבודת נמלים הופכת לעבודה פשוטה ויעילה.אשרי האנשים שאת היא מדריכתם.יישר כוח ותודה
שלום מיכל
אינני יודע אם כבר שאלו את השאלה הפשוטה הזו, אך היא מציקה ואין לי את הזמן לעבור על כל השאלות שנשאלו.
אני רושם כתובת בעברית. באופן קבוע נצמד מספר הבית לשם הרחוב, למרות שהקשתי על מקש הרווח.
מה הסיפור?
אמשח ואודה לתשובה בהקדם
תודה
דוד תבדוק שכיוון הטקסט בתאים מימין לשמאל
היי עברתי על כל השאלות ותשובות ולא מצאתי תשובה, אז כככה
עשיתי בדיוק את מה שרשום בהוראות של העמוד הזה (ואני מדבר על לחלץ שעה ) וזה פשוט לא עובד לי ניסיתי גם לשנות את כל האפשרויות ב עיצוב תא זה פשוט לא עובד לי אין לי אפשרות לעלות תמונה אז אכתוב :
זה הנתון שיש לי
21/09/2019 20:00:11
הנוסחה :
(MID(A4,8,2)& ":" &MID(A4,10,2)& ":" &MID(A4,12,2=
והתוצאה
33:46:06
נכון שאני לא טמבל וניסיתי לשנות את כל האפשרויות כולל שינויים ב (MID(A16,12,2 את הסיפרה 12 שיניתי לכול המקומות האפשריות זה לא עובד
נסיתי גם עם אפשרות אחרת
זה הנתון שיש לי
01/09/2019 00:00:01
הכנסתי את הנוסחה הבאה
(RIGHT(A3,20=
והתוצאה
43709.0000115741
ניסיתי לשנות בעצוב תא למה ש אפשרי .. לא עובד
יהודה שלום,
שים לב שהטיפ נועד לשליפת נתונים מטקסט,
תאריך שהיה כטקסט והפכתי לתאריך.
כאשר אתה רוצה לשלוף נתונים מתאריך ושעה חוקיים ,
אתה לא משתמש בפונקציות טקסט אלא בפונקציות תאריך ושעה.
לדוגמא day לשליפת היום בחודש מהתאריך
זו הסיבה שלא הצלחת.
אני רוצה לעשות סיכום של כל החלונות בטור Q אבל אני רוצה שרק חלונות שבאותו שורה שבטור B מופיע הספרה 12 ייכנס לחישוב
זה הנוסחה שעשיתי וכמובן זה לא עובד ..איפה טעיתי?
(IF(B:B=12,SUM(Q33:Q400),0=
יהודה, הנוסחה שמתאימה היא SUMIF
בהצלחה!
היי ,
יש לי בתיבה אחת של אקסל נתונים מחוברים אחד אחרי השני כך:
Uv_89
FALSE
Uv_88
TRUE
אני צריכה להפריד את ה TRUE/FALSe לעמודה נפרדת כך שיהיו במקביל למלל שמעליהם , לדוגמא:
Uv_89 בעמודה אחת ובשניה FALSE
איך ניתן לעשות זאת?
תודה
שלום ניצן,
אפשר לסנן לפי שווה TRUE או FALSE ולהעתיק את הנתונים למקום אחר
ואז לסנן לפי שונה TRUE והם מ FALSE ולהעתיק לעמודה ליד העמודה הקודמת.
בהצלחה!
ריציתי לדעת איך מוציאים נקודה מאמצע תא לדוגמא 26.55 להוציא את הנקודה
אם המטרה לבטל נקודה עשרונית,
שלא יראו את המקומות אחרי הנקודה.
יש אפשרות בעיצוב תא CTRL+1 , מספר, לסמן 0 מספרים אחרי הנקודה.
או בכרטיסייה בית בקבוצת מספר לחצן של הפחת מקומות עשרוניים.
שלום,
מה התחביר לכתובת של תא הנמצא בחוברת עבודה אחרת?
נעה תכתבי את הסימן שווה =
תעברי לחוברת עבודה ממנה את רוצה נתון ותבחרי אותו.
אקסל לבד יתן לך את התחביר
הי, נהנתי מאוד לקרא ולדעת
יש סיכוי לשאול אותך שאלה בטלפון?
קצת חשוב לי להרגע
תודה רבה!!
היי מיכל
יש לי עמודה שרשומים בה ספרות
אני רוצה לחתוך את 6 הספרות מהסוף ולהשאיר בעמודה את שאר הספרות
יש לציין שמספר הספרות בכל תא הוא שונה .
היי גדעון,
הפונקציה RIGHT גוזרת מהסוף כמה תוים שרוצים , לא משנה לה מה אורך המחרוזת
היי מיכל, רציתי לדעת איך ניתן לחלץ את המילה השנייה מתוך שורת טקסט שכתובה באנגלית?
תודה מראש:)
עם הפונקציה FIND את מחפשת רווח , תו אחריו צריך להתחיל לחלץ עד הרווח השני
היי
מחפש את הפונקצייה הנכונה.
אני צריך מהמחרוזת הזו S23-0023-01 24906 שלפעמים נראית כך S23875 24906
לטור נפרד את:
-S
-המספרים שאחרי האות S (לפעמים זה 5 תווים ולפעמים 11)
-5 מספרים אחרונים
ניסיתי גם דרך MID וגם דרך FIND
מהי הדרך הנכונה?
תודה רבה !
נועם,
בהנחה שהאות S ראשטנה אפשר לשלוף אותה עם הפונקציה LEFT
את שאר התוים/מספרים אפשר לשלוף עם הפונקציה RIGHT וכדי לדעת כמה תוים להשתמש בפונקציה LEN פחות 1 .
בהצלחה!
הי מיכל,
יש לי תאים עם הרבה מספרים אני צריכה מכל התאים למחוק רק את התו הרביעי שבכולם אמו להיות 0 לדוגמה – 153020002
איך אני יכולה לעשות זאת ע"י נוסחה?
שלום רות,
את צריכה להשתמש בפונקציות טקסט, אני מצרפת לך דוגמא.
נניח שהמספר נמצא בתא A1
=LEFT(A1,3)&RIGHT(A1,LEN(A1)-4)
בהצלחה!