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



      • Sticky Posts

      • Announcing Zoho Analytics 6.0 Beta!

        We are delighted to open up the next major version, Zoho Analytics 6.0 Beta! The new version comes packed with a wide range of functionalities for all persona, namely business users, data analysts, data engineers, and data scientists. Zoho Analytics team

      • What's New in Zoho Analytics - August 2024

        Hello Users! We are back with the latest updates and enhancements made to Zoho Analytics. Keep reading to learn more about them. Connect to the data hosted in the cloud without allow-listing the IP addresses Utilize Zoho Databridge to connect to the data

      • We are coming to your city! Zoho Analytics Community Meetup

        Hello, business leaders and data enthusiasts! We are delighted to announce that registrations are now open for the ZUG meetups, and we can't wait for you to be a part of them. Our in-house analytics experts are geared up to lead discussions on constructing

      • Zoho Analytics: 2021 Look Back

        As we start a new year in 2022, here's some of our top moments from 2021. Zoho Analytics in 2021

      • [Customer Talk] PREMO Group's Analyst Interview at Zoho Day 2022

        Premo Group, a 50 year old Spanish Manufacturing Company, has been our long-standing customer with #ZohoAnalytics. They've been using our platform for their end-to-end, unified business analytics solution.  Hear more from Claudio Cabeza, Director at PREMO