Automated Edit/Replace

[jbfrank]

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.

 

[SteveO59L]

Would you consider a formula solution ?

 

[jbfrank]

Sure.

 

[SteveO59L]

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

 

[sweater_vests_rock]

hi jb.

i think this example should help:

book1
	A	B	C	D	E	F	G
1	SKU/COLOR		SKU	COLOR		Code	Color
2	35600/01		35600	RED		/01	RED
3	35600/02		35600	GREEN		/02	GREEN
4	35600/02		35600	GREEN		/03	BLUE
5	35600/03		35600	BLUE
6	35601/01		35601	RED
7	35601/01		35601	RED
8	35601/02		35601	GREEN
Sheet1

cheers. ben.

 

[jbfrank]

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]

Ok

Does the SKU always comprise of 9 characters ?

 

[jbfrank]

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.

 

[SteveO59L]

Dont suppose your suffix codes are always 3 characters ?

 

[Mc Culloch]

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