+ Reply to Thread
Results 1 to 8 of 8

Extract Values/Text ONLY

  1. #1
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Extract Values/Text ONLY

    Looking for formula to extract values/text ONLY from column I. Desired outcome is in column J. See attached sample.
    Attached Files Attached Files

  2. #2
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Extract Values/Text ONLY

    You could use this in J2 and drag down but I think it would be easier to simply construct it from sheet 1 where you already have the individual data fields. Up to you though

    =IFERROR(MID(I2,FIND("-",I2)+1,LEN(I2)+FIND("-",I2)+1-FIND("-",I2,FIND("-",I2)+1)),LEFT(I2,FIND("-",I2)-1))
    Happy with my advice? Click on the * reputation button below

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Extract Values/Text ONLY

    If Here!J2 should extract text from Here!I2,

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

    Fill J2 down as far as needed.

    If there are 2 or more space-dash-space substrings (-) in I2, this returns the text between the 1st and 2nd. If there's only 1 such substring in I2, this returns the text to the left of it. If there's no such string, this returns I2 as-is.

  4. #4
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Extract Values/Text ONLY

    Or try:

    =TRIM(MID(SUBSTITUTE(I2,"-",REPT(" ",250)),1+250*(COUNTIF(I2,"* - * - *")),250))

  5. #5
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract Values/Text ONLY

    Phuocam: Outstanding solution. Thanks a lot.

  6. #6
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract Values/Text ONLY

    hrlngrv: Outstanding solution. Thanks a lot.

  7. #7
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Extract Values/Text ONLY

    Did my solution not work?

  8. #8
    Forum Contributor
    Join Date
    02-06-2013
    Location
    Maryland
    MS-Off Ver
    Excel 2010
    Posts
    1,000

    Re: Extract Values/Text ONLY

    Crooza: Yes, Crooza. Excellent solution. Thanks a lot.

+ 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] Extract values from text
    By keshavtale in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-07-2019, 01:15 AM
  2. Extract unique text values from a filtered list
    By ljerromes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-23-2015, 07:51 PM
  3. [SOLVED] extract unique values & create new text file
    By hluk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2013, 07:34 PM
  4. Extract multiple values from long strings of text
    By Patrick791 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-05-2012, 04:17 AM
  5. extract time-values from variable text
    By WilliamV in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-09-2012, 11:30 AM
  6. Extract Values and percentages from text string
    By Andy748 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-01-2007, 05:56 PM
  7. [SOLVED] Comparing two coloumns text contents & extract different values
    By nabil in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2006, 09:30 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