how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have to
retrieve "how can i retrieve text" to B1

The word "from" or ANY last word in the sentence?

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"JAF-In" wrote in message
...
how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have
to
retrieve "how can i retrieve text" to B1

Hi,

Try this

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"")

Mike

"JAF-In" wrote:

how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have to
retrieve "how can i retrieve text" to B1

Try this

=SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),"")

You should encase that in a TRIM function in order to get rid of the space
that is in front of that last word...

=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),""))

--
Rick (MVP - Excel)


how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have
to
retrieve "how can i retrieve text" to B1

You right, because I was removing spaces with the inner part of the formula

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

I never realised I would get the last space back when using the extracted
string in the outer substitute part, nice spot.

Mike

"Rick Rothstein" wrote:

Try this

=SUBSTITUTE(A1,),"")

You should encase that in a TRIM function in order to get rid of the space
that is in front of that last word...

=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),""))

--
Rick (MVP - Excel)


how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have
to
retrieve "how can i retrieve text" to B1

In article ,
JAF-In wrote:

how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have to
retrieve "how can i retrieve text" to B1


Try...

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

--
Domenic
http://www.xl-central.com

Try

=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)),))

If this post helps click Yes
---------------
Jacob Skaria


"JAF-In" wrote:

how can i retrieve text from one column to another except one last word
for eg:The text "how can i retrieve text from" is in column A1 and i have to
retrieve "how can i retrieve text" to B1