How do I trim and replace in a formula?

How do I trim and replace in a formula?

Hello,

Apologies in advance for the length of this post, but it requires background information. I am trying to do something in Zoho Reports that I have no problems with in Excel (or Access). 

Background:

We make tooling used in sheet metal fabrication. Our part numbers are either fixed or variable. The variable part numbers have always caused headaches for sales analysis. Here are a couple of examples of variable part numbers:
  • PAAA0A025
  • PAAB2A0250X1000
  • The fifth character always indicates the shape, with “0” always meaning Round; any other number represents a shape, so the 2 in the second number indicates a Rectangle.
  • The sixth character is usually an “A” but it can change to any other letter and is used by our manufacturing department. 
  • Everything after the sixth character is the dimensional information of the tool, which we really don’t care about in sales analysis
  • Each part number is also assigned a tooling type:
    • Fixed Part Numbers are FIXED PN, BLANKS, or OTHER OPTIC
    • Variable Part Numbers are either PDS, SA or SS
Here is an example of a data extract from our system:
Year      PN             SumOfQTY TSTYLE TTYPE
2016      A0BKTLA       175 TTT       FIXED PN
2016      A0DE00VB         1 TTT       FIXED PN
2016      A0KB00BA         2 ULTRA    FIXED PN
2016      D0AA0C0297 1 TTT       PDS
2016      D0AA3A0320 1 TTT       PDS
2016      D0AA1E0040X0675 1 TTT       PDS
2016      D0AA2BD03         2 TTT       SS
2016      D0AA1BE01         2 TTT       SS
2016      D0AA3BE07         2 TTT       SS
2016      PAAB0A0272 1 TTT       PDS
2016      PAAB1F0250X0750      1 TTT       PDS
2016      MLN00014         1 TTT       OTHER OPTIC
2016      MLL00003         2 TTT       OTHER OPTIC
2016      PAAB0A1234B             8 TTT       BLANK
2016      PAAB0A0385B             3 TTT       BLANK
 

What I want to do:

When we look at variable part number sales, we want to look at the round or all shapes "families" (e.g., all D0AA0A round dies, or all PAAB_A shaped punches) and do not care about specific shapes or any dimensions.What I do is add a column called “PN Abbr” to Excel with a formula that:
  1. Trims the Variable Part Number to 6 characters
  2. If the Variable Part Number is for a round (i.e., 0 in space 5), then it replaces the “0” and sixth character with “0A” for consistency
    1. Example: D0AA0C0297 becomes D0AA0A so we can tabulate all of the D0AA0As
  3. If the Variable Part Number is for a shape (i.e., anything but “0” in space 5), then it replaces the fifth and sixth character with “_A” for consistency
    1. PAAB1F0250X0750 becomes PAAB_A so we can tabulate all of the PAAB shapes
  4. Leaves any Fixed Part Number alone

The Excel formula I used to get my desired result is:

=IF(OR(G2="FIXED PN",G2="SA",G2=”SS”),B2,LEFT(IF(MID($B2,5,1)<>"0",REPLACE($B2,5,2,"_A"), REPLACE($B2,5,2,"0A")),6))

(The first row below represents the Excel columns)
A B             C       D E       F            G
Year PN             PN Abbr   SumOfQTY Sales      TSTYLE TTYPE
2016 A0BKTLA         A0BKTLA       175 1750       TTT       FIXED PN
2016 A0DE00VB       A0DE00VB 1 100       TTT       FIXED PN
2016 A0KB00BA A0KB00BA 2 200       ULTRA      FIXED PN
2016 D0AA0C0297 D0AA0A         1 100       TTT       PDS
2016 D0AA3A0320 D0AA_A         1 100       TTT       PDS

How would I accomplish this with a calculated column in Zoho reports?  

Any help is appreciated!

Regards,
John Galich