CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
user3067464
user3067464

Reputation: 11

Removing prefixes of variable lengths

Below is a snapshot list of part numbers I've exported out of our ERP system. The full list is thousands of parts long:

.121CZ1000
.015DC0036
..020DF0003
..030MD0021
..040NA0009
..010DB0171
...306AD0002
.075MC0161
..030MD0021
...321FC0001
.092DM0069
..120BZ0003
..120DZ0004
..120DZ0008
..123DZ0011
..128DZ0003
..134CZ0004
..139CZ0007
..156CZ0002
..165DZ0005
..166CZ0009
..174CZ0003

The BOM report I used to export has inserted .s to indicate the various levels within the BOM. I need to use these part numbers in formulas, such as VLOOKUP or MATCH, so I need to have the .s removed. I need a dynamic program that adjusts as the quantity of .s varies from row to row.

Upvotes: 0

Views: 172

Answers (1)

pnuts
pnuts

Reputation: 59485

Though not a VBA solution it appears that a formula such as: 

=SUBSTITUTE(A1,".",)

(copied down to suit) was all that was required.

Upvotes: 2

Related Questions