Split string after ";" into new Colum in Excel

Programming
, , ,
1

`

I want to separate the strings beginning with “NCT” from the following column of data:

CTR20223447; NCT05718167;    GDCT0476127; TQB2450-III-12
NCT05671510; GDCT0475865; PRESERVE-003  
NCT05487391; QL1706-304; CTR20222281; GDCT0462845; DUBHE-L-304

How can I extract all strings beginning with “NCT” into a separate column for each respective row?

2

To extract all strings beginning with “NCT” into separate columns for each respective row, you can use the following formula in Excel:

Assuming the data is in column A, starting from cell A1, you can use the formula =IFERROR(MID($A1,SEARCH("NCT",$A1),IFERROR(SEARCH("; ",$A1)-SEARCH("NCT",$A1),LEN($A1))),"") in cell B1. Then, drag the formula down to fill the remaining cells in column B.

This formula uses the MID, SEARCH, and IFERROR functions to extract the desired strings. It searches for “NCT” in each cell, then uses the MID function to extract the substring starting from “NCT”. The IFERROR function is used to handle cases where there is no “;” delimiter after the “NCT” substring.

The result will be the extracted strings in a separate column, with empty cells where no “NCT” string is found.