Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 132
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
 Excel Super Guru Posts: 1,867
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
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 8,520
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
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 226
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
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 132
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
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 8,520
Can I extract the only second word?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Wanna Learn Excel Discussion (Misc queries) 3 April 9th 09 04:08 PM [email protected] Excel Worksheet Functions 17 July 5th 07 06:43 PM Dinesh Excel Worksheet Functions 14 November 23rd 06 05:49 AM hellokitty77 Excel Discussion (Misc queries) 1 January 14th 06 01:51 PM 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.