[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Extract Text After A Specified Word

Posted on 2011-10-25
9
Medium Priority
?
213 Views
Last Modified: 2012-05-12
I need to be able to extract the first word after "created by" in an excel cell, some of the cells do not have a "Created by" in them so i just want to return a null value. there is a large string within each cell. can you help me with this?
0
Comment
Question by:bryanscott53
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37023652
=MID(A1,FIND("created by",LOWER(A1))+11,FIND(" ",A1,FIND("created by",A1)+11)-FIND("created by",LOWER(A1))-11)
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 37023680
There has to be a shorter way:

=IF(ISNUMBER(FIND("created by",H22)),MID(H22,FIND("created by",H22)+11,IF(ISNUMBER(FIND(" ",H22,FIND("created by",H22)+11)),FIND(" ",H22,FIND("created by",H22)+11),LEN(H22))-FIND("created by",H22)-10),"")
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37023689
Following handles missing "created by " and also single word after it (requires Excel 2007/2010 - let me know if you use an earlier version)...

=IFERROR(MID(A1,FIND("created by ",A1,1)+11,IFERROR(FIND(" ",A1,FIND("created by ",A1,1)+11)-FIND("created by ",A1,1)-11,9999)),"")

Regards,
Brian.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:barry houdini
ID: 37023692
Hello Saqib,

If you use SEARCH then that is not case-sensitive (so you don't need LOWER), i.e.

=TRIM(REPLACE(A1,1,SEARCH("Created by",A1&"Created by")+9,""))

That returns a blank if "Created by" isn't in A1. Use FIND instead of SEARCH if you do want case-sensitivity

regards, barry

0
 

Author Closing Comment

by:bryanscott53
ID: 37023714
ssaqibh apologies but i was getting a #Value error

StephenJR yours worked perfect thanks saved me days!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 37023722
StephenJR,

Nice (pre-2007 "compliant"), but needs a Trim()...

=trim(IF(ISNUMBER(FIND("created by",H22)),MID(H22,FIND("created by",H22)+11,IF(ISNUMBER(FIND(" ",H22,FIND("created by",H22)+11)),FIND(" ",H22,FIND("created by",H22)+11),LEN(H22))-FIND("created by",H22)-10),""))

Regards,
Brian.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37023739
Barry, thumbs up.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 37023741
Oh, did I miss "first word"....must brush up on my reading skills.....

FWIW you could try this

=TRIM(REPLACE(LEFT(A1,FIND(" ",A1&"Created by x ",SEARCH("Created by",A1&"Created by")+11)),1,SEARCH("Created by",A1&"Created by")+9,""))

regards, barry
0
 
LVL 17

Expert Comment

by:Muhammad Khan
ID: 37023763
or this

=MID(A2,FIND("Created By",A2)+LEN("Created By")+1,FIND(" ",A2,FIND("Created By",A2,1)+LEN("Created By "))-(FIND("Created By",A2,1)+LEN("Created By")))
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question