1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

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

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

Thread Status:
Not open for further replies.
Advertisement
  1. Stisfa

    Stisfa Thread Starter

    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.

    Thanks in advance
     

    Attached Files:

  2. etaf

    etaf Moderator

    Joined:
    Oct 2, 2003
    Messages:
    64,904
    First Name:
    Wayne
    try
    =search("set", A1,1)
    that will return the number the SET starts at
    if not found returns a #value
    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 have added to your example spreadsheet and uploaded

    i only have excel 2003
     

    Attached Files:

  3. Stisfa

    Stisfa Thread Starter

    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. etaf

    etaf Moderator

    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

    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. etaf

    etaf Moderator

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

    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
     
  8. Stisfa

    Stisfa Thread Starter

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

    Again, thanks for the help etaf.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/900846

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice