+ Reply to Thread
Results 1 to 5 of 5

Handy: Strip out the contents of the rightmost parenthesis

  1. #1
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Handy: Strip out the contents of the rightmost parenthesis

    Here's a big dumb formula I made for stripping out the number at the end of a text string in certain scenarios. Seems like it might be a fairly common scenario, so I thought I'd share.

    Purpose:

    Sometimes I'll get reports where a column contains customer names and numbers in the format "Customer Name (Number)", and I need that Number. That's all fine and dandy and I could just use a MID/FIND combo or text-to-columns on "(" and ")", however sometimes the data is in a "Customer Name (Arbitrary Junk) (Number)", and that ruins everything because it's not consistent and it's annoying. Saving grace: In this scenario, the common factor is the number I need is always in the last set of ()'s

    So, I made a formula, and ended up making it long and complicated because I got tired of manually changing the cell references all the time because I'm lazy - this formula is therefore completely dynamic. Paste this in the cell immediately to the RIGHT of the cell with the string you want to pull a number from. As long as your number isn't in column XFD, it should work just fine.


    Note: This also maintains any leading zeros in the number at the end, if they exist. Also, perhaps contrary to it's appearance, it's pretty quick - just tested on 50k cells, calculation time 1 second on an older Core i5M. Also, if your cell contains a =CHAR(2) it'll give an error [this is unlikely to come up in most scenarios, but if it does just change all the char(2)'s in the formula to anything you know won't ever exist, and it'll work just fine]



    Please Login or Register  to view this content.
    Enjoy
    Last edited by Speshul; 02-27-2017 at 02:57 PM.
    You should hit F5, because chances are I've edited this post at least 5 times.
    Example of Array Formulas
    Quote Originally Posted by Jacc View Post
    Sorry, your description makes no sense. I just made some formula that looks interesting cause I had nothing else to do.
    Click the * below on any post that helped you.

  2. #2
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Handy: Strip out the contents of the rightmost parenthesis

    Wow that's a lot of work to go through just to save yourself a tiny little bit of work.

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2012
    Location
    Barrington, IL
    MS-Off Ver
    Excel 2007/2010
    Posts
    1,211

    Re: Handy: Strip out the contents of the rightmost parenthesis

    Took 20 minutes to make, and saves me about 5 hours per week

  4. #4
    Forum Contributor
    Join Date
    01-05-2017
    Location
    New York
    MS-Off Ver
    Office 2016
    Posts
    206

    Re: Handy: Strip out the contents of the rightmost parenthesis

    Flash fill also works, if you have excel 2013+

  5. #5
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: Handy: Strip out the contents of the rightmost parenthesis

    For those who might be interested, if there is no text after the last closing parenthesis, then this formula will return whatever is inside of the last pair of parentheses...

    =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-1),"(",REPT(" ",300)),300))

    Note: This formula assumes the the text in cell A1 is not longer than 300 characters (if it could be, increase both of those 300s).


    If there could be text after the last closing parenthesis, then this formula will return whatever is inside of the last pair of parentheses...

    =TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),")",REPT(" ",300)),600),300))

    Note: This formula assumes the the text in cell A1 is not longer than 300 characters (if it could be, increase both of those 300s and make the 600 double whatever you change the 300s to).
    Last edited by Rick Rothstein; 08-19-2019 at 10:59 PM.

+ 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. Handy One Liners
    By Gregor y in forum Tips and Tutorials
    Replies: 21
    Last Post: 10-30-2023, 11:57 PM
  2. Anyone have a Jaccard Index Macro handy?
    By rjw524 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-19-2019, 02:36 PM
  3. Replies: 1
    Last Post: 04-10-2016, 11:42 AM
  4. Contents of Rightmost Cell
    By atwy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-01-2015, 09:31 PM
  5. TIP: Handy String Functions
    By jvancraa in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-18-2012, 11:25 AM
  6. Very Handy Indeed
    By ceemo in forum Excel General
    Replies: 4
    Last Post: 01-26-2006, 11:40 AM
  7. Replies: 2
    Last Post: 04-01-2005, 08: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