Stirng within string

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Sep 22, 2010

• #2

What do you mean by extract? Remove?

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

 

Upvote 0

C

chandrashekar

Well-known Member

Joined

Jul 15, 2005

Messages

524

Office Version

1. 365

Platform

1. Windows

• Sep 22, 2010

• #3

Hi,

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


Regards,

Chandra Shekar

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Sep 22, 2010

• #4

chandrashekar said:

Hi,

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


Regards,

Chandra Shekar

Click to expand...

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

 

Upvote 0

F

FareedKhan

New Member

Joined

Sep 3, 2010

Messages

32

• Sep 22, 2010

• #5

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)))

 

Upvote 0

P

p45cal

Well-known Member

Joined

Nov 10, 2009

Messages

4,763

• Sep 22, 2010

• #6

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?

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Sep 22, 2010

• #7

To remove the last word:

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

 

Upvote 0