TEXTBEFORE function | Splitting names from professional designations

/in /by

The TEXTBEFORE functions was recently released in this video I show 3 uses for TEXTBEFORE, it’s strengths and limitations.

In this example we have names and professional designations. The problem is some people have more than one designations, some have 1 designation and some folks have no designations.

BUT! We have to be careful. We can’t target all suffixes for removal.
REMOVE:
CFP, VP, MD and DDS
KEEP:
Jr. and III

#TEXTBEFORE
#SplittingNames
#SplittingText
For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

TOCOL and DROP | 2 of the 14 new Excel functions | Unwinding a grid

/in /by

14 new functions were released into Excel recently. In this video I cover two of them:
TOCOL: converts a rectangular matrix into a single column
DROP: retrieves a rectangular array and drops (eliminates) columns that aren’t needed. One thing to know: you can drop columns starting from the beginning or end of a range. You can eliminate, say, the first 3 columns, but you cannot drop columns from the middle of the range.

I show this along with the UNIQUE and SORT functions, and a dropdown list and conditional formatting.

#TOCOL
#DynamicArrays
#ConditionalFormatting

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Sum variable and custom ranges with XLOOKUP: the Victor Momoh Edition

/in /by

I was on a couple of Meetup Zoom calls and Victor Momoh was the host. He showed some fascinating stuff by using XLOOKUP to retrieve a range of data. He then used it to make sums and all kinds of other cool things. I share some of that in this video with you.

– Victor Momoh presenting at the Saudi Arabia Excel Meetup Group.

– See Victor Momoh explain XLOOKUP returning a range instead of a value

– Victor’s YouTube channel:
https://www.youtube.com/c/ExcelMoments/videos
– Download the workbook in this video:
https://datascopic.net/momoh
For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Extract Bold Font from a String of Text in a Cell – Baffmasta Edition

/in /by

My very first need for Excel’s VBA was back in 2009. I had a lot of data and the content I needed was the bold font in the cells. Some how, the non-bold font needed to go away.

Back then, some kind soul on an online forum wrote me some VBA code that I didn’t understand, but it worked. Today, I’ll show you how how I could have done this without VBA.

#baffmasta
#ExtractBoldText
#RemoveRegularText

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Mr. Excel Seating Chart Challenge: 100 people 10 tables 11 Sessions * Can everyone meet everyone?

/in /by

Mr. Excel, Bill Jelen, contacted me with a question that he’d been asked:
Can you arrange seating such that every person in attendance will sit at a table with every other person, at least once? The parameters:

– 100 people
– 10 tables
– 10 seats t each table
– 11 rounds

Bill concluded that there’s no way to connect everyone in only 11 rounds. He said the best he could achieve is 65%. Here is his video: https://youtu.be/0StppWCBgnY

I found this fascinating because it presents a real world scenarios where the ultimate goal can’t be achieved. We have to go back to the person who made the request and tell the truth. Then we have to ask if there’s any flexibility. Can we get more tables, bigger tables or add more rounds to the 11?

I came up with a solution that requires 19 rounds.

After thinking about my days as a wrestler, and round-robin tournaments, I could see adding people to 5-person teams, and then create an agenda that gets each of the 20 teams to meet, rather than try to work with 100 people.

In a real scenario, my 19 rounds would be a suggestion. The boss/client/friend/co-worker who made the request would have to decide if it’s an acceptable solution, or not.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel: Importing data from an image

/in /by

Recently, I’ve been getting mail that contains data that I need to get off of the paper and into Excel. Typing the data into Excel is one option. But I figured there must be better ways.

In this video I show 2 ways to get data by taking a picture, then converting the text from the data into something that Excel can use. You’ll see:
– Save the image in Microsoft Word as a PDF
– Import the image into OneNote, then use Extract Text from Image.

NOTE: All methods involve some kind of clean-up. The trick is to figure out which method results in the least mess.

0:00 Intro
1:55 A comment about the Excel phone app
2:20 Import work schedule
4:37 Import workshop data
7:16 Import from a magazine page
8:35 Why not import PDF using Power Query?
9:10 Outro

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query: Split by Variable Columns (BONUS: Road Trip Summary)

/in /by

It’s easy to use Power Query to split by, say, 5 columns. But, what if your data changes and needs to be split by 3 columns or 10 columns? That’s trickier–especially since Power Query will hard-code that 5 in the query and will ignore future changes.

This video shows how to get Power Query to cooperate. It takes a few steps, but we get it done!

Also. You’ll get to see where I’ve been during my time away. I finally went on a road trip that I’d been putting off for 7 years.

#Roadtrip
#SplitVariableColumns
#SplitColumnsinExcel

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Power Query Challenge: Split by Multiple Delimiters | Merge All Columns

/in /by

Iranian Dr. Excel posted a challenge on LinkedIn:
How can you split columns when there are multiple delimiters? Ex:
Joe+Lisa+Francine/Kathy
Rita&Sal&Gene
Samantha/Denise

This was interesting for several reasons:

1. It’s very easy to do if you want a 1-and-done solution with just 3 delimiters. However,
2. If you want something truly dynamic, it involves solving 2 major problems with Power Query: splitting variable columns and merging all columns in a query.
3. I show an ugly but effective solution that I call The Elephant Through the Front Door Move.

First. When splitting columns with Power Query, a value gets hard-coded; e.g., if your initial split goes into 5 columns, the 5 is stuck, and if you later have 7 columns or 3 columns, that 5 is still there.
I found a solution to this here:

Split by Variable Columns in Power Query

Second. Sometimes we want to merge all of the columns in a query, but there’s no feature for that. But here is Power Query M-Code:

Merging of all columns in Power Query – regardless of their names

This was hard! But the solution I show is truly dynamic. If there are more delimiters in future data, they get picked up; if the number of columns grows or expands, Power Query will cooperate.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.

Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2

Excel NETWORKDAYS part 3: calculating custom weekends with Power Query

/in /by

In the previous 2 videos we’ve looked at calculating NETWORKDAYS and days off that aren’t just Saturday and Sunday. Thanks for Bill Szysz, Wayne Edmondson, XLarium and others for leading a lively discussion and offering really cool formula-based solutions. (The conversation is on this video: https://youtu.be/njC1AvEU3fg)

In this video I show you something I learned from Wayne and Bill. You can create your custom weekends by putting together a string of 1s and 0s that represent each weekday.

1 = day off
0 = workday
The string starts with Monday.

If you work (or go to school) only on Tuesdays and Wednesdays, your string would look like this:
“1010111”
Thus:

=NETWORKDAYS.INTL(Start_Date, End_Date, “1010111”, [Holidays])

You’ll also see Power Query, replace values and merge columns in order to simplify calculating net work days in Excel.

For a list of my Excel courses at Lynda/LinkedIn:
https://www.linkedin.com/learning/instructors/oz-du-soleil
There are courses on Power Query, Good spreadsheet habits, and a weekly Excel challenge that comes out every Friday.
Website: https://ozdusoleil.com

My book: Guerrilla Data Analysis 2nd Edition

My old blog: http://datascopic.net/blog-2-2