How to split text to support reporting?

Hi,

I am bumping into a design / Smartsheet limitation issue. I use an request inventory sheet to track client change requests. CR are submitted via a form. One of the field helps tracking the category of the CR (business feature grouper). I had to concatenate 2 types of data to workaround the form and the fact that I cannot have a conditional menu. Example: if 01) core claim processing is selected, then sub menu should display only A,B,C. See information and table below for more details.

What I'd like to do now is report only on the business feature grouper, not the sub-groupers (so 01) Core Claim Processing, 02) Payroll Processing, 03) Claim Correspondence, etc.). I am manually capturing the information in dedicated column that is hidden and that is used in a report.

How can I either use a formula to split the grouper field and capture only the first part of it?

OR how can I redesign my form to capture the grouper / sub grouper in an easy way?

Current drop down menu, to support form submission:

01) Core Claim Processing 10) Leave Plan Configuration (Fed FMLA, State FMLA, ADA, Military, and all flavors of company leaves)

01) Core Claim Processing 20) Disability Plan Design(STD, LTD, PFL, STAT, etc.)

01) Core Claim Processing 30) Claim Workflow

01) Core Claim Processing 40) Claim Tasking

01) Core Claim Processing 50) Claim Calculations

01) Core Claim Processing 60) Intake Scripts

01) Core Claim Processing 98) TBD

01) Core Claim Processing 99) Other

02) Payroll Processing 10) Benefit Calculations

02) Payroll Processing 98) TBD

02) Payroll Processing 99) Other

03) Claim Correspondence 10) Letters

03) Claim Correspondence 20) Emails

03) Claim Correspondence 30) Text

03) Claim Correspondence 40) Forms

03) Claim Correspondence 98) TBD

03) Claim Correspondence 99) Other

04) Inbound File Feed Integration From ER or Agent 10) SSO Setup

04) Inbound File Feed Integration From ER or Agent 20) Roster File

04) Inbound File Feed Integration From ER or Agent 30) API Integration

04) Inbound File Feed Integration From ER or Agent 40) Conversion of Takeover claims

04) Inbound File Feed Integration From ER or Agent 98) TBD

04) Inbound File Feed Integration From ER or Agent 99) Other

05) Outbound File Feed Integration To ER or Agent 10) Time Keeping File Feed

05) Outbound File Feed Integration To ER or Agent 20) Leave Status File Feed

05) Outbound File Feed Integration To ER or Agent 30) Data Warehouse File Feeds

05) Outbound File Feed Integration To ER or Agent 98) TBD

05) Outbound File Feed Integration To ER or Agent 99) Other

06) Digital Tools 10) Next Self Service Portal

06) Digital Tools 20) Self-Service Reporting

06) Digital Tools 98) TBD

06) Digital Tools 99) Other

07) External Vendor Integration 10) Cigna Better Integration

07) External Vendor Integration 98) TBD

07) External Vendor Integration 99) Other

08) Telephony / IVR Integration

09) Information Security

10) Partner Core Integrations (Carrier) 10) CIGNA

10) Partner Core Integrations (Carrier) 20) GLIC

10) Partner Core Integrations (Carrier) 30 )GLIC

10) Partner Core Integrations (Carrier) 40) Guardian

10) Partner Core Integrations (Carrier) 50) Co-Source

10) Partner Core Integrations (Carrier) 98) TBD

10) Partner Core Integrations (Carrier) 99) Other


Mapping table:


Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!