Extracting data from existing columns to form new columns

New Contributor



I am looking to create two new columns for my report using column D and E. For example, I would like the "First Patient In Date" column to display dates for any milestones that say "First Subject In" (doesn't matter what is in parenthesis). I would like the same for the "last patient out date" column and then remove columns D & E. Is there an equation to do this?





2 Replies
best response confirmed by Hans Vogelaar (MVP)

@cfitzg3 Hello, if you're looking to conditionally place the date found in column E into one of the two new columns you can use the following formula for First Subject In column F, for example:

=IF(ISNUMBER(SEARCH("First Subject In",D2)),E2,"")

And the following for column G

=IF(ISNUMBER(SEARCH("Last Subject Out",D2)),E2,"")


However, you can't delete the dependent columns that the formulas now reference unless you copy and past as values. 


Depending on the source for this data, you could automate both the conditional placement of the date and deleting the original columns during the data import by using power query.  I can elaborate, if this is something you'd like to try.