How do you handle messy data with inconsistent spaces across hundreds of columns?
Our take
I just got handed a spreadsheet with over 300 columns and the data is a complete mess. Every single entry has a leading space before the actual text. Not trailing spaces just a space at the start of each cell. Someone apparently copy pasted from somewhere without trimming and now I have to clean this up for a report due Friday.
I know about the TRIM function but manually applying it to 300 columns one by one is going to take forever. I tried using find and replace with a space and replace with nothing but that also removes spaces between words which I need to keep. So thats not going to work.
Power Query seems like the obvious answer here but Im curious how others would approach this. If I load the whole table into Power Query I can select all columns and do a trim transform in one go which feels way faster. But I rarely work with this many columns and Im wondering if theres any performance downside or gotchas I should watch out for.
Also open to VBA solutions if anyone has a macro for this. Just trying to figure out the most efficient way before I commit to a method. Curious how the pros handle massive cleanup jobs like this.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- What’s the most frustrating part of cleaning messy Excel/CSV data?I’ve been working with a lot of messy spreadsheets lately (duplicates, inconsistent formatting, mismatched columns, etc.), and it feels like everyone runs into slightly different issues depending on their data. Some people rely on Power Query, while others do things manually, but I still see workflows break when the data isn’t consistent to begin with. Curious what tends to slow you down the most when cleaning or organizing data? Is it duplicates, formatting issues, inconsistent columns, or something else? submitted by /u/SmitleyData [link] [comments]
- How to handle data from different sources when columns are in different orders?I regularly get CSV exports from multiple clients. Each client uses their own column order. One puts names in column A and dates in column B, another swaps them. Manually rearranging every time is driving me crazy. What's your go-to method for standardizing columns from different sources? Power Query seems powerful but I'm not sure where to start. I've tried INDEX/MATCH with header lookups, but it gets messy when column names vary slightly. Also open to VBA solutions if they're reusable. Any tips or templates you'd recommend? submitted by /u/biggy_boy17 [link] [comments]
- What is your go to method for cleaning messy PDF data importsI have to pull data from PDF reports pretty often and it always seems to come in with random line breaks, weird spacing, or numbers formatted as text. Im curious what everyones preferred workflow is for this. Do you rely on Power Query's PDF connector, paste it in and use Text to Columns, or have you built a VBA macro to handle the cleanup. Just looking for some fresh ideas because my current method feels like it takes way too long. submitted by /u/Internal-Remove7223 [link] [comments]
- How do you handle messy client data without losing your sanity?I’ve been helping a client clean up their monthly reports and every single file they send is a different format, random merged cells, inconsistent headers, you name it, and I feel like I spend more time fixing structure than actually analyzing anything, so I’m curious how do you all deal with messy, inconsistent Excel data efficiently without rebuilding everything from scratch each time? submitted by /u/Calm-Mud3304 [link] [comments]