Extract second word from text tring

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
I've seen references to this on-line but nothing seems to work.
In column F from F2 i have various text strings IE: "CPI Beaconsfield", "Diverscite Pleinbois", "Hodge Clemco" etc etc... I want to extract the second word so in E2 onwards it reads Beaconsfield, Pleinbois, Clemco etc.......
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

NeonRedSharpie

Well-known Member
Joined
Jul 14, 2014
Messages
1,678
Code:
=RIGHT(F2,LEN(F2)-FIND(" ",F2))

This assumes two words with a space. You could also just use text to columns with a space as delimiter.
 

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
Sorry, maybe i should have been clearer, i want to extract ONLY the second word. This formula extracts the second word onwards.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
i want to extract ONLY the second word. This formula extracts the second word onwards.
Give this formula a try...

=MID(A1,FIND(" ",A1&" "),FIND(" ",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" "))

or, alternately, this one...

=TRIM(MID(SUBSTITUTE(A5," ",REPT(" ",99)),100,99))
 
Last edited:

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
Rick, the second one worked PERFECT, the first one extracted only the second word but had a space at the beginning. Many thanks.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,313
Office Version
  1. 2010
Platform
  1. Windows
Rick, the second one worked PERFECT, the first one extracted only the second word but had a space at the beginning. Many thanks.
If you care, here is the fix for that first one..

=MID(A1,FIND(" ",A1&" ")+1,FIND(" ",A1&" ",FIND(" ",A1&" ")+1)-FIND(" ",A1&" ")-1)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,354
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top