Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retriving text from a column
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
retriving specific data????? | Excel Worksheet Functions | |||
Find a text from a column in a text string within another column? | New Users to Excel | |||
Retriving Values from a separate sheet of a different file | Excel Worksheet Functions | |||
Wrap text in column headers to fit text in column | Excel Discussion (Misc queries) | |||
Retriving a deleted version of Excel | Excel Worksheet Functions |