Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: Find specific text within a string in a Cell

Status
Not open for further replies.

bobomutt

Technical User
Nov 23, 2005
12
US
I need to write an equation that I can use to search a cell for a string of text, and if it finds it, return it.

Example: If cell A1 contains the text string "J6R Dllser" I want the value "J6R" to be displayed in cell B1.

Thanks!
 
Have a look at the FIND and SEARCH functions in Excel's help file.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
You need to state you case a little bit better, because the solution for this one
=MID(A1,FIND("J6R Dllser",A1),3)

may not work for other cells depending on what you need to extract.

Member- AAAA Association Against Acronym Abusers
 
Note to self: Type faster than John

Member- AAAA Association Against Acronym Abusers
 
Or you could just not offer a function - that shaves a bit of time off [wink]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
Right... it may be surprising to you, but I already searched excel's help before posting.

And as far as what I want to retrieve - everytime "J6R" appears within a string in a cell, I want to return "J6R" in another cell. So the first cell that I am searching may have "asdfalsdf J6R asdfasdf". The function in the second cell should search the string for J6R, and if it finds it, return "J6R". If not, return nothing. I am not trying to find the position of the text.

Best,

Doug
 
No need to get defensive.

It will help us help you faster if you tell us what you have already tried and state your desired outcome more clearly in the first place.

Now that I know what you're after, I can offer this:
[tab]=if(isnumber(find("J6R",A1)),"J6R","")

Or you can replace the text with another cell reference:
[tab]=if(isnumber(find(B1,A1)),B1,"")


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.
 
In that case it's simple

=IF(ISERROR(MID(A1,FIND("J6R",A1),3)),"",MID(A1,FIND("J6R",A1),3))


Member- AAAA Association Against Acronym Abusers
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top