+ Reply to Thread
Results 1 to 6 of 6

How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

  1. 10-27-2015, 03:27 PM #1
    unducks
    unducks is offline
    Registered User
    Join Date
    10-27-2015
    Location
    New York, NY
    MS-Off Ver
    Mac 2011
    Posts
    1

    How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    I have an assignment in which I need to separate several strings of data into separate columns using only formulas, and I'm struggling to figure out how to do this. I'm not allowed to use the text to columns feature and it's driving me crazy. Here's an example of a couple of strings I've been given:

    AB_football player_tv_93junfkdn

    CD_baseball fan_radio_f9j498jfoimv

    Is there a MID formula that will allow me to select text between those specifics underscores so that I can grab the second ("football player", "baseball fan") and third sets of data ("tv", "radio") with formulas? I've figured out the left and right sections, but the fact that the middle two sections have inconsistent lengths makes the formulas a huge challenge.
    Reply With QuoteRegister To Reply
  2. 10-27-2015, 03:42 PM #2
    etaf
    etaf is online now
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,737

    Re: How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    I'm not allowed to use the text to columns feature and it's driving me crazy.
    is this for a homework question ?
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  3. 10-27-2015, 03:45 PM #3
    CRIMEDOG
    CRIMEDOG is offline
    Valued Forum Contributor
    Join Date
    02-04-2009
    Location
    Texas
    MS-Off Ver
    Excel 2016
    Posts
    665

    Re: How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    Look at Search in the Text Formulas
    Reply With QuoteRegister To Reply
  4. 10-27-2015, 04:40 PM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    Use text to column and use _ as seperator.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  5. 10-27-2015, 05:11 PM #5
    JeteMc
    JeteMc is online now
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,552

    Re: How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    What is your formula for parsing the right text string?
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
    Reply With QuoteRegister To Reply
  6. 10-27-2015, 06:49 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to Parse Underscore-Separated Data Using Formulas (LEFT, MID, RIGHT, etc.)

    Try this...

    Data Range
    A
    B
    C
    1
    2
    AB_football player_tv_93junfkdn
    football player
    tv
    3
    CD_baseball fan_radio_f9j498jfoimv
    baseball fan
    radio


    This formula entered in B2:

    =TRIM(MID(SUBSTITUTE($A2,"_",REPT(" ",100)),COLUMNS($B2:B2)*100,100))

    Copy across to C2 then down as needed.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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 text separated by underscore with formula
    By nur2544 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-11-2015, 12:19 PM
  2. [SOLVED] Separated first string which is separated by dash (-) or underscore (_) or space ( )
    By nur2544 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-03-2014, 10:32 AM
  3. [SOLVED] Sort all sheets alphabetically, values separated by underscore
    By sans in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2012, 03:09 PM
  4. How to parse comma separated values in a cell & generate command
    By sirababu in forum Excel General
    Replies: 6
    Last Post: 07-19-2012, 01:58 PM
  5. Parse Text Separated by ; to next Row
    By prkhan56 in forum Excel General
    Replies: 3
    Last Post: 12-24-2011, 06:14 AM
  6. Parse Strings of Space-Separated Text of Varying Lengths
    By mlexcelhelpforum in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2011, 07:49 AM
  7. Best way to parse colums and rows w/ multiple values separated by comma?
    By Sutukh19 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2009, 04:37 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1