- 267
- 139 144
How to Learn Excel
Ukraine
เข้าร่วมเมื่อ 26 ก.พ. 2022
💼 Excel is easy to learn, but hard to master - I'm here to assist you with both.
📚 Tutorials Offered:
Microsoft Excel, VBA, Power BI, and more Office Applications
🤝 Passion for Learning: I'm enthusiastic about discovering new tools and techniques and love sharing them with you. Let's inspire each other to experiment and spread knowledge. Always ready to embrace new challenges, continually learning and
improving.
🎓 Background: Economist / Analyst in the Petroleum Gas Industry
📊 Experience: 12+ Years | Financial Forecasting | Data Mastery
🎬 Subscribe: Join me to improve your skills and access valuable content.
🤝 Reach out via email if you need personalized assistance or have any questions.
📚 Tutorials Offered:
Microsoft Excel, VBA, Power BI, and more Office Applications
🤝 Passion for Learning: I'm enthusiastic about discovering new tools and techniques and love sharing them with you. Let's inspire each other to experiment and spread knowledge. Always ready to embrace new challenges, continually learning and
improving.
🎓 Background: Economist / Analyst in the Petroleum Gas Industry
📊 Experience: 12+ Years | Financial Forecasting | Data Mastery
🎬 Subscribe: Join me to improve your skills and access valuable content.
🤝 Reach out via email if you need personalized assistance or have any questions.
Extract Text Using TEXTBEFORE & TEXTAFTER: Excel DAF Challenge 03
Extract Text Using TEXTBEFORE & TEXTAFTER: Excel DAF Challenge 03
Manually extracting text between specific characters like ( ), ** * **, [ ], or { } can be frustrating and time-consuming. You’d have to rely on complex MID, FIND, and LEN formulas, making it difficult to read and maintain. But with Excel 365’s TEXT functions, we can solve this problem in a few simple steps!
In this tutorial, we’ll walk through a step-by-step method to extract text between different character pairs using TEXTBEFORE and TEXTAFTER-all while keeping the formula clean and efficient.
Here’s what you’ll learn:
✔️ How TEXTBEFORE works: Extract everything before a specific closing character.
✔️ Why we use -1 in TEXTBEFORE: Capture text before the last closing character instead of the first.
✔️ Using TEXTAFTER: Remove the opening character to extract only the desired text.
✔️ Handling multiple character pairs: Work with parentheses, brackets, curly braces, and asterisks in a single formula.
✔️ Combining everything into one dynamic formula that works across different cases.
By the end of this tutorial, you’ll be able to:
✅ Extract text between different symbols dynamically without helper columns.
✅ Understand how TEXTBEFORE and TEXTAFTER process arrays.
✅ Apply this method to clean and structure data efficiently.
💡 Pro Tip: If your data has inconsistent formats, consider using Power Query for a more structured approach!
🔔 Don’t forget to like, comment, and subscribe for more Excel tutorials!
Links:
Challenge Author:
Omid Motamedisedeh
www.linkedin.com/in/omid-motamedisedeh-74aba166Original Challenge Post:
www.linkedin.com/posts/omid-motamedisedeh-74aba166_powerabrquery-excel-powerabrqueryabrtips-activity-7266918581626359808-8XMH?
Download the Example File:
crispomwangi-my.sharepoint.com/:x:/g/personal/crispo_crispexcel_com/ETfEfdDWa5JFmEkd86GPEeAB5pGbm4jldGHbNcAHrEyEIA?rtime=HdOwHzlC3Ug
#Excel #DynamicArrayFormulas #HowToLearnExcel
Manually extracting text between specific characters like ( ), ** * **, [ ], or { } can be frustrating and time-consuming. You’d have to rely on complex MID, FIND, and LEN formulas, making it difficult to read and maintain. But with Excel 365’s TEXT functions, we can solve this problem in a few simple steps!
In this tutorial, we’ll walk through a step-by-step method to extract text between different character pairs using TEXTBEFORE and TEXTAFTER-all while keeping the formula clean and efficient.
Here’s what you’ll learn:
✔️ How TEXTBEFORE works: Extract everything before a specific closing character.
✔️ Why we use -1 in TEXTBEFORE: Capture text before the last closing character instead of the first.
✔️ Using TEXTAFTER: Remove the opening character to extract only the desired text.
✔️ Handling multiple character pairs: Work with parentheses, brackets, curly braces, and asterisks in a single formula.
✔️ Combining everything into one dynamic formula that works across different cases.
By the end of this tutorial, you’ll be able to:
✅ Extract text between different symbols dynamically without helper columns.
✅ Understand how TEXTBEFORE and TEXTAFTER process arrays.
✅ Apply this method to clean and structure data efficiently.
💡 Pro Tip: If your data has inconsistent formats, consider using Power Query for a more structured approach!
🔔 Don’t forget to like, comment, and subscribe for more Excel tutorials!
Links:
Challenge Author:
Omid Motamedisedeh
www.linkedin.com/in/omid-motamedisedeh-74aba166Original Challenge Post:
www.linkedin.com/posts/omid-motamedisedeh-74aba166_powerabrquery-excel-powerabrqueryabrtips-activity-7266918581626359808-8XMH?
Download the Example File:
crispomwangi-my.sharepoint.com/:x:/g/personal/crispo_crispexcel_com/ETfEfdDWa5JFmEkd86GPEeAB5pGbm4jldGHbNcAHrEyEIA?rtime=HdOwHzlC3Ug
#Excel #DynamicArrayFormulas #HowToLearnExcel
มุมมอง: 95
วีดีโอ
Ranking Data with GROUPBY, SEQUENCE functions: Excel DAF Challenge 02
มุมมอง 1422 ชั่วโมงที่ผ่านมา
Ranking Data with GROUPBY, SEQUENCE functions: Excel DAF Challenge 02 Struggling to rank data dynamically in Excel? In this challenge, we’ll rank fruits based on demand while combining ties into a single entry-all using Dynamic Array Formulas! With Excel 365’s GROUPBY function, we can efficiently group, sort, and format our results, eliminating the need for manual adjustments. Plus, we’ll strea...
Rank and Group Data Like a Pro - Power Query Challenge 52
มุมมอง 2177 ชั่วโมงที่ผ่านมา
Rank and Group Data Like a Pro - Power Query Challenge 52 Do you need to rank products based on their demand while ensuring items with the same demand receive the same rank? In this challenge, we’ll use Power Query’s hidden ranking function to achieve this and group items efficiently. This technique is great for analyzing sales, organizing inventory, or preparing structured reports. In this tut...
Reshape Rows into Columns - Power Query Challenge 51
มุมมอง 80921 ชั่วโมงที่ผ่านมา
Reshape Rows into Columns - Power Query Challenge 51 Do you have data spread across multiple columns that you need to reshape into just a few? In this challenge, we’ll transform a table with five columns into three columns by grouping every three consecutive cells into a single row. Power Query makes this kind of reshaping not only possible but surprisingly straightforward! In this tutorial, I’...
RESHAPING Tables with TOCOL and WRAPROWS: Excel DAF Challenge 01
มุมมอง 38921 ชั่วโมงที่ผ่านมา
RESHAPING Tables with TOCOL and WRAPROWS: Excel DAF Challenge 01 Tired of spending hours reshaping messy Excel tables? If you’ve ever struggled to reorganize data spread across multiple columns, this tutorial is for you! In this video, we’ll tackle a common challenge: reshaping a table with 5 columns into 3 columns by arranging every 3 consecutive cells in a row. The solution? Excel's Dynamic A...
Transform One Messy Column into a Perfect Table - Power Query Challenge 50
มุมมอง 1.2Kวันที่ผ่านมา
Transform One Messy Column into a Perfect Table - Power Query Challenge 50 Do you have a single column filled with mixed data-dates, product names, and values-and don’t know how to clean it up? Power Query can help you turn that mess into a structured, easy-to-use table! In this challenge, I’ll show you how to organize mixed data types in a single column and create a clean, functional dataset f...
Shared Drive Full? Power Query to the Rescue!
มุมมอง 48614 วันที่ผ่านมา
Shared Drive Full? Power Query to the Rescue! Struggling to make sense of messy shared drives and overflowing folders? Shared network drives can be a nightmare-duplicate files, outdated documents, and mysterious storage hogs everywhere. And sometimes, corporate restrictions mean you can’t use fancy software to clean up the mess. But guess what? Power Query in Excel can be your secret weapon! In...
Group Every N Rows - Power Query Challenge 49
มุมมอง 52814 วันที่ผ่านมา
Group Every N Rows - Power Query Challenge 49 Do you need to group every N rows in Excel and calculate their totals but don’t know where to start? Power Query makes it easy! Whether you’re summarizing sales data, survey responses, or inventory counts, this step-by-step guide will show you how to group every 5 rows and sum up the quantities for each group. In this tutorial, I’ll walk you through...
Running Totals with a Condition - Power Query Challenge 48
มุมมอง 92014 วันที่ผ่านมา
Running Totals with a Condition - Power Query Challenge 48 Do you need to calculate running totals in Excel but only for rows that meet specific conditions, like defect-free units? Power Query makes it effortless! Whether you're tracking production data, sales, or quality metrics, this step-by-step guide will show you how to calculate dynamic running totals with precision. In this tutorial, I’l...
Group Customers by Month - Power Query Challenge 47
มุมมอง 39421 วันที่ผ่านมา
Group Customers by Month - Power Query Challenge 47 Do you need to group your data by month in Excel but don’t know where to start? Power Query makes it simple! Whether you're organizing sales data, customer orders, or event signups, this step-by-step guide will show you how to group your data by month and get a clean summary in no time. In this tutorial, I’ll walk you through the entire proces...
Power Query Reshaping Trio: Transpose, Pivot, and Unpivot in 3 MINUTES!
มุมมอง 39621 วันที่ผ่านมา
Power Query Reshaping Trio: Transpose, Pivot, and Unpivot in 3 MINUTES! Are your Excel tables messy and hard to work with? Struggling to figure out when to transpose, pivot, or unpivot your data? Trust me, I’ve been there too! These tools in Power Query can reshape your data into exactly what you need, but knowing which one to use isn’t always obvious. In this tutorial, we’ll simplify these thr...
Power Query or Excel? The Rounding Issue You Didn't See Coming!
มุมมอง 28421 วันที่ผ่านมา
Power Query or Excel? The Rounding Issue You Didn't See Coming! Rounding-it’s such a small thing, right? Wrong! When it comes to finance, reporting, or any data-sensitive work, rounding differences can lead to major discrepancies. And here’s the kicker: Excel and Power Query don’t round numbers the same way. In this tutorial, we’re diving into the critical differences between the default roundi...
The Smart Way to Remove Duplicate Rows That Offset - Power Query Challenge 46
มุมมอง 35428 วันที่ผ่านมา
The Smart Way to Remove Duplicate Rows That Offset - Power Query Challenge 46 Ready to tackle another Power Query challenge? In this tutorial, we’ll learn how to handle rows where duplicates offset each other, resulting in a zero-sum scenario. These duplicates can clutter your dataset and mislead your analysis, so it’s crucial to address them. In this video, I’ll guide you through a practical s...
Power Query Partial Lookup: Automate Text Categorization Like a Pro in 5 MINUTES!
มุมมอง 1.4K28 วันที่ผ่านมา
Power Query Partial Lookup: Automate Text Categorization Like a Pro in 5 MINUTES! Struggling to categorize messy data in Excel? Whether it's bank statements, customer feedback, or sales records, manual text categorization is tedious and prone to errors. But don’t worry-Power Query has you covered! In this tutorial, we’ll explore partial lookups in Power Query, a powerful technique that allows y...
Test Grading Process - Power Query Challenge 45
มุมมอง 344หลายเดือนก่อน
Test Grading Process - Power Query Challenge 45 Ready to level up your Power Query skills? In this tutorial, I’ll guide you through a practical challenge: marking student answers as correct or incorrect and dynamically calculating their scores. This challenge is perfect for those looking to master data transformations and logic-based calculations in Power Query. Whether you're handling test res...
How to Group Data by Quarters - Power Query Challenge 44
มุมมอง 361หลายเดือนก่อน
How to Group Data by Quarters - Power Query Challenge 44
The Power Query Fix You Didn’t Know You Needed
มุมมอง 542หลายเดือนก่อน
The Power Query Fix You Didn’t Know You Needed
Consecutive Grouping - Power Query Challenge 43
มุมมอง 688หลายเดือนก่อน
Consecutive Grouping - Power Query Challenge 43
Advanced Conditional Splitting - Power Query Challenge 42
มุมมอง 482หลายเดือนก่อน
Advanced Conditional Splitting - Power Query Challenge 42
Extracting the Last Entry in Groups - Power Query Challenge 41
มุมมอง 306หลายเดือนก่อน
Extracting the Last Entry in Groups - Power Query Challenge 41
How to Extract Information from Text - Power Query Challenge 40
มุมมอง 757หลายเดือนก่อน
How to Extract Information from Text - Power Query Challenge 40
Clean Up Data with Conditional Replacement - Power Query Challenge 39
มุมมอง 951หลายเดือนก่อน
Clean Up Data with Conditional Replacement - Power Query Challenge 39
How to Use Advanced Grouping - Power Query Challenge 38
มุมมอง 443หลายเดือนก่อน
How to Use Advanced Grouping - Power Query Challenge 38
Master VBA: Save Excel Sheets as Separate Files in One Click!
มุมมอง 150หลายเดือนก่อน
Master VBA: Save Excel Sheets as Separate Files in One Click!
Excel's Hidden Gem: Turn Pivot Filters into Individual Sheets!
มุมมอง 414หลายเดือนก่อน
Excel's Hidden Gem: Turn Pivot Filters into Individual Sheets!
Combine Data from EVERY Folder, File & Sheet with Power Query in 5 MINUTES!
มุมมอง 9842 หลายเดือนก่อน
Combine Data from EVERY Folder, File & Sheet with Power Query in 5 MINUTES!
STOP Struggling with Power Query Auto-Complete and Try This Instead!
มุมมอง 3142 หลายเดือนก่อน
STOP Struggling with Power Query Auto-Complete and Try This Instead!
How to Split & Pivot Data with Ease - Power Query Challenge 37
มุมมอง 4482 หลายเดือนก่อน
How to Split & Pivot Data with Ease - Power Query Challenge 37
Make Running Totals in Power Query 12x Faster With This Hack
มุมมอง 1.4K2 หลายเดือนก่อน
Make Running Totals in Power Query 12x Faster With This Hack
Calculate Quarter-To-Date in Excel: The Step-by-Step Guide
มุมมอง 2312 หลายเดือนก่อน
Calculate Quarter-To-Date in Excel: The Step-by-Step Guide
Amazing.
Thank you! Glad you liked it.
Brillinat sultion 👍❤
Thank you!
nice and simple, thanks for sharing
Glad you liked it!
Perfect 👍
Awesome as always❤, thanks
=LET(a,HSTACK(XMATCH(D4:D18,SORT(UNIQUE(D4:D18),,-1)),C4:C18),GROUPBY(TAKE(a,,1),TAKE(a,,-1),LAMBDA(x,TEXTJOIN(" ; ",,x)),,0)) My formula ex 365
Wow, interesting use of TAKE and LAMBDA. Nice!
Thank you
great as always 👍❤
Thanks again!
Just being curious, where do you take an inspiration for all of these handy examples?
For regular videos, I create scenarios based on typical office analyst tasks I've encountered before. For the challenges series, I feature problems designed by other talented creators (with their permission) and credit them in the video intro and description, so you can explore more of their work! They post new content regularly on LinkedIn, so you can definitely join in and participate as well! th-cam.com/users/postUgkxp6bt4IIKBn04nxv04Tkj23fLKAfCoac7
Thanks, I will try to make an ATS for HR with this technique
Great idea! I'm glad it was helpful!
let A = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], B = List.RemoveNulls(List.Combine(Table.ToRows(A))), C = Table.FromRows(List.Split(List.Transform(B, each if Value.Is(_, type datetime) then DateTime.Date(_) else (_)),3),{"Date","Product ID","Quantity"}) in C My solution
I love it! Great use of conditional List.Transform. You should definitely post it under the original LinkedIn post. A lot of folks there would enjoy it a lot.
@@howtolearnexcel I posted on Linkedln. tks
Hi, Power Query: how efficiently Combine Data from Multiple HTML Files with multiple Tables in each file stored in SharePoint
Hi! This one is a bit tricky. If the tables have similar structure, you can create the query by combining these two techniques: 1) To combine Sharepoint files: th-cam.com/video/-XE7HEZbQiY/w-d-xo.html 2) To Transform each html file: th-cam.com/video/aOd5sUf4yzM/w-d-xo.html If you can share 1 or 2 html files with some dummy data, I can provide some additional instructions. My email is in the channel description.
Brilliant as the Excel wrap row method 👍, thanks. This can also be done by unpivotting all columns: let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Unpivoted Columns" = Table.UnpivotOtherColumns(Source, {}, "Attribute", "Value"), #"Removed Columns" = Table.FromRows(List.Split(Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"})[Value],3), {"Date", "Product", "Quantity"}) in #"Removed Columns"
Sure. Awesome idea! Clean and concise, I like it. Great job!
Great Job. I've used this technique many times; I love it!
Thank you! Glad you liked it!
hope for more DAF challege, tks
Thanks! There’s a lot more coming soon. I’ll also try to revisit some older PQ challenges and tackle them using DAF from now on.
Brilliant as always. 👍❤. Thank you
You are too kind 😇 The PQ version will be released soon too. Thank you!
Brilliant👍
Thank you!
Very Nice hack... It would have been great only if you spoke better english for me to understand 😢
Thanks for the feedback! I’m sorry if my English made it a bit hard to understand. I’ll do my best to improve, step by step! Hopefully, the latest videos will be easier to follow. Appreciate your patience!
@howtolearnexcel Once again Thank you for this game changer excel hack... Will watch the video a couple of times more and learn it 👍
bravo❤
WOW! Really helpful and much needed. Thank you for sharing. 🎉
Thank you!
The example file is incorrect...
Thank you for the notice. I'll update the description right now. Example: docs.google.com/spreadsheets/d/13tW9CGiBHUoLgI4LHdRBj6JG5-OlgVYO?rtpof=true&usp=drive_fs
So good 👍👍
Hi 👋 Is there any solution to share an excel work sheet which has a tables or pivot tables
The link is here: docs.google.com/spreadsheets/d/13sIA_sssQvn9feXYnMpxQgGOw9oH_z3j?rtpof=true&usp=drive_fs
@ unfortunately this link not replying to my question 🙋♂️
Sorry, it seems I didn't understand the question correctly. Can you specify the problem a bit?
@ i have an excel work book contains pivot table, i need to share it with one of my colleagues as the work book on our shared drive , so when i do share it gives me an error of unable to share or change tables to ranges but i need to keep pivot table
Oh, I see. Shared Workbooks have severe limitations, and one in particular is the inability to use tables and pivot tables. Unfortunately I haven't seen a decent workaround yet. Unless you and your colleagues use a newer version of Excel. In Excel365 there is a feature called Co-authoring, which is the replacement for Shared Workbooks. It works with tables and pivot tables well: support.microsoft.com/en-us/office/collaborate-on-excel-workbooks-at-the-same-time-with-co-authoring-7152aa8b-b791-414c-a3bb-3024e46fb104
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], A = List.Transform({1..Table.RowCount(Source)},each Number.RoundDown((_-1)/5)+1), B = List.Combine(List.Skip(Table.ToColumns(Source),1)), C = List.Zip({A, B}), D = Table.Group(Table.FromRows(C, {"Group", "N"}), {"Group"}, {{"Quantity", each List.Sum([N])}}) in D My solution not add index column.
awesome❤
Thank you!
Very original solution, never seen before. Inspiring !!!
Thank you! I'm glad you liked it.
Excellent solution 👍❤
Thank you!
These are magnificent tricks plus 🍒 ??0.
Thanks, glad you like it!
👍
Awesome method. Thank you
Thank you so much! Glad to see you again here ;)
Sooo... Good . Thanks for sharing
A W E S O M E CONDENSING! ✌ 💥
Thank you! Glad to see you again here ;)
The group function is so underrated. Mastering it is essential. Thank you for this easy to digest vid. Straight to the point.
100% true. 'Group By' is really awesome combined with some M language knowledge.
simple and clear!
Thank you!
This is the perfect 'first principles' that we all should master and truly understand in order to progress to more complicated data wrangling! Thank you for sharing your knowledge!!
Thank you! I’m glad you found it helpful. I’m actually planning a whole series to dive deeper into the fundamentals and gradually tackle more complex data wrangling techniques.
@@howtolearnexcelMarvelous Idea 🎉
That's absolutely correct, tidy dataset is crucial in data analytics. Hadley Wickman has delved into this in his fundamental 2014 paper "Tidy Data"
Awesome addition. Gonna read it soon.
Creative use of the 2nd argument of the function Table.TransformColumns. With many columns to be transformed, your clever method makes the functions shorter and more dynamic. Thank you for sharing your wisdom 👍👍❤❤
brilliant 👍
great 👍👍
Thank you!
👍❤
👍❤
👍❤ Thank you
Sir, your videos are so good. It is really enjoyable to watch your videos. Thank you and look forward to more and more 👍❤
Wow, thank you! That truly made my day! I can’t wait to share more with you soon!
👍❤