Drop part of a cell value after a specific character

Question

I have one cell that contains a long string of several values separated by "|".

EXAMPLE in Cell B5

title | time | location | cost

I need a formula that will show the value of B5, but only the characters before the first "|".

Is it possible to trim everything including and following a specific character?

Answer 1

=REGEXEXTRACT(B5, "[^\s|]+")

• \s a space
• \s| a space followed by | character
• ^ the first
• ^\s| the first match of a space followed by | character
• [] a group
• [^\s|] a group found before the first match of a space followed by | character
• + more than one character if possible
• [^\s|]+ extract a group of characters before the first occurrence of a space followed by | character

Answer 2

Split formula may fit your needs:

=split(B5, "|")

next step: use index to return any of splitted words:

=TRIM(INDEX(split(B5, "|"),1))

change 1 to your number, this is the index of a word to return

Answer 3

You can just wrap it with trim

=Trim (split (B5,"|"))

Answer 4

I need a formula that will show the value of B5, but only the characters before the first "|".

=TRIM(LEFT(B5,SEARCH("|",B5)-1)

The result is title

Answer 5

You can try =LEFT(B5,SEARCH("|",B5)-1).