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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,910
Messages
5,541,541
Members
410,547
Latest member
htran4
Top