Automated Edit/Replace

jbfrank

Active Member
Joined
Oct 13, 2003
Messages
290
I'm trying to find a way to automate the Edit --> Replace function in Excel.

Every morning, I pull a report containing well over a hundred different SKU's. Each SKU has it's own suffix code. For example, let's say a package of golf balls has 35600 as the SKU. At the end of that SKU, we might have a /05 to designate pink golf balls rather than white (which would have an /01. With the suffix, the entire SKU would look like this: 35600/01.

I would like to remove the /01 from each of the SKU's. If I create a list in Excel of all of the slash codes available, how do I get the Edit --> Replace function to automate so that every slash code in that list is removed from a set column?

Thank you in advance for your help.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
=LEFT(G9,FIND("/",G9)-1)


in another column, will give all characters to the left of the "/" character.


Alternatively, you could use Data, Text to Columns, with the "/" as the delimiter, However this does not preserve your original data.
 

jbfrank

Active Member
Joined
Oct 13, 2003
Messages
290
Here's the only issue, which I should have specified initially. Not all of our suffixes begin with a "/". Some suffixes are just a series of numbers. Using my example above, we might have a SKU 35600 where the suffix is 901. Other suffixes start with a "-".

I've already compiled a list of every possible suffix code my company has. What I'm trying to do is tell Excel to highlight column G and eliminate any suffix found in cells H1:H150.

Is that possible?
 

SteveO59L

Well-known Member
Joined
Apr 21, 2004
Messages
7,896

ADVERTISEMENT

Ok

Does the SKU always comprise of 9 characters ?
 

jbfrank

Active Member
Joined
Oct 13, 2003
Messages
290
No. I know this is really confusing. We've been trying to simplify our suffix codes for a while now. Unfortunately, this is what I have to work with.
 

Mc Culloch

Board Regular
Joined
Jul 21, 2005
Messages
68
If there is no logic to your codes then try a lookup table. In one column have a list of all the FULL codes, in the next column have the applicable suffix. Use this 2master list2 to do a VLOOKUP on and return the correct suffix to your main sheet.

Only draw back is that you will have to keep your master list upto date - best to get a logical coding convention.

Hope this helps
 

Forum statistics

Threads
1,141,160
Messages
5,704,649
Members
421,360
Latest member
Rhodia

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