Power Query- Remove model number from description

ronnyf85

New Member
Joined
May 4, 2024
Messages
2
Office Version
  1. Prefer Not To Say
Platform
  1. Windows
  2. Mobile
Hello is there a formula that i can use to get this result? I receive other Excel sheets like this from different sources, so if there is a general formula that i can use and tailor them, that would be great. I am a beginner still so i tried to do Text Remove and was getting no where. Please help, thank you! = )
power-query-remove-item-numbers-from-description-v0-h0h9varnkeyc1.png
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
You need a rule, or rules that you can apply. If you can’t write down the rule, you can’t do it with Power Query.
One rule appears to be, if the word fragment starts with DYN, then remove it. There are complications however. In the Pedi product, -if doesn’t seem to have a space before the product code. Given what I can see, this is what I would do, in order. All of these steps are in the menus
For the first column
1. Replace -if with <space>-if<space>. You would use actual spaces, not <space>
2. Replace two spaces with one space
3. Trim the column (removing leading and trailing spaces
4. Add and index column
5. Split the original column based on delimiter, spaces, into rows
6. I would then add another index row, in case the text gets out of order. I’m not sure if it’s needed.
7. Add a conditional column to identify the codes. Something like “if the text column begins with “DYN”, then true”. You can then use this column to remove all codes beginning with DYN
8. Repeat for all other code patterns
9. Sort on the second index column, again, I’m not sure if it’s needed
10. Remove second index column
11 pivot the text column by selecting just that column and pivoting. There is an option to select. I think it is “do not aggregate” but that’s from memory.
12. delete the index column added in step 4.

This is from memory, but directionally the approach I would use. It may need to be tweaked.
Ken and I have a training course (Power Query Academy) at Skillwave Training that has 27 hours of coached learning content if you are interested in comprehensive learning.
 
Upvote 1
Great, I will try your suggestions. Thank you!
 
Upvote 0

Forum statistics

Threads
1,216,500
Messages
6,131,016
Members
449,615
Latest member
Nic0la

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