+ Reply to Thread
Results 1 to 8 of 8

Delete Characters Before Last Space In Text String [SOLVED]

  1. 02-01-2016, 09:53 PM #1
    alacey425
    alacey425 is offline
    Registered User
    Join Date
    10-29-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2013
    Posts
    33

    Delete Characters Before Last Space In Text String [SOLVED]

    Can anyone provide help to write a function that will delete all text in a string before the last space in the string?

    I have:

    Cleveland CLE
    New Orleans NO
    Utah UTAH

    I need:

    CLE
    NO
    UTAH

    I found a similar thread but this deletes the text after the last space: http://www.ozgrid.com/forum/showthread.php?t=134864
    Last edited by alacey425; 02-01-2016 at 10:14 PM.
    Reply With QuoteRegister To Reply
  2. 02-01-2016, 09:56 PM #2
    jindon
    jindon is offline
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,525

    Re: Delete Characters Before Last Space In Text String

    Is this how you wanted?
    Please Login or Register  to view this content.
    Alter "a" to suite.
    Reply With QuoteRegister To Reply
  3. 02-01-2016, 09:58 PM #3
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Delete Characters Before Last Space In Text String

    If you would like a formula solution try this.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Dave
    Reply With QuoteRegister To Reply
  4. 02-01-2016, 10:03 PM #4
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Delete Characters Before Last Space In Text String

    Here's another.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 02-01-2016, 10:05 PM #5
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,525

    Re: Delete Characters Before Last Space In Text String

    Hi alacey425,

    Try this modified UDF from the link you provided:

    Please Login or Register  to view this content.
    HTH

    Robert
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post
    Reply With QuoteRegister To Reply
  6. 02-01-2016, 10:13 PM #6
    alacey425
    alacey425 is offline
    Registered User
    Join Date
    10-29-2012
    Location
    Miami, FL
    MS-Off Ver
    Excel 2013
    Posts
    33

    Re: Delete Characters Before Last Space In Text String

    Thanks for the fast responses. I went with Trebor's suggestion and modified it a bit to fit my needs. Here is the code:

    Please Login or Register  to view this content.
    Could you explain what the 256 is for?
    Reply With QuoteRegister To Reply
  7. 02-01-2016, 10:39 PM #7
    FlameRetired
    FlameRetired is offline
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,406

    Re: Delete Characters Before Last Space In Text String [SOLVED]

    alacey,

    Glad you found your solution. Thanks for the feedback.
    Reply With QuoteRegister To Reply
  8. 02-01-2016, 11:30 PM #8
    Trebor76
    Trebor76 is offline
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,525

    Re: Delete Characters Before Last Space In Text String [SOLVED]

    Could you explain what the 256 is for?
    That was already on the UDF from OzGrid but it is the maximum width a column can be. It's really an arbitrary number to capture all the characters from the last space with having to calculate it.
    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. delete last 5 characters from string
    By leooconnor in forum Excel General
    Replies: 6
    Last Post: 01-02-2015, 08:50 PM
  2. [SOLVED] Macro to create a new line within a text string if specific characters appear mid-string
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2014, 11:32 AM
  3. Delete everything BEFORE and AFTER a certain string of characters, including string itself
    By edwardjc in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-25-2014, 08:21 AM
  4. [SOLVED] Backspace event text box string formatting not able to delete characters
    By Code Flunkie in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-28-2014, 04:30 PM
  5. adding space to a string after 10 characters
    By copythat in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 12-02-2011, 11:28 AM
  6. Delete Everything Before and After Characters in String
    By jcholla in forum Excel General
    Replies: 4
    Last Post: 12-08-2010, 01:28 PM
  7. erase all space characters into string
    By Maileen in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-16-2005, 06:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1