Solved

# Extract Text After A Specified Word

Posted on 2011-10-25
173 Views
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
Question by:bryanscott53

LVL 43

Expert Comment

=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

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

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

LVL 50

Expert Comment

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

ssaqibh apologies but i was getting a #Value error

StephenJR yours worked perfect thanks saved me days!
0

LVL 26

Expert Comment

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

Barry, thumbs up.
0

LVL 50

Expert Comment

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

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

### Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.