Extract specific word from a text string
Hi Folks,
What I am looking for Extract specific word from a text string, I want to extra the text from Column A between ".MWS." and next "." (See the below examples).
Column A
T12-12.2.1.27.MWS.I161.A101
T12-12.2.1.27.MWS.LMI161.A102
T12-12.2.3.1.27.MWS.LMI161A.A103
T12-12.2.3.1.2.27.MWS.MI161B.A104
Column B (Returns)
I161
LMI161
LMI161A
MI161B
Thanks in advance for your expertise!
Joshi
Last edited by krjoshi; 01-17-2012 at 12:13 PM.
krjoshi,
Give this formula a try:
=TRIM(MID(SUBSTITUTE(A1,".",REPT(" ",99)),SEARCH("mws",SUBSTITUTE(A1,".",REPT(" ",99)))+99,99))
Hope that helps,
~tigeravatar
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Or
=MID(A1,FIND(".MWS.",A1)+5,FIND(".",A1,FIND(".MWS.",A1)+5)-FIND(".MWS.",A1)-5)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
=LEFT(REPLACE(A1,1,SEARCH("mws.",A1)+3,""),FIND(".",REPLACE(A1,1,SEARCH("mws.",A1)+3,""))-1))-1)
dang im trying to get this shorter!
=TRIM(LEFT(SUBSTITUTE(REPLACE(A1,1,SEARCH("mws.",A1)+3,""),".",REPT(" ",20)),20))
Last edited by martindwilson; 01-17-2012 at 12:44 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi Tigeravatar & Special-K,
Thank you so much Guys, its working great. One more query, how I can find the position of last (repeated) Dot "." from a text string?
Thanks,
joshi
Last edited by krjoshi; 01-17-2012 at 12:45 PM.
one way
=FIND("^",SUBSTITUTE(A1,".","^",LEN(SUBSTITUTE(A1,".","^^"))-LEN(A1)))
Nice one, martin. Beat me to it with a better formula to boot
Thank you so much Martindwilson,
I don’t know much about Formula, Can you please explain your formula? Especially exponentiation operator ("^^") stands for what purpose.
Thanks in advance,
Joshi
Last edited by krjoshi; 01-17-2012 at 01:06 PM.
^^ is just some text you are unlikely to have in your string if you did have ^ or^^ then use something different like|or @
take
mmm.fff.fff.eee.rrr.tttttt.yyyyyyy
LEN(SUBSTITUTE(A1,".","@@"))-LEN(A1) gives the difference in string length which = number of . in original string
so
len("mmm@@fff@@fff@@eee@@rrr@@tttttt@@yyyyyyy")-len("mmm.fff.fff.eee.rrr.tttttt.yyyyyyy")
=6 so there were 6 . in a1
then substitute the 6th . in a1 SUBSTITUTE(A1,".","@",6)
="mmm.fff.fff.eee.rrr.tttttt@yyyyyyy"
finally find the position of @ so
find("@","mmm.fff.fff.eee.rrr.tttttt@yyyyyyy") =27
Last edited by martindwilson; 01-17-2012 at 01:24 PM.
Thanks Guys, Great formula.
I need one more help, I have some resources data in B Column, I want to extract the "Crane" resources only from "B" Column. Please note that the all resources separated with (",") comma.
I have attached the work sheet. Can you please help me to resolve this issue?
Thanks in advance,
Joshi
krjoshi,
Attached is a modified version of the workbook you posted.
In cell C2 is this formula:
Please Login or Register to view this content.
In cell D2 is this formula:
Please Login or Register to view this content.
It looks like there are only 4 rows that contain the word "crane" twice. I confirmed using this formula:
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks