Breaking down a field into separate fields.

[Database Designs]

I have a part number that needs to be broken down to populate other fields. For instance the part number TE5A needs to broken down where "T" represents the manufacturer, "E" is the row the part is in and "5A" is the bin number. The problem I'm having is when we run into a part number such as TBB15A where "BB" is the row and "15A" is the bin number. Any ideas on how I can break these down?

[comment]

What is the rule here? Does the first digit signify the end of the row and the beginning of a bin number?

[Database Designs]

First letter represents the manufacturer. The next letter (or 2) represent the row, the digit followed by a letter represent the bin. The digit could be between 1 & 99

[Raybaudi]

Try:

manufacturer:

Left ( part number ; 1 )

row:

Let(

r = Middle ( part number ; 2 ; 2 ) ;

Filter ( r ; KanjiNumeral ( r ) )

)

bin number:

Substitute ( part number ; manufacturer & row ; "" )

[Database Designs]

That's perfect. Thank you so much.

[Raybaudi]

I simplyfied the row calculation.

[comment]

bin number:

Substitute ( part number ; manufacturer & row ; "" )

That's a rather bold assumption, isn't it?

[Raybaudi]

I made this argument: if manufacturer has always a letter and row has always one (or two) letters ( IOW they haven't any digit ), then that calculation should work.

[comment]

If bin number has only one letter.

[Raybaudi]

the digit followed by a letter represent the bin. The digit could be between 1 & 99

Edit: oh, only now I understand you... you think that the bin number could have more than a single letter!

Yes, in that case the calculation may fail and it will better to use something like:

Let(

l = Length ( part number ) - Length ( row ) - 1 ;

Right ( part number ; l )

)

or:

Replace ( part number ; 1 ; Length ( row ) + 1 ; "" )

[Database Designs]

There is one more thing I didn't realize. My parts are categorized as either bronze or aluminum. This calculaion works great if the category is bronze. If my category is aluminum my part number will look like TPM1 2 where T is still the manufacturer but PM1 is now my row and 2 is my bin. there is a space between PM1 and 2. My rows should always be 3 characters but my bin can be 2 digits.

[Raybaudi]

Try:

manufacturer:

Left ( part number ; 1 )

row:

Let([

A = Middle ( part number ; 2 ; 3 ) ;

B = Middle ( part number ; 2 ; 2 )

];

Case(

category = "Aluminium" ; A ;

category = "Bronze" ; Filter ( B ; KanjiNumeral ( B ) )

)

)

bin number:

Trim ( Replace ( part number ; 1 ; Length ( row ) + 1 ; "" ) )

[Database Designs]

Fantastic. Thank you again for your expertise. I would have never figured that out.