Stirng within string

[chandrashekar]

Hi,

I have data in A1="This can be done" and in A2="he has done" here i would like to extract "done" using excel formula.

Thanking you
Chandra Shekar B

 

[Andrew Poulsom]

What do you mean by extract? Remove?

=TRIM(SUBSTITUTE("A1","done",""))

 

[chandrashekar]

Hi,

Thank you for ur reply here "done" may vary as done1, done101 etc.


Regards,

Chandra Shekar

 

[Andrew Poulsom]

Hi,

Thank you for ur reply here "done" may vary as done1, done101 etc.


Regards,

Chandra Shekar

So change SUBSTITUTE's second argument to suit. It can be a cell reference.

 

[FareedKhan]

If the last word in the cell starts with done(ex: done101,done89), the below formula would work

=TRIM(LEFT(A1,(SEARCH("done*",A1)-1)))

 

[p45cal]

Chandrasheka, if you must cross post, please have the courtesy to tell others everywhere you have done so. So far:
http://vbaexpress.com/forum/showthread.php?t=34151
…anywhere else?

 

[Andrew Poulsom]

To remove the last word:

=LEFT(A1,FIND("|",SUBSTITUTE(A1," ","|",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)