Can I extract the only second word?
in [General]
|
Prev: Sort Worksheets
Next: encryption
From: Narnimar on 19 Mar 2010 01:36 How can I extract the second word in a cell into a cell? I don't want the first word or any other words to go with it into the new cell from company name. e. g. from AL BASSAM INTERNATIONAL FACTORIES extract BASSAM only. Also I would like to know to extract only third word in case I may need for future alternatively.
From: Jacob Skaria on 19 Mar 2010 01:42 Use the below formula....to extract the second word =TRIM(MID(SUBSTITUTE(" " & $A$1& REPT(" ",6)," ",REPT(CHAR32),255)),2*255,255)) For the 3rd word change 2*255 to 3*255 -- Jacob "Narnimar" wrote: > How can I extract the second word in a cell into a cell? I don't > want the first word or any other words to go with it into the new cell from > company name. > e. g. from AL BASSAM INTERNATIONAL FACTORIES extract BASSAM only. > Also I would like to know to extract only third word in case I may need for > future alternatively. >
From: Gary Keramidas on 19 Mar 2010 01:57 and if a code solution is preferable: split(range("A1")," ")(1) ' BASSAM split(range("A1")," ")(2) ' INTERNATIONAL -- Gary Keramidas Excel 2003 "Narnimar" <Narnimar(a)discussions.microsoft.com> wrote in message news:1FF9643F-3492-4744-BE86-2969BBFBD75F(a)microsoft.com... > How can I extract the second word in a cell into a cell? I don't > want the first word or any other words to go with it into the new cell > from > company name. > e. g. from AL BASSAM INTERNATIONAL FACTORIES extract BASSAM only. > Also I would like to know to extract only third word in case I may need > for > future alternatively. >
From: Narnimar on 19 Mar 2010 02:17 Hi, It returns me like too few arguments and formula curser points at CHAR32. What may be the problem? "Jacob Skaria" wrote: > Use the below formula....to extract the second word > > =TRIM(MID(SUBSTITUTE(" " & $A$1& REPT(" ",6)," > ",REPT(CHAR32),255)),2*255,255)) > > For the 3rd word change 2*255 to 3*255 > > -- > Jacob > > > "Narnimar" wrote: > > > How can I extract the second word in a cell into a cell? I don't > > want the first word or any other words to go with it into the new cell from > > company name. > > e. g. from AL BASSAM INTERNATIONAL FACTORIES extract BASSAM only. > > Also I would like to know to extract only third word in case I may need for > > future alternatively. > >
From: Jacob Skaria on 19 Mar 2010 02:19
Try the below instead =TRIM(MID(SUBSTITUTE(" " & $A$1& REPT(" ",6)," ",REPT(" ",255)),2*255,255)) -- Jacob "Narnimar" wrote: > Hi, > It returns me like too few arguments and formula curser points at CHAR32. > What may be the problem? > > "Jacob Skaria" wrote: > > > Use the below formula....to extract the second word > > > > =TRIM(MID(SUBSTITUTE(" " & $A$1& REPT(" ",6)," > > ",REPT(CHAR32),255)),2*255,255)) > > > > For the 3rd word change 2*255 to 3*255 > > > > -- > > Jacob > > > > > > "Narnimar" wrote: > > > > > How can I extract the second word in a cell into a cell? I don't > > > want the first word or any other words to go with it into the new cell from > > > company name. > > > e. g. from AL BASSAM INTERNATIONAL FACTORIES extract BASSAM only. > > > Also I would like to know to extract only third word in case I may need for > > > future alternatively. > > > |