Extract string based on specific string

Hello everybody
I have this value for example
My name is Yasser Khalil
I want to extract the name and the address based on the string (My name is) to extract (Yasser Khalil) the name consists of two parts (first name and second name) ..
I also want to extract the address based on the string(and the address is) to extract the rest of the string(20 Salem st.,Cairo)
Thanks advanced

I appreciate if it can be done with a UDF function

Moderator's note: Personal info removed, never a good idea to show that kind of info on public forum

A2: given text string
B2: =TRIM(LEFT(MID(SUBSTITUTE($A2, " ", REPT(" ", 30)), FIND("is", SUBSTITUTE($A2, " ", REPT(" ", 30)))+30, 100), 30))
C2: =TRIM(LEFT(MID(SUBSTITUTE($A2, " ", REPT(" ", 30)), FIND("is", SUBSTITUTE($A2, " ", REPT(" ", 30)))+60, 100), 30))
D2: =MID($A2, FIND($C2, $A2)+LEN($C2)+19,1000)

Hello YasserKhalil,

I think this thread of yours may be related to this one; http://www.excelforum.com/excel-prog...tion-list.html

Please forgive me if I am wrong.

Regards.

See if this helps.

Please Login or Register  to view this content.

Then use it like this.....

To get the Name

Please Login or Register  to view this content.

To get the Address

Please Login or Register  to view this content.

Mr. sktneer
really wonderful function from wonderful professional expert..
I want to learn how to do such magic lines

Is there a way to add an argument to specify which occurence of specific word and extract what follows:
for example: I have two "is" I want to specify the first or second and based on its occurence extract the string which follows

You may try something like this....

Please Login or Register  to view this content.

So if your string is

Please Login or Register  to view this content.

Then to get the Name, try

Please Login or Register  to view this content.

And to get the Address, try

Please Login or Register  to view this content.

And if you string is like this...

Please Login or Register  to view this content.

Then to get the Name, try

Please Login or Register  to view this content.

And to get the Address, try

Please Login or Register  to view this content.

Is this what you are trying to achieve?

I'm not seeing what was wrong with the formulas from post #2 based on your original question. They didn't work for you?