Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Can I extract the only second word?

Yes, you can extract the second word from a cell in Excel. Here are the steps to do it:
  1. Select the cell where you want to extract the second word.
  2. Type the following formula in the formula bar:
    Formula:
    =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+1)-FIND(" ",A1)-1
  3. Press Enter.

This formula uses the MID function to extract the text from the cell, starting from the position of the first space character plus one, and ending at the position of the second space character minus one. The FIND function is used to locate the position of the space characters.

If you want to extract the third word instead of the second word, you can modify the formula as follows:

Formula:
=MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,FIND(" ",A1,FIND(" ",A1,FIND(" ",A1)+1)+1)-FIND(" ",A1,FIND(" ",A1)+1)-1
This formula uses the FIND function twice to locate the position of the third space character, and then extracts the text between the second and third space characters.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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.



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
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.

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
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.


.

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
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 05:49 AM
Extract data (not in table) from Word to Excel hellokitty77 Excel Discussion (Misc queries) 1 January 14th 06 01:51 PM
Extract the first word from a cell? Adam Cole Excel Discussion (Misc queries) 6 December 22nd 04 07:01 PM


All times are GMT +1. The time now is 02:26 AM.

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

About Us

"It's about Microsoft Excel"