Snasui Excel Forum

Hi Dear,

I need your helpful solution to extract some text after key words.
For example I have text string like
A good day B nice holiday in A2
In B1:A ,C1:B
In B2 I want to get good day base on B1
C2 I want to get nice holiday base on C1
....
Thank you in advance

.

B2
=MID(LEFT(A2,FIND(C$1,A2)-2),3,99)

C2
=MID(A2,FIND(C$1,A2)+2,99)

Norkaz

Thank but I need a dynamic formula just drag to the right the solution will come up.

Hi Guy

Do you mean the right solution is the one formula come up in a cell and then drag or copy to the related cells?


-- If yes, try this.


B2

=IF(LEFT($A2,FIND(" ",$A2)-1)=B$1,MID($A2,3,FIND($C$1,$A2)-4),SUBSTITUTE($A2,LEFT($A2,FIND($C$1,$A2))&" ",""))

Copy to the right-hand side and down.


-- if no, please give us more some samples to more clear about what you're looking for.

Norkaz

If it is something like
St Sadegol Address rati ka Home 123 Capital Reunion in A2
B2: Sadegol base on B1:St
C2:rati ka base on C1; Address
D2:123 base on D1:Home
E2:Reunion base on E1: Capital

B2

=TRIM(SUBSTITUTE(SUBSTITUTE(MID($A2,SEARCH(B$1,$A2),99),B$1,""),MID($A2,SEARCH(C$1,$A2),99),""))

Copy to right-hand side


Norkaz

Thank you