Extract text from Cell String using delimiters “|” into separated columns

EgliAlv

Hello wonderful community,

I need your help on this. I have cells that I need their text extracted into columns. Text in cells is not always the same length, so I cannot just count the characters.

Example of cells look like

CANADA | ID NO. 78989 | “JUAN” | IN COHORT(S) 1, 5 | SPONSORED WITH LEGACY

USA | ID NO. 19667888 | “PATRICIA” | IN COHORT(S) 4 | SPONSORED WITH PHARMA LTD.

So, I want a column for

Country

CANADA

USA

ID NO.

ID NO. 78989

ID NO. 19667888

NAME

JUAN

PATRICIA

COHORT(S)

IN COHORT(S) 1, 5

IN COHORT(S) 4

Extra complexity bonus: is there a possibility in columns ID NO. & COHORT(S), to replace ID NO. by “ “ and IN COHORT(S) by “ “ , so these words are erased.

appreciate any help.

Leibel S

@EgliAlv

The FIND function is limited as it only finds the first instance. In the SUBSTITUTE function you can specify which instance you want to SUBSTITUTE. So first we SUBSTITUTE the specific instance of your delimiter —say the 5th one— with a unique delimiter (I use the "~" but you can use anything), and then we can used FIND to get the placement of the 5th delimiter.

Leibel S

@EgliAlv

For the column pulling the first pc of information (Country) you would use the below:

=IFERROR(LEFT([TEXT_COLUMN]@row + " | ", FIND(" | ", [TEXT_COLUMN]@row + " | ") - 1),"")

For the other columns use the below, and increment the number bolded.

=MID([TEXT_COLUMN]@row + " | ", FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 1)) + 3, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 2)) - FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 1)) - 3)

=MID([TEXT_COLUMN]@row + " | ", FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 2)) + 3, FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 3)) - FIND("~", SUBSTITUTE([TEXT_COLUMN]@row + " | ", " | ", "~", 2)) - 3)

EgliAlv

Hello Leibel,

Wow! wonderful, this just worked, thank you :) I tried many ways with MID and FIND functions. I see you used “~” what is it for? I got curious about that symbol.

Leibel S

@EgliAlv

The FIND function is limited as it only finds the first instance. In the SUBSTITUTE function you can specify which instance you want to SUBSTITUTE. So first we SUBSTITUTE the specific instance of your delimiter —say the 5th one— with a unique delimiter (I use the "~" but you can use anything), and then we can used FIND to get the placement of the 5th delimiter.

jkoester23

@EgliAlv

The formula you posted works for me only if the delimiter has a space after the first information set. The cell I am referencing is set up in the format like the outlook contact field where you have (last name, first name; last name) with the delimiter being ";". When I put a space after that first name the formula works great, but I don't have the time to adjust all the fields in this column to add the space. Is there a way to account for no space being in the referenced cell before the delimiter?

jkoester23

Believe I just answered my own question. " ; " vs ";"

Jim B

@Leibel Shuchat Thank you so much for this.