+ Reply to Thread
Results 1 to 13 of 13

Text parsing with variable length

  1. #1
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Question Text parsing with variable length

    I'm sure I've just been staring at formulas for far too many hours and the solution is right in front of my face, but I'm just not getting this one work.

    Column M:
    ##/##/#### | Variable Length Text-####

    Example:
    01/06/2014 | Daniel Trimble-4048

    I need to parse out the different parts of Column M.

    In Column R -- "Close Date", I'm successfully using:
    =LEFT(M2,FIND(" | ",M2)-1)

    ...to extract the close date of the donation.

    In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".

    I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.


    The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.

    How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?


    Thanks,
    dt

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text parsing with variable length

    Can you provide more examples?
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Text parsing with variable length

    01/15/2013 | Larry, Curly & Moe LLP-1234
    need to extract Larry, Curly & Moe LLP

    10/27/2003 | Abraham Lincoln-1965
    need to extract Abraham Lincoln

    02/14/2014 | Bond James Bond-0777
    need to extract Bond James Bond

    The MID start will always be position 14, and I need the text from 14 to whatever the end of the text string is, minus the last 5 characters.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Text parsing with variable length

    Try this...

    M2 = 01/06/2014 | Daniel Trimble-4048

    R2: =LEFT(M2,FIND(" | ",M2)-1)

    This formula in S2:

    =SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Text parsing with variable length

    Quote Originally Posted by Tony Valko View Post
    Try this...

    M2 = 01/06/2014 | Daniel Trimble-4048

    R2: =LEFT(M2,FIND(" | ",M2)-1)

    This formula in S2:

    =SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")

    That's netting me a circular warning and a value of 0.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text parsing with variable length

    Try this

    =TRIM(MID(SUBSTITUTE(MID(M2,FIND(" ",M2)+2,255),"-",REPT(" ",255)),1,255))

    M
    N
    2
    01/06/2014 | Daniel Trimble-4048 Daniel Trimble
    3
    01/15/2013 | Larry, Curly & Moe LLP-1234 Larry, Curly & Moe LLP
    4
    10/27/2003 | Abraham Lincoln-1965 Abraham Lincoln
    5
    01/15/2013 | Larry, Curly & Moe LLP-1234 Larry, Curly & Moe LLP

  7. #7
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Text parsing with variable length

    Never mind...minor typo. I got it.

    Can you explain a little bit about what's going on here? I'm not familiar with substitute and wouldn't have thought to try this.

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text parsing with variable length

    Are you asking me or Tony?

  9. #9
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Text parsing with variable length

    Quote Originally Posted by AlKey View Post
    Are you asking me or Tony?

    Sorry, I was asking Tony. But honestly either one of you would be fine as you both used SUBSTITUTE to accomplish this--something I wouldn't have thought of and am not familiar with.

  10. #10
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Text parsing with variable length

    The formula I have provided is using MID function to find position of the first space first space and the position of the "-" and extracts what is in the middle while replacing the rest of the string with empty space

    Thank you for the feedback!

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Text parsing with variable length

    Quote Originally Posted by dtrimble View Post
    I'm not familiar with substitute and wouldn't have thought to try this.
    =SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")

    LEFT(M2,FIND("-",M2)-1) returns 01/06/2014 | Daniel Trimble

    The formula you already have in R2 returns 01/06/2014

    We use the SUBSTITUTE function to "virtually" replace "01/06/2014 | " with nothing:

    =SUBSTITUTE("01/06/2014 | Daniel Trimble","01/06/2014 | ","")

    In English:

    In this string: 01/06/2014 | Daniel Trimble

    Replace this portion: 01/06/2014 |

    With this: "" (nothing)

  12. #12
    Registered User
    Join Date
    05-25-2011
    Location
    California, USA
    MS-Off Ver
    Office 2016 on Mac and Windows
    Posts
    57

    Re: Text parsing with variable length

    Quote Originally Posted by Tony Valko View Post
    =SUBSTITUTE(LEFT(M2,FIND("-",M2)-1),R2&" | ","")

    LEFT(M2,FIND("-",M2)-1) returns 01/06/2014 | Daniel Trimble

    The formula you already have in R2 returns 01/06/2014

    We use the SUBSTITUTE function to "virtually" replace "01/06/2014 | " with nothing:

    =SUBSTITUTE("01/06/2014 | Daniel Trimble","01/06/2014 | ","")

    In English:

    In this string: 01/06/2014 | Daniel Trimble

    Replace this portion: 01/06/2014 |

    With this: "" (nothing)
    Intriguing approach. I can see this being very useful in the future. Thanks very much for the explanation.

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Text parsing with variable length

    You're welcome. Thanks for the feedback!


    If your question has been solved please mark the thread as being solved.

    In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

+ 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] Extracting Text from a string of Text & Digits of variable length
    By hastex in forum Excel General
    Replies: 6
    Last Post: 06-06-2012, 09:11 AM
  2. Replies: 6
    Last Post: 03-08-2012, 10:35 AM
  3. Extract variable length of text from cell
    By stonegwene in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-12-2011, 03:38 AM
  4. separating variable length text and numbers
    By jhmoxl in forum Excel General
    Replies: 3
    Last Post: 08-19-2010, 11:09 AM
  5. EXTRACT TEXT FROM TEXT STRING:The names are of variable length
    By carricka in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-08-2005, 06:05 AM

Tags for this Thread

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