extract from raw data from column

[arperez]

Hey all,

I am stumped on how to clean up the data that can extract every single time one of our items is being mentioned in the column labeled description. I am more familiar with creating formulas than using some of the other more advanced tools.  I think Power Query might have something that will help me out but not too familiar with it.  Ideally, I'd like a formula to work with but if there's a better way, I am open to it.

Note: every one of our items starts exactly as "BAN" and then follow with 4 numbers e.g. BAN2345.  Some might be BAN130 etc.

There are over 700 rows.  Pretty time consuming to go through the entire list.

Goal: Have a cell for every item like column B and C.

would appreciate some assistance on the matter.

P.S. Any additional resources you'd recommend for me to continue to learn more on handling raw data and becoming a more advanced excel user would be helpful.

Thanks so much!

[#2]

@arperez Quite a novice myself on PQ, since I've only recently started using Excel for the PC on my Mac, but the attached workbook holds a PQ solution. Real experts may laugh at it, but it works.

[#3]

@Riny_van_Eekelen that's really great! looks to have worked well, not sure on how to do this.  can you point the way for me to do so?

Thanks so much!

[#4]

@arperez You're asking the deaf to lead the blind. There are literally thousands of sites out there that teach about Get & Transform ("GT")/ Power Query. As said, I have only recently discovered it and had no clue how to get started. Then, I stumbled over this site.

https://yodalearning.com/tutorials/power-query-tips-and-tricks/ 

It shows you very basics and some more advanced tricks. How to rename columns, how to import date, remove duplicates, replace values, unpivot columns, add filters, split columns etc. etc.

What I did in your case was to replicate the three examples you gave (Sheet1), pulled it into GT and performed the following steps:

1) Duplicate the one column table so that I could still see the original, but it's not needed.

2) Then I replaced PD, ( , ) and / with blanks to get rid of the "garbage" in the data.

3) Then I split the column at the first space. This separated "Drum set" into its own column, which I then removed.

4) Finally, I split the column at the point where a number changed to a character. That only affected the third row and split the two BAN-numbers between the 7 and the B.

5) Press "Close& Load" to create Sheet2.

In fact, this is no more than a fancy way of a doing "find and replace" and "text-to-columns" with the advantage that all your steps get recorded for you and keeping your original data (Sheet1) untouched.

With the help of the tips & tricks, I believe, you can get started as well. Just try. Make errors, screw up completely, get frustrated and then start from scratch. It might take few hours, but then you'll start to get the hang of it. From what I've seen, the sky (or perhaps only your own imagination) is the limit. Enjoy!

[#5]

awesome thanks so much!