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.

Excel 2007 - Extracting Characters before & after a specific word from a Cell

Discussion in 'Business Applications' started by antz2011, Jul 14, 2011.

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

    antz2011 Thread Starter

    Joined:
    Jul 14, 2011
    Messages:
    2
    Hello,
    i do call data analysis in excel 2007 involving more than 1 lac call record.

    i need to exctract certain text (eg.Oracle) before a word (eg. install,repair,reload,reinstall) and even after these word if any.

    maybe 10 characters before and after the word "PLEASE INSTALL ORACLE"

    as in call records we have fields as Problem/Issue raised and Resolved.

    Please help..!!!
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,553
    First Name:
    Hans
    Hi, welcome to the fourm.

    I suggest you write a macro.

    In this macro you search for the position you word starts:

    You use the Instr() function

    Dim pos as integer
    pos = instr(1,<your full text string>,<you word>)

    "This is your text string and you need to know where the word is"

    so

    pos = instr("This is your text string and you need to know where the word is","text")

    pos will return the value 14, the beginning position of the word "text"


    try this in Excel as macro

    Code:
    Sub test()
    MsgBox InStr(1, "This is your text string and you need to know where the word is", "text")
    End Sub
    
    After you have the pos value then you can work with the different string functions to extract or create your desired text
     
  3. antz2011

    antz2011 Thread Starter

    Joined:
    Jul 14, 2011
    Messages:
    2
    Hi Hans,

    thanks for the reply, i tried the macro it throws up a zero value, the output has to be copied in the next cell.

    thanks:)
     
  4. WendyM

    WendyM Retired Trusted Advisor

    Joined:
    Jun 27, 2003
    Messages:
    4,042
    Hi antz,
    If you know the word you're looking for (for example, install), you can use a formula to get the words before and after it. However, in your example, how would you distinguish between returning the word "Please" and the word "Oracle"? It might be a better choice to insert a blank column and then search and return words like "Oracle", "Excel" and whatever else if they exist. Then once that's done, you can sort that column to see where you still have blanks and enter those in manually. Going forward, it would be easier for people to enter in this column as they create the ticket, if possible.
     
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/1007300

  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