# Solved: Excel 2007 - Formula for Extracting Specific Characters from Cell

Discussion in 'Business Applications' started by Stisfa, Feb 6, 2010.

Not open for further replies.

Joined:
Nov 13, 2009
Messages:
72
Trying to pull the word "set" from a cell that has more words and characters than just "set. I also need it to account for variables such as lower case/upper case (i.e. SET, SeT, SEt, seT). The formula needs to pull the word "set", however it looks, into the adjacent cell, this way I can use the Sort tool to organize all cells.

I tried the INDEX formula, but it only looks for the word "set" without distinguishing it from the other characters.

I don't think the LEFT/RIGHT/MID functions will work, as the character count is going to be a variable from each cell (my judgment on these formulas is really shallow due to my lack of time to completely understand them).

Take a look at the attached spreadsheet and you'll see where I'm going.

File size:
8.4 KB
Views:
354
2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,904
First Name:
Wayne
try
=search("set", A1,1)
that will return the number the SET starts at
so you could then use a IF statement to write set if a number in the cell
and MID to extract

i'll work onit
EDIT

=MID(B2,(SEARCH("set",B2,1)),3)
#VALUE!
will be in any cell that does not match

if you want to return a blank cell if set is not found
=IF(ISERROR(SEARCH("set",B2,1)),"",MID(B2,(SEARCH("set",B2,1)),3))

i only have excel 2003

#### Attached Files:

• ###### Example_SET.xls.xlsx
File size:
8.9 KB
Views:
337

Joined:
Nov 13, 2009
Messages:
72
The SEARCH formula was good enough for me but you blew my socks off with the other 2 formulas.

Thanks for the quick reply etaf!

By the by, how did you end up becoming so proficient with Excel? Microsoft MVP? Or a seasoned vet out of necessity?

4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,904
First Name:
Wayne
Self taught via Books, in the days before the Internet - started on a propriety package on a Digital Equipment PC in 1985ish - called 2020 and then onto Lotus 123 for local Management reporting and then needed to develop further with VB on Excel 95.
Very rusty now - but like to answer these and it keeps my hand in , although not always successful in answering posts

The Mid function is very good and I used it a lot in business
also Trim , left , right all useful for text manipulation

5. ### nesr

Joined:
Nov 5, 2008
Messages:
51
Peace be upon you all
please put this function in column C row 2, then pull down
=IF(ISERROR(FIND("set",LOWER(B2))),B2,MID(B2,FIND("set",LOWER(B2)),3))
this will fix the problem
also it can find the word Set anywhere, even in the middle of a word like "Reset"
and it returnd the word set in its original case, even "Set", "sET", or whatever

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
64,904
First Name:
Wayne
nesr - no function posted or attached

7. ### nesr

Joined:
Nov 5, 2008
Messages:
51
thank you for this notice, but I fixed it before receiving your kind post,
the problem was with the forum advanced editor!
any way, please refresh and you will see the formula.
I think the post should be marked Solved

Joined:
Nov 13, 2009
Messages:
72
Thanks for the reminder nesr.

Again, thanks for the help etaf.

As Seen On

### Welcome to Tech Support Guy!

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!