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 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