Forum Discussion

Sue Ditsch's avatar
Sue Ditsch
Copper Contributor
Mar 29, 2017

Macro to Extract Data from 500 blocks of Information. Help!

I have attached a file with just two blocks of information that was downloaded from another program. There are over 500 blocks in total.  I need to first Replace All occurrences of "Totals: $" with ...
Macros & VBA
Need Help
  • Sue Ditsch's avatar
    Sue Ditsch
    Copper Contributor
    Apr 04, 2017

    Thank you so much, Wyn!  Genius! Everything worked like a charm! I really appreciate the time you took to fix the file and then communicate to me how to then do it myself.  You sure saved me a ton of time and effort trying to figure this all out on my own.  Really appreciate it!

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Apr 04, 2017

      No problem,  Power Query is definitely worth learning about.   

       

      If you do a lot of this sort of thing then I can't urge you strongly enough to attend a course on it.

       

      Also the best book on the topic is this M is for Data Monkey

       

       

      Good luck

      • Sue Ditsch's avatar
        Sue Ditsch
        Copper Contributor
        Apr 04, 2017

        Thanks again, Wyn.  I'm a retired instructor who helps friends out when they get stuck using Excel. In this case, our church needed the help and called me. I will go over there today to show them what you did. You are saving HOURS of time for someone and she really will appreciate it. Thanks for the book recommendation as well! I can pass this along to several people I know. 

  • Sue Ditsch's avatar
    Sue Ditsch
    Copper Contributor
    Apr 03, 2017

    Thanks so much, Wyn, for getting back to me!  I was beginning to worry that no one thought my problem was problem enough!

     

    I did download the 32 bit version of Power Query.  I am running Excel 2013, but it wouldn't let me download the 64 bit version.  I also pulled up some help information on how to use this feature, never having heard of it.  So, even though the name, street address, city/state/Zip are in the same cell, it will extract the information into separate columns? That would be the fix - although on a separate cell below this contact information is the dollar amount.  I would want that in the final column.  Then I could easily delete/hide the columns I don't want and work with the ones I do.

     

    I would appreciate any insight you can give me.  I played around with it a little, but didn't get it to work. 

    I also tried the Data/Text to Columns feature in Excel . . . didn't quite get that to work either, but it was close. 

     

    Again, I REALLY APPRECIATE your help!  Love that I don't have to write a macro!  Yea!

    • Wyn Hopkins's avatar
      Wyn Hopkins
      MVP
      Apr 03, 2017

      Hi Sue

       

      32 bit is fine (most people have that version).

       

      If you download my file (in the previous reply) you can then point it at the correct source file on your system and it should do the transformation for you.

       

      To repoint it at your file:

      Once you've downloaded my file go to the Power Query ribbon and select the Data Source Settings button....

       

       

       

      then click on Change Source and go and find your source file that needs formatting

       

      After that click on Show Pane to show the query panel down the right hand side

       

      Finally RIGHT-CLICK in the green table and select refresh.  

       

      Power Query should then pull the data from the source file and clean it up.

       

      To look at what the query is doing you can double click on the query in the Query Pane on the right and then click on each of the "Applied Steps" starting with source and working your way down.

       

       

       

      For more info on Power Query I've done some videos

       

      https://www.linkedin.com/today/author/wynhopkins

       

      Check out Power Query part 1 and 2

       

Resources