+ Reply to Thread
Results 1 to 4 of 4

Text Extraction - Right to Left

  1. #1
    Registered User
    Join Date
    02-26-2008
    Posts
    3

    Text Extraction - Right to Left

    Hi all,
    I want to extract text start at last space (" ") and working to the right in a text string. i.e. strip off everything from the last space in the text string. Since there can be multiple spaces in the text string I was working on finding the last space and working to the right. Sample of the text are:

    @Risk 5
    504 Administrator
    731_SGL-JWalk Client 3_3-2KXP_Ins
    7500FastSystem_1.4.0.25(1)Eng
    8200AnalysisSW_4.0(1)Eng
    AB Commander Business License 7.1***************************
    ABS 5.0

    would yield:
    @Risk
    504
    731_SGL-JWalk Client 3_3-2KXP_Ins
    7500FastSystem_1.4.0.25(1)Eng
    8200AnalysisSW_4.0(1)Eng
    AB Commander Business License
    ABS

    If there is no space in the string I ignore it.

    Thanks

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Text Extraction - Right to Left

    try:

    =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1)
    If you liked my solution, please click on the Star -- to add to my reputation

    If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Text Extraction - Right to Left

    tknutsen,

    This will find the right-most space (if a space is present) and return everything to the left of that space:
    =IF(ISNUMBER(FIND(" ",A1)),TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),"")),A1)


    @dgagnon, Your formula finds the left-most space and returns everything to the left of that.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Text Extraction - Right to Left

    Quote Originally Posted by tigeravatar View Post
    @dgagnon, Your formula finds the left-most space and returns everything to the left of that.
    based on his example i misunderstood the ask, the formula provided by tigeravatar should work for what you are trying to do.

  5. #5
    Registered User
    Join Date
    02-26-2008
    Posts
    3

    Thumbs up Re: Text Extraction - Right to Left

    Thanks tigeravatar -- worked great.

    How do we close this thread as solved?

    Thanks

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Text Extraction - Right to Left

    Quote Originally Posted by tigeravatar View Post
    =IF(ISNUMBER(FIND(" ",A1)),TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",255)),255)),"")),A1)
    Perhaps not very likely but this might not work correctly if the last "word" is repeated, e.g. if A1 is "dog cat dog" then the formula returns just "cat". Perhaps try this version

    =IF(COUNTIF(A1,"* *"),MID(A1,1,LOOKUP(2^15,FIND(" ",A1,ROW(INDIRECT("1:"&LEN(A1)))))),A1)
    Audere est facere

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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