+ Reply to Thread
Results 1 to 6 of 6

Find specific text within text and result dependent text in specified column

  1. #1
    Registered User
    Join Date
    03-13-2014
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    59

    Find specific text within text and result dependent text in specified column

    Hi all,

    I am dealing with the following issue,

    I get data for email categorization in format like :

    'text1;text2;text3'
    (it is possible to have missing values within the text)

    I attach a sample in order to expain.

    The text in column A provides me the information regarding the sender of the email (Column FROM), the action (Column ACTION/INFO) and the subject (Column SUBJECT). So from the column A I want the text to split in the 3 columns B - C - D.

    The problem is that I cannot use text to columns since the texts within each cell are not always in the right order.

    So I made 3 tables (columns G:H, I:J & K:L) in order to show you the texts I may find within the text 'text1;text2;text3' of column A and the result I want in columns B - C - D.

    For example:

    If I have the following text in column A:

    AGREEMENTS; ACTION; DEALER

    then I want the result to be :

    in column B 'DEALER',
    in column C 'ACTION'
    in column D 'AGREEMENTS'


    Any help will be highly appreciated!
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Find specific text within text and result dependent text in specified column

    With the tables defined you could use a SEARCH approach, e.g.:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    note: pending your client settings you may need to modify delimiters (and function names) to your reflect your locale

    EDIT: if you should need to generate different outputs for strings that are not mutually exclusive, e.g. REQUIREMENT & REQUIREMENTS, you would need to adapt the above -- however your samples would imply this is not an issue.
    Last edited by XLent; 01-08-2019 at 08:08 AM.

  3. #3
    Registered User
    Join Date
    03-13-2014
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Find specific text within text and result dependent text in specified column

    Thank you for the answer Xlent!


    What is the 9.99E+307 in the formula?

    Should I use this formula as an array?

  4. #4
    Registered User
    Join Date
    03-13-2014
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Find specific text within text and result dependent text in specified column

    OMG it Works!

    But what is this 9.99E+307?

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Find specific text within text and result dependent text in specified column

    No, this is not an Array formula - so can be entered with usual Enter command.

    9.99E+307 is a very big number....

    LOOKUP will find the last value in the lookup vector that is <= criteria, and will only consider values in the lookup vector that are of the same data type as the criteria {and, importantly, ignores errors in this context}

    In this example the SEARCH will return an array of values, one per search value, and these values will either be a number (if search string is found), or #VALUE! (not found)

    The LOOKUP finds the last number in the lookup vector (given our criteria is a number, and will be bigger than any number in the lookup vector) and subsequently return the associated value from the (optional) result vector -- in this case the the return columns in your three tables.

    In case you are having locale issues translating the suggested formula I have attached the same which will auto translate when opened on your client.

    I hope that helps

    edit: I should have added that LOOKUP assumes that the lookup vector is sorted -- however when used in this context it's effectively irrelevant
    Attached Files Attached Files
    Last edited by XLent; 01-08-2019 at 08:35 AM.

  6. #6
    Registered User
    Join Date
    03-13-2014
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2007
    Posts
    59

    Re: Find specific text within text and result dependent text in specified column

    Thank you XLent for your time and help!

    Couldn' t find it on my own...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] IF function - dependent on text, have opposite text in other column
    By rayted in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-28-2018, 07:23 AM
  2. [SOLVED] VBA Macro to find text string in one column and replace specific text in another column if
    By bmahfood in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-21-2017, 11:15 AM
  3. Find Specific Text as result of formula
    By greenjl7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2016, 10:21 AM
  4. [SOLVED] Search through columns and find specific text and replace that text with header column
    By adamzee in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-09-2014, 11:59 PM
  5. InStr function doesn't find specific text, but finds any text in column and runs code
    By mikey3580 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-27-2014, 03:50 AM
  6. Replies: 4
    Last Post: 12-20-2012, 04:33 AM
  7. [SOLVED] Find Specific Text in Text String and Return Value in Adjacent Column
    By watchouse in forum Excel General
    Replies: 2
    Last Post: 07-11-2012, 03:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1