Link to home
Start Free TrialLog in
Avatar of Bryan Scott
Bryan ScottFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Extract Text After A Specified Word

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?
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
=MID(A1,FIND("created by",LOWER(A1))+11,FIND(" ",A1,FIND("created by",A1)+11)-FIND("created by",LOWER(A1))-11)
ASKER CERTIFIED SOLUTION
Avatar of StephenJR
StephenJR
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of redmondb
redmondb
Flag of Afghanistan image
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.
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
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

Avatar of Bryan Scott
Bryan Scott
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

ssaqibh apologies but i was getting a #Value error

StephenJR yours worked perfect thanks saved me days!
Avatar of redmondb
redmondb
Flag of Afghanistan image
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.
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image
Barry, thumbs up.
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image
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
Avatar of Muhammad Khan
Muhammad Khan
Flag of Canada image
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")))