Extracting data from existing columns to form new columns

Hi, 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 Sub...

excel

Formulas and Functions

DexterG_III
Sep 08, 2022
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.

Dexter

DexterG_III

Iron Contributor

Sep 08, 2022

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.

Dexter

cfitzg3
Copper Contributor
Sep 08, 2022
DexterG_III Thank you so much!!

Forum Discussion

Extracting data from existing columns to form new columns

Resources