CODEWITHSUNDEEP
excelexcel-formula
Goran B
Goran B

Reputation: 53

extract text from between two words

I'm trying to extract text between two specific words in Excel. First word is unique, but problem is that second word (it's actually a character) is not unique and can be even before "first word" in text, and that's where I have problem.

So, I need any text starting with first word "dimenzija" and anything up to first time we get to character ";", and I menage to set formula for that, but problem is if character ";" shows up in text before text "dimenzija"

Text example where my formula doesn't work:

Some text ; and text dimenzija: 10x10; some other text.

So here, I should get this: "dimenzija: 10x10" but I get Error #VALUE! code instead.

Code I'm using:

=MID(I2;SEARCH("dimenzija";I2);SEARCH(";";I2)-SEARCH("dimenzija";I2))

*So text is in I2 cell

Also, I have one other problem, if that block of text is at the end of the line, then it has no ";" at the end, but I'd still like to extract it, is it possible to do it without adding ";" on the end of the text? Example:

Text; some text; dimenzija: 10x10

so in this example, I would like for my code to extract text "dimenzija: 10x10" as well if possible.

Upvotes: 0

Views: 5782

Answers (2)

JvdV
JvdV

Reputation: 75990

Looks like you can use:

=MID(A1,FIND("dimenzija",A1),FIND(";",A1&";",FIND("dimenzija",A1))-FIND("dimenzija",A1))

enter image description here

Upvotes: 1

Harun24hr
Harun24hr

Reputation: 37135

Try below formula-

=LEFT(MID(A1,SEARCH("dimenzija",A1),LEN(A1)),SEARCH(";",MID(A1,SEARCH("dimenzija",A1),LEN(A1))))

enter image description here

Upvotes: 4

Related Questions