data cleansing

  1. S

    Best method of combining many Excel sheets and workbooks

    Hi So I have a bit of a data cleansing problem for work. I have approximately 20 workbooks all containing approx 25 sheets and I've been asked to combine all of this data into one master sheet. The problem is that whilst all of the sheets have some of the data that we need, the column...
  2. A

    Recording Macros using multiple workbooks but workbooks name kep changing!

    Hi all, I am trying to automate a data cleansing and couple of Lookup activities in my weekly report by recording macros. The problem is the weekly raw data workbooks keep changes based on the week or other criteria. I was wondering if there is a way to solve this problem? I tried to copy and...
  3. B

    Power Query - Data Cleaning Question

    I'm trying to use Power Query to clean up some data and I've hit a road block. My knowledge in power query is very limited so I'm hoping someone with more experience can help me out. I've attached a link with screenshots of the data model (original.png) and the desired outcome (desired.png) I...
  4. B

    Data scrubbing a list of street addresses - how to trim off street number?

    Hi all, Hoping to find some help on this one! I have a list of thousands of addresses containing numbers and street names, and I'm trying to "shave off" the number (and sometimes, unit name - e.g. 15/A or 64C) from this list in order to isolate the street name. Unfortunately there are no clear...
  5. T

    Find & Replace cells containing specific value with another value

    Hi all, I'm looking to execute a find & replace on a dataset whereby anything containing the value 'xxx' is replaced with the value 'xxx'. For example, in a set of: yyyxxxyyy hhhxxxhhh xxxhhhhhh wwwaaaxxx xhhaxxghh After the find & replace, I would like the cells to read: xxx xxx xxx xxx...
  6. S

    DATA CLEANSE: how to remove gibberish around the DATE in cells, so just leaving the date?

    How do I remove characters around the date in a cell for eg, íf someone stupid has put in a cell ' exp 10/10/2014 year to go' - i want to remove all surrounding text or number (all gibberish) so it is just the data i.e. '10/10/2014' in the cell so i can analyse it. For work, please help soon...
  7. A

    Data Correction

    I have a large data of families with the information about age, sex, marital status etc. of each member in the columns. The marital status column has somehow been inverted for each family during extraction. Let me explain this with an example. Following is the format of my data <table...
  8. T

    data cleansing once more

    Hi Everybody, I am supposed to do client analysis, howevere, I have a big problem with data quality starting with client's names :( I am supposed to match our key accounts for the largest companies in our region. The official list contains full official names of the companies, e.g. John...
  9. A

    Using Google maps to autocorrect a spreadsheet of badly formatted addresses

    Hi guys, Is it possible to upload a spreadsheet of badly formatted addresses and use google maps to autocorrect them to their proper addresses and address form? Thanks in advance,

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top