CODEWITHSUNDEEP
excelexcel-formulaextractdelimiter
Miaka3
Miaka3

Reputation: 405

Extract text one word to the right of delimited value within string

I've tried numerous formulas and cannot seem to figure it out using the RIGHT, LEFT and MID functions. Using MS Excel, I would like to extract only one word (two spaces) to the right of my delimiter value ^.

EXAMPLE: Cell A2

Johnny and I were planning on going to the movie to see ^Batman Returns, but it was to late.

Results: Cell B2

^Batman Returns,

Upvotes: 0

Views: 750

Answers (3)

Rodrigo Gomes
Rodrigo Gomes

Reputation: 358

Using your example, the following formula it works:

MID(A1;FIND("^";A1);FIND(" ";A1;FIND(" ";A1;FIND("^";A1))+1)-FIND("^";A1))

A1 is the cell where you have your text that will be checked to extract the piece you are interested.

The formula looks for the index of ^ character

FIND("^";A1)

then look for the index of the second space from that ^ character index

FIND(" ";A1;FIND(" ";A1;FIND("^";A1))+1)

use this second index to subtract the first index to get the piece of text length from ^ character until the second space after that

       second index            first index
FIND(" ";A1;FIND("^";A1))+1) - FIND("^";A1)

AND finally use the MID formula to retrieve the piece of text desirable.

Upvotes: 1

Excel Hero
Excel Hero

Reputation: 14764

Here you go:

=MID(A2,FIND("^",A2),FIND("¦",SUBSTITUTE(MID(A2,FIND("^",A2),99)," ","¦",2)))

Just change the '2' at the end to adjust your capture of spaces to the right of the delimiter.

Upvotes: 3

user4039065
user4039065

Reputation:

Expand the spaces with the SUBSTITUTE and REPT functions then chop out a piece with MID and finally clean it up with the TRIM function.

=TRIM(MID(SUBSTITUTE(A2, " ", REPT(" ", 99)), FIND("^", SUBSTITUTE(A2, " ", REPT(" ", 99))), 199))

      Substring from String at special character

Upvotes: 1

Related Questions