+ Reply to Thread
Results 1 to 4 of 4

Extract text to left of nth character

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Extract text to left of nth character

    I have a text string in Excel which contains 4 "/". I need the text (including "/'s" that come BEFORE the final "/"

    The text can be of varied length, and is for instance

    /Purchasing/Cambridge/d-division/jB8k4xLu

    I want to only extract

    /Purchasing/Cambridge/d-division

  2. #2
    Forum Contributor
    Join Date
    04-21-2009
    Location
    England
    MS-Off Ver
    Excel 2016
    Posts
    710

    Re: Extract text to left of nth character

    Sorry solved it with =LEFT( A1, FIND( "/", SUBSTITUTE( A1, " ", "|", 4 ) ) - 1 )

  3. #3
    Valued Forum Contributor TheCman81's Avatar
    Join Date
    11-06-2012
    Location
    Edinburgh, UK
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    346

    Re: Extract text to left of nth character

    You can use something like this

    =TRIM(LEFT(SUBSTITUTE(A2,"/",REPT(" ",999),4),999))
    Excel Guru in the making

    <----------If the question has been answered to your satisfication please click the Add Repuation star to the left

  4. #4
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,883

    Re: Extract text to left of nth character

    Try this:
    Formula: copy to clipboard
    =LEFT(A1,MAX(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="/",ROW(INDIRECT("1:"&LEN(A1)))))-1)


    This is an array formula so you have to enter it with Ctrl + Shift + Enter.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

+ 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] Find text left or right of a specific character in a text string
    By Locopete99 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-14-2014, 05:55 AM
  2. Extract text left and right of character part 2
    By stuartm4h in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-01-2013, 07:12 AM
  3. [SOLVED] Extract text left and right of character
    By stuartm4h in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-12-2013, 07:32 AM
  4. Extract Text up to Character
    By finlain in forum Excel General
    Replies: 2
    Last Post: 08-26-2009, 12:13 PM
  5. Replies: 2
    Last Post: 08-19-2008, 07:18 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