How to parse a value from a concatenated field?

gjohnson.pcpro · 08/20/24

Hello!

I have a column in a sheet that is being populated with multiple pieces of information from Data Mesh. As shown in the example below, the cells in this column contain multiple dates. I would like to parse out a single date from this column (Actual Deadline Achieved) into another column. I don't need the text "Actual Deadline Achieved:" - just the date that follows that text.

Does anyone know how I can go about doing this? Example text and actual screenshot of a cell in this column are below:

"Baseline 1 - Pre-approval: 08/07/24
Baseline 2 - Post Approval:
Best Case Scenario: 07/19/24
Stretch Goal:
Actual Deadline Achieved: 07/29/24
% Complete: 100%"

tjbahhur · 08/20/24

Use the formula =RIGHT([Cell], 8). And make sure you're using a date column

gjohnson.pcpro · 08/20/24

https://community.smartsheet.com/discussion/comment/450268#Comment_450268

I appreciate your help, but that did not work. The result I got using that formula can be seen below. It seems to have just pulled the last 8 characters from the cell. Even if it's formatted as a date field, it still pulls letters and symbols. If I click the column properties setting "restrict to dates only" then it won't allow this formula to be input.

AravindGP · 08/21/24

Hi @gjohnson.pcpro

You can use this formula.

=MID([Column containing the information]@row, FIND("Actual Deadline Achieved", [Column containing the information]@row) + LEN("Actual Deadline Achieved: "), 8)

gjohnson.pcpro · 08/23/24

Hello @AravindGP

Thank you so much for taking the time to help. The formula you provided worked perfectly! One question - if the column being parsed does not have an Actual Deadline Achieved date yet, the destination formula returns a value of "% Compl". I understand why this is, but if possible I'd like the destination column to be blank if there is no Actual Deadline Achieved value. Is it possible to achieve this within the same formula?

Thank you again for your help. I really appreciate it.

AravindGP · 08/25/24

Hi @gjohnson.pcpro

Unfortunately, no. As the formula is just trying to find the immediate next 8 characters after the text string.

gjohnson.pcpro · 08/26/24

Understood. Thank you again for your help. I really appreciate it.

How to parse a value from a concatenated field?

Answers

Categories