Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 19th 10, 06:36 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 132
Default Can I extract the only second word?

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.


  #2   Report Post  
Old March 19th 10, 06:42 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Can I extract the only second word?

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.

  #3   Report Post  
Old March 19th 10, 06:57 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 226
Default Can I extract the only second word?

and if a code solution is preferable:

split(range("A1")," ")(1) ' BASSAM
split(range("A1")," ")(2) ' INTERNATIONAL



--


Gary Keramidas
Excel 2003


"Narnimar" wrote in message
...
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.


  #4   Report Post  
Old March 19th 10, 07:17 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2006
Posts: 132
Default Can I extract the only second word?

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.

  #5   Report Post  
Old March 19th 10, 07:19 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Can I extract the only second word?

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.



  #6   Report Post  
Old March 19th 10, 07:35 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default Can I extract the only second word?

Gary

Split() by default takes space as delimiter and hence

Split(range("A1"))(1)

is enough

--
Jacob


"Gary Keramidas" wrote:

and if a code solution is preferable:

split(range("A1")," ")(1) ' BASSAM
split(range("A1")," ")(2) ' INTERNATIONAL



--


Gary Keramidas
Excel 2003


"Narnimar" wrote in message
...
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.


.

  #7   Report Post  
Old March 19th 10, 07:37 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2010
Posts: 226
Default Can I extract the only second word?

just for the heck of it, here's a solution just using search.

=MID(A1,SEARCH(" ",A1,1)+1,SEARCH(" ",A1,SEARCH(" ",A1,1)+1)-SEARCH("
",A1,1)-1)

--


Gary Keramidas
Excel 2003


"Narnimar" wrote in message
...
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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
extract word before / Wanna Learn Excel Discussion (Misc queries) 3 April 9th 09 04:08 PM
How to extract right-most word? [email protected] Excel Worksheet Functions 17 July 5th 07 06:43 PM
extract word function Dinesh Excel Worksheet Functions 14 November 23rd 06 06:49 AM
Extract data (not in table) from Word to Excel hellokitty77 Excel Discussion (Misc queries) 1 January 14th 06 02:51 PM
Extract the first word from a cell? Adam Cole Excel Discussion (Misc queries) 6 December 22nd 04 08:01 PM


All times are GMT +1. The time now is 06:41 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017