|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 15th, 2009, 11:35 AM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help me!
Please help me. I would like to separate one word from a cell.
Example : Cell A2 = The Quick Brown Fox Question-1: How to separate "Fox" from Cell A2? Question-2: How to separate "Brown" from Cell A2? 
|
|
#2
June 15th, 2009, 01:00 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me!
Hi Razzak,
To search for the last word you can use the formula below. =RIGHT(A2;LEN(A2)-FIND(" ";A2)) If you have 3 words then you can use this formula. =LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)- FIND(" ",C45,1)),1)) (replace C45 with the cellreference you are using. Haven't got the formula for the 3 word (or prior to the last word).  Good luck, Tom On 15 jun, 12:35, Razzak wrote: Please help me. I would like to separate one word from a cell. Example : Cell A2 = The Quick Brown Fox Question-1: How to separate "Fox" from Cell A2? Question-2: How to separate "Brown" from Cell A2?
|
|
#3
June 15th, 2009, 01:08 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me!
Hi,
to get the last word use =RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) and to get the second to last no matter how many words there are use =MID(MID(MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),1,256),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),1,256),FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1)),256))-2) Mike "Razzak" wrote: Please help me. I would like to separate one word from a cell. Example : Cell A2 = The Quick Brown Fox Question-1: How to separate "Fox" from Cell A2? Question-2: How to separate "Brown" from Cell A2?
|
|
#4
June 15th, 2009, 01:25 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Help me!
Use SUBSTITUTE function; (case sensitve)
For replacing string use =TRIM(SUBSTITUTE(A2,"Fox",) For replacing the word "Fox". =TRIM(SUBSTITUTE(" " & A2 & " ", " " & "Fox" & " "," ")) 'The Quick Brown Fox Foxer' will be converted to 'The Quick Brown Foxer' If this post helps click Yes --------------- Jacob Skaria "Razzak" wrote: Please help me. I would like to separate one word from a cell. Example : Cell A2 = The Quick Brown Fox Question-1: How to separate "Fox" from Cell A2? Question-2: How to separate "Brown" from Cell A2?
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
