+ Reply to Thread
Results 1 to 5 of 5

Return All Data Between 2 Text Values

  1. #1
    Registered User
    Join Date
    09-24-2012
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Question Return All Data Between 2 Text Values

    I am attempting to create a formula to join all rows of data in a column into a single cell in another column if between a specific text string. I have the piece to join the data points if criteria is met using the TEXTJOIN function.
    What I am lacking is how to search a column between 2 text strings. I hope my explanation was clear. Below is an example to return everything between the word bingo.

    Current Column Data Desired Data in Single Cell

    Bingo 1,2,3,4
    1
    2
    3
    4
    Bingo
    Last edited by Priceman; 10-24-2017 at 10:57 AM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,943

    Re: Return All Data Between 2 Text Values

    Try a formula like this, for data in column A:

    =TEXTJOIN(",",TRUE,OFFSET(A1,MATCH("Bingo",A:A,FALSE),0,MATCH("Bingo",OFFSET(A1,MATCH("Bingo",A:A,FALSE),0,COUNTA(A:A),1),FALSE)-1,1))
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    09-24-2012
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Return All Data Between 2 Text Values

    Thanks...so if I wanted to copy that formula down a column would it work. To further elaborate, I am trying to textjoin numerous times when a value is found in a cell in column A.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,943

    Re: Return All Data Between 2 Text Values

    It won't copy at all - it wasn't designed to copy. How many instances of 'Bingo' do you expect in your column? Would you want the textjoin between the first and second and the third and fourth, or between the first and second, second and third, and third and fourth?

  5. #5
    Registered User
    Join Date
    09-24-2012
    Location
    Tampa
    MS-Off Ver
    Excel 2010
    Posts
    10

    Smile Re: Return All Data Between 2 Text Values

    Actually with a little work I figured out that it will indeed copy down. Thanks for your help

+ 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] Return 3 values from text in excel
    By sammo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2015, 03:31 AM
  2. Replies: 3
    Last Post: 12-03-2014, 05:43 PM
  3. [SOLVED] Return Text value from a range of values in a certain row
    By mbajmca in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-31-2014, 10:27 AM
  4. return text when values don't match
    By JakeMann in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-30-2013, 12:44 PM
  5. Search for text and return values
    By Ninja2k in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-29-2013, 02:22 PM
  6. Replies: 2
    Last Post: 09-07-2013, 07:50 PM
  7. Search text string for range of text values - return match
    By crugg in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2010, 09:55 AM

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