removing prefix from part numbers

[22strider]

Hello Firends

I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?

Following are some of the examples of prefixes:
AB
GD
KR
BCD
FP-
TJ-
W

Thanks for your time
Rajesh

 

[schielrn]

I don't see any commonality between the prefixes? How many other prefixes are there? You could maybe set up a table of prefixes and then test that using an if statement or VBA code, but could a part number start with W and W not be the prefix?

 

[22strider]

I don't see any commonality between the prefixes? How many other prefixes are there? You could maybe set up a table of prefixes and then test that using an if statement or VBA code, but could a part number start with W and W not be the prefix?

You are correct there is no consistancy in the prefixes. You are correct about the part numbers actually starting with 'W'. I guess I'll have to take chance on that. If all other prefixes get sorted out I could handle 'W' seperately.

 

[schielrn]

Well how many prefixes approx are there? Is there a list of them that you can have in your spreadsheet? If so you can use the match formula to check if it has a prefix that is in the list. And what version of Excel are you using?

 

[22strider]

Well how many prefixes approx are there? Is there a list of them that you can have in your spreadsheet? If so you can use the match formula to check if it has a prefix that is in the list.

there are about 20 prefixes in total and I can have them in a table

 

[schielrn]

Also what version of excel are you using?

 

[22strider]

also what version of excel are you using?

2003

 

[schielrn]

Assuming your list is in column A, in my example it is in A1:A6 and the part numbers are in column D. The formula in column E looks at the first 2, 3 or 4 characters and then just takes them off if they are in the list. But it assumes that no other part numbers can start with those characters. If you have the prefix KR and another prefix of KRT it will only work for KR and not for KRT. Hope tI explained myself well enough of how this works:

Excel Workbook
	A	B	C	D	E
1	AB			12345	12345
2	GD			123456	123456
3	KR			AB12345	12345
4	BCD			FP-54321	54321
5	FP-			BCD6789	6789
6	TJ-			84756	84756
Sheet2

Cell Formulas
Range	Formula
E1	=IF(ISNUMBER(MATCH(LEFT(D1,2),$A$1:$A$6,0)),RIGHT(D1,LEN(D1)-2),IF(ISNUMBER(MATCH(LEFT(D1,3),$A$1:$A$6,0)),RIGHT(D1,LEN(D1)-3),IF(ISNUMBER(MATCH(LEFT(D1,4),$A$1:$A$6,0)),RIGHT(D1,LEN(D1)-4),D1)))
E2	=IF(ISNUMBER(MATCH(LEFT(D2,2),$A$1:$A$6,0)),RIGHT(D2,LEN(D2)-2),IF(ISNUMBER(MATCH(LEFT(D2,3),$A$1:$A$6,0)),RIGHT(D2,LEN(D2)-3),IF(ISNUMBER(MATCH(LEFT(D2,4),$A$1:$A$6,0)),RIGHT(D2,LEN(D2)-4),D2)))
E3	=IF(ISNUMBER(MATCH(LEFT(D3,2),$A$1:$A$6,0)),RIGHT(D3,LEN(D3)-2),IF(ISNUMBER(MATCH(LEFT(D3,3),$A$1:$A$6,0)),RIGHT(D3,LEN(D3)-3),IF(ISNUMBER(MATCH(LEFT(D3,4),$A$1:$A$6,0)),RIGHT(D3,LEN(D3)-4),D3)))
E4	=IF(ISNUMBER(MATCH(LEFT(D4,2),$A$1:$A$6,0)),RIGHT(D4,LEN(D4)-2),IF(ISNUMBER(MATCH(LEFT(D4,3),$A$1:$A$6,0)),RIGHT(D4,LEN(D4)-3),IF(ISNUMBER(MATCH(LEFT(D4,4),$A$1:$A$6,0)),RIGHT(D4,LEN(D4)-4),D4)))
E5	=IF(ISNUMBER(MATCH(LEFT(D5,2),$A$1:$A$6,0)),RIGHT(D5,LEN(D5)-2),IF(ISNUMBER(MATCH(LEFT(D5,3),$A$1:$A$6,0)),RIGHT(D5,LEN(D5)-3),IF(ISNUMBER(MATCH(LEFT(D5,4),$A$1:$A$6,0)),RIGHT(D5,LEN(D5)-4),D5)))
E6	=IF(ISNUMBER(MATCH(LEFT(D6,2),$A$1:$A$6,0)),RIGHT(D6,LEN(D6)-2),IF(ISNUMBER(MATCH(LEFT(D6,3),$A$1:$A$6,0)),RIGHT(D6,LEN(D6)-3),IF(ISNUMBER(MATCH(LEFT(D6,4),$A$1:$A$6,0)),RIGHT(D6,LEN(D6)-4),D6)))

 

[schielrn]

You could also do this in vba by using an if statement and using the macth function, but I don't have the time at the moment to write the code, but it should be hard, so someone may come along and help you with it.

 

[22strider]

You could also do this in vba by using an if statement and using the macth function, but I don't have the time at the moment to write the code, but it should be hard, so someone may come along and help you with it.

Thank you very much for your time.