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.

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:

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.

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.

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.

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.

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.

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.


.

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.