Data Cleaning PRO Tip: Dealing with text & numbers in same column

Recently I had to deal with a weird data clean up scenario. My client (a large hotel chain) was getting room booking data with mixed format. The booking date column occasionally has a text value which needs to extracted and used for proper analysis. In this short tip video, let me show you how to deal with such problems using Excel.

⏱Video topics 👉
===============
0:00 - The text & numbers problem
0:39 - Avoiding the problem by removing text data
2:32 - How to separate and properly arrange data (with Power Query)

📗Sample file
============
If you want to practice this concept, here is a sample workbook.

👩‍💻👨‍🎓 LEARN MORE about Data Cleaning
=================================

📺 YOUTUBE VIDEOS:

👩‍💻 COURSES:

~

A data analyst walks in to the bar. A pretty lady smiles at him and walks over. She says, "You look cool. Here is my number. Text me your number."
The data analyst replies, "Sorry, but I don't TEXT() numbers" 🤦‍♂️😂

#datacleaning #excel

[chandoo_]

Just checking... Do you want to see a Pandas tutorial on how to clean this data? Like the comment if you want to.

[vivekchakraborty]

I never comment on any YT video, but your videos are really interesting and informative. Kudos to such content and Thanks for this

[Hopspowered]

Small token of appreciation. Several views lately and almost always save to my excel playlist. And you’re so entertaining as well. Even a rugby reference in a video!

[muhammadjawad]

I am lucky to have a teacher like you. keep up the good work.

[canirmalchoudhary]

2nd solution is awesome, I was thinking how data can be retained while it is being removed 😀.

[Azhar_Khan]

I was familiar with the first technique but today I learned the second technique. I'm becoming awesome. Thanks Sir

[MobinulHaq]

THANK YOU!! So many accounting softwares extract ledgers like this (with account heads between dates) and it has been so frustrating until now. This is REALLY helpful!

[khallodah]

easy but yet very valuable. thannk you

[binishthomas]

I might have not done this using power query [might have used fiters and really nice application. 👍

[warriorppr]

Hi Chandoo, Thanks a lot. i was looking for this solution and found this video by God's grace. Excellent, Appreciate it Bro.

[tpnphoonsiri]

Hi Chandoo, I love 2nd technique .This will my life easy than before.Thank you very much.

[theanalystkr]

Just started my journey into data analytics, thanks for these really interesting/intuitive videos!!

[arun.kumar.s]

Learned power query from you and will keep learning from you. Boom

[Seftehandle]

Great approach for people trying to transition to power query and later on dax. I loved how you gave the1st option for excel beginners as well as 2nd one for intermediate learners. Cant wait to see how will you transition to simple

[nethikarnarendra]

Amazing solution!! How to text.combine for only single column e.g. QnA in multiple rows separated by ? Delimiter (Q ends with?)

[namy]

good tips on data cleaning, Can't wait to apply this knowledge practically.. 👌🏻

[chrism]

Great video as always! Thanks Chandoo!

[Excelambda]

Great video!!✌
To solve with a formula:

=LET(ar, A2:D196, h, A1:D1, d, A2:A196, f, D2:D196,
a, IF(ar="", "", ar), s, SEQUENCE(ROWS(a)),
x, f="",
k, XLOOKUP(s, FILTER(s, x), FILTER(d, x), , 1),
VSTACK(h, FILTER(HSTACK(a, k), NOT(x))))

And dates are dates (numeric), and room numbers, numbers, so we can do other filtering etc

[vishalbhati]

Sir u don't need go to special. Just simply apply filter and choose blanks and press ctrl+ - boom.

[carpediemwithLesli]

This is great!!
I'm also looking for a formula for the following:
This is my issue: I need to keep my employees at 40 hours a week. So if on Thursday I do the time cards and let's say Monday thru Wednesday they are at 30 hours already. Assuming that on Thursday they will do 8 hours and they will only have 2 hours left for Friday; at what time on Friday they will go home when they get to 40 hours?
So the formula that I need is:
Accumulated hours ➕️ today's 8 hours; hours left for Friday equals at what time he will go home on friday..
Help!