LinkBack URL
About LinkBacks
I need to extract the second word in a column that contains text of different length? I have attached a sample file.
Thanks in advance
I need to extract the second word in a column that contains text of different length? I have attached a sample file.
Thanks in advance
Hi
Try this formula:
=TRIM(MID(A1,FIND(":",A1)+1,FIND(" -",A1)-4))
Click *, if my suggestion helps you. Have a good day!!
Originally Posted by jraj1106
The 2nd part of that formula assumes that the ":" will always be the 3rd character. If you're going to make that assumption then why use the first FIND?
i am going with the assumption that you may or may not have ":" and "-", but certainly will have " " on either side of the second word in the string. if ":" and "-" are constants, then you are better off using @jraj's formula.
=MID(A1,FIND("|",SUBSTITUTE(A1," ","|",1)),FIND("|",SUBSTITUTE(A1," ","|",2))-FIND("|",SUBSTITUTE(A1," ","|",1)))
EDIT:
use the following monstrosity instead for best results.
=MID(TRIM(A1),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",1)),FIND("|",SUBSTITUTE(TRIM(A1)," ","|",2))-FIND("|",SUBSTITUTE(TRIM(A1)," ","|",1)))
TRIM function reduces to 1 any number of contiguous white spaces greater than (or equal to) 1 within the string (not extremities - those are always zeroed out). in the earlier formula, if it encountered 2 contiguous white spaces, the result would be erroneous. due to TRIM, all such instances would be reduced to just a single instance. however, not all "white-space" induced errors, i will admit, can be countered with this strategy (as zbor has pointed out below).
Last edited by icestationzbra; 05-09-2012 at 07:03 AM. Reason: enhancement due to TRIM
- i.s.z -
CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
All good ideas are courtesy resources from this forum as well as others around the web.
- e.o.m -
Thanks to all for the solution.
If you're always looking for the terms "Client" or "Customer" you could use:
=INDEX({"Client","Customer"},MATCH(TRUE,INDEX(ISNUMBER(FIND({"Client","Customer"},A1)),0),0))
But I'd probably go with icestationzbra's formula.
Or, to be more clear, those results work for different cases to extract word:
jraj-> ABC DEF: word will return word
icestationzbra -> ABC: word will return word
icestationzbra -> ABC DEF: word will return DEF:
jraj-> ABC DEF; word will return error
icestationzbra -> ABC; word will return word
icestationzbra -> ABC DEF; word will return DEF;
Aland2929: what possible inputs you expect of above?
see post #4 for updated formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks