Need a solution for Parsing a value within a block of text

I have a schedule that automatically adds appointments from teams. Because of the way the data is retrieved it will pull all the meeting info into a block of text in the "Task" Column. I am not sure how this is done but I know its done using a bot in MS teams.

Task column will always pull data that looks like this (without the commas): Project Code, Typing, Location, Appointment Name

Project code is 3-7 characters long, typing ranges from 2-10 characters long, location will be 3-4 characters long and appointment names can vary in length. This is why I have been using CONTIANS to find the location.

I have a location column that uses an ever growing nested IF function with various CONTAINS corresponding to different locations in the building (see below)

My current formula for parsing out the location is

=IFERROR(IF((CONTAINS("CR1", Task@row )), "CR1", IF((CONTAINS("CR2", Task@row )), "CR2", IF((CONTAINS("CR3", Task@row )), "CR3", IF((CONTAINS("CR4", Task@row )), "CR4", IF((CONTAINS("CR5", Task@row )), "CR5", IF((CONTAINS("CR6", Task@row )), "CR6", IF((CONTAINS("CR7", Task@row )), "CR7", …. formula continues for many locations.) "See Task Column")

We have a rather large building with over 100 rooms. The issue I am having is that whenever someone selects a room that hasn't been added to the formula yet I have to add that room to the formula or else it returns with the "See Task Column" that I built into the formula so I know when I need to add another room.

Does anyone have a clever way for me to automate this so that I no longer have to update the formula every time a new room is added? Maybe using another sheet containing all the rooms and making a formula with CONTAINS and then INDEX COLLECT a location?

Any suggestions are appreciated.

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Are you able to provide some screenshots for context?

marc4

Can't you use FIND to find the location of the letters CR, then feed that to another FIND to find the location of the next space (or whatever separates the Location from Appointment Name). Use the difference to extract either 3 or 4 characters starting with the CR.

/marc

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.