Parse text and return value between 2 delimiters

Question

Parse text and return value between 2 delimiters

Anonymous

Suppose, the sample text below is in Column A Cell A1,A2...so on, Could you please help me with a function that can parse a text and return text between 2 delimiters as shown in the examples below, the delimiters could be a ":" and/or "-".

Sample text 1: My Name is : Melinda Bill Gates : and I work at Microsoft.

Expected output - "Melinda Bill Gates"

Sample Text 2: My Name is : Melinda Bill Gates - and I work at Microsoft.

Expected output - "Melinda Bill Gates"

Thanks,

KVM

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Answer 1

Hi,

If your string is in A1, try:

=TRIM(MID(A1,FIND(":",SUBSTITUTE(A1,"-",":"))+1,FIND(":",SUBSTITUTE(A1,"-",":"),FIND(":",SUBSTITUTE(A1,"-",":"))+1)-FIND(":",SUBSTITUTE(A1,"-",":"))-1))

Hope that helps.

Cheers

Rich

PS, if you knew that the desired text would be sandwiched between ":" at each end, you can just use:

=TRIM(MID(A1,FIND(":",A1)+1,FIND(":",A1,FIND(":",A1)+1)-FIND(":",A1)-1))

but if the "bread of the sandwich" could be either ":" OR "-", then you need to use the longer formula with SUBSTITUTE, which effectively replaces any "-" with ":".

Share via

Parse text and return value between 2 delimiters

1 answer