Can I extract the only second word?

[Narnimar]

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.

 

[Jacob Skaria]

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

 

[Gary Keramidas]

and if a code solution is preferable:

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

 

[Narnimar]

Hi,
It returns me like too few arguments and formula curser points at CHAR32.
What may be the problem?

 

[Jacob Skaria]

Try the below instead

=TRIM(MID(SUBSTITUTE(" " & $A$1&
REPT(" ",6)," ",REPT(" ",255)),2*255,255))

 

[Jacob Skaria]

Gary

Split() by default takes space as delimiter and hence

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

is enough

 

[Gary Keramidas]

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)