+ Reply to Thread
Results 1 to 7 of 7

Extract the data after last occurring space in a string ?

  1. 08-13-2015, 07:17 AM #1
    syncguy
    syncguy is offline
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Extract the data after last occurring space in a string ?

    Hi , trying to find a way to extract the data after last occurring space in a string into a new cell. Any help would be much appreciated.

    Sample Workbook:
    Sample for forum-Pull data after last occuring space.xlsx

    Example Pic:
    Screen Shot 2015-08-13 at 4.12.41 AM.png
    Reply With QuoteRegister To Reply
  2. 08-13-2015, 07:26 AM #2
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Extract the data after last occurring space in a string ?

    Hi,

    Try the following formula in B2:

    =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
    Reply With QuoteRegister To Reply
  3. 08-13-2015, 07:32 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,941

    Re: Extract the data after last occurring space in a string ?

    =trim(right(substitute(a2," ",rept(" ",len(a2))),len(a2)))
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 08-13-2015, 08:01 AM #4
    Izadi
    Izadi is offline
    Registered User
    Join Date
    07-17-2015
    Location
    Shiraz,Iran
    MS-Off Ver
    MS 2010
    Posts
    12

    Re: Extract the data after last occurring space in a string ?

    Hi,
    Try the following array formula in B2 by pressing Ctrl+Shift+Enter :

    =RIGHT(TRIM(A2),LEN(TRIM(A2))-MAX((MID(TRIM(A2),ROW(INDIRECT("1:"&LEN(TRIM(A2)))),1)=" ")*(ROW(INDIRECT("1:"&LEN(TRIM(A2)))))))
    Reply With QuoteRegister To Reply
  5. 08-13-2015, 12:47 PM #5
    syncguy
    syncguy is offline
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Extract the data after last occurring space in a string ?

    ...I'll try all of these and report back . Thanks everyone!
    Reply With QuoteRegister To Reply
  6. 08-15-2015, 04:47 AM #6
    syncguy
    syncguy is offline
    Registered User
    Join Date
    02-07-2012
    Location
    LA
    MS-Off Ver
    Excel For Mac 2011
    Posts
    99

    Re: Extract the data after last occurring space in a string ?

    THey all worked perfectly! thanks a lot everyone
    Reply With QuoteRegister To Reply
  7. 08-15-2015, 05:30 AM #7
    cbatrody
    cbatrody is offline
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,136

    Re: Extract the data after last occurring space in a string ?

    You are welcome

    If that takes care of your question, please mark this thread as Solved by selecting Thread Tools --> Mark thread as solved.
    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] How can I trim strings with re-occurring pattern to a defined string
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-29-2015, 01:45 AM
  2. [SOLVED] Extract data appearing after 4th occurrence consistently re-occurring character
    By syncguy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 06-15-2015, 05:52 PM
  3. [SOLVED] Extract data appearing after 4th occurrence consistently re-occurring character
    By syncguy in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-15-2015, 05:42 PM
  4. [SOLVED] Find Most Common Occurring String Based on Lookup Value
    By kolor in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-11-2013, 09:17 PM
  5. How to count occurrence of items occurring both singly or in a string within a cell
    By Supersadie in forum Excel General
    Replies: 5
    Last Post: 08-23-2010, 07:05 AM
  6. Extract text from string after , before a space
    By Dulanic in forum Excel General
    Replies: 1
    Last Post: 05-24-2010, 08:20 AM
  7. Extract data after space
    By lmohaxx in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-16-2008, 09:40 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