remove text

[sna]

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

[norkaz]

.

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

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

Norkaz

[sna]

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

[norkaz]

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

[sna]

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

[norkaz]

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

[sna]

Thank you