+ Reply to Thread
Results 1 to 15 of 15

Handy One Liners

  1. #1
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Handy One Liners

    Below are the ones I had handy; does any one else have any others to add to the list?

    Please Login or Register  to view this content.
    Last edited by Gregor y; 09-11-2012 at 04:08 PM.

  2. #2
    Registered User
    Join Date
    08-27-2012
    Location
    Roma, Italia
    MS-Off Ver
    Excel 2003
    Posts
    60

    Re: Handy One Liners

    I have a few:

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Re: Handy One Liners

    Here's a new one I picked up recently from another user
    Please Login or Register  to view this content.

  4. #4
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Re: Handy One Liners

    improved quote
    Please Login or Register  to view this content.
    this one's not really a one-liner, but nifty none the less:
    Please Login or Register  to view this content.
    and I've been using private one-liners for testing.
    Please Login or Register  to view this content.
    If it'd make you feel better using my answer because of my street cred, then you can go ahead and click Add Reputation below to improve it.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,648

    Re: Handy One Liners

    It would help if you added a note to each post telling us what these handy one-liners actually do.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  6. #6
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Re: Handy One Liners

    The older Posts don't have an edit button available, but here's a quick listing by name:

    For me, these are mostly mapped to buttons on my Excel Ribbon/QAT; and a lot of them are related to data/spreadsheet cleansing. (I guess some of them were even handy enough to be added to vanilla Excel)

    Please Login or Register  to view this content.
    Huh, almost all of them are built in now. Maybe I should post my other multi-liner data cleansing macros to see if they can be added to the cannon too.

  7. #7
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Re: Handy One Liners

    add r/l-trim as a cell function

    Please Login or Register  to view this content.

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: Handy One Liners

    I hope Rick Rothstein sees this thread, as he used to come up with some nifty one-liners in a forum I used to subscribe to.

    Pete

  9. #9
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,231

    Re: Handy One Liners

    Quote Originally Posted by Gregor y View Post
    add r/l-trim as a cell function

    Please Login or Register  to view this content.
    You can do these with regular Excel formulas...

    Left Trim (normally entered)
    ====================================
    =MID(A1,FIND(LEFT(TRIM(A1)),A1),LEN(A1))

    Right Trim (normally entered)
    ====================================
    =LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1)),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),"")))))

    Right Trim (array entered)
    ====================================
    =LEFT(A1,MAX((MID(A1&REPT(" ",99),ROW($1:$99),1)<>" ")*ROW($1:$99)))

  10. #10
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,231

    Re: Handy One Liners

    Quote Originally Posted by Pete_UK View Post
    I hope Rick Rothstein sees this thread
    Sorry for being so late to this thread but I only just saw it while doing a web search for something else (obviously, I missed it the first time around). Better late than never though, right? Here are a few one-liners I've constructed across the years...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 04-02-2022 at 12:44 AM.

  11. #11
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,231

    Re: Handy One Liners

    Hey Pete,

    Were the one-liners I posted the other day those that you had in mind or were you thinking of other old one-liners I may have posted? The real pity is a ton of one-liners I posted to the old Visual Basic and Excel newsgroups (not forums) were lost when Microsoft shutdown their newsgroup servers quite a few years ago now. When those servers were still active, searching for my name brought up literally 25,000+ postings by me and 2,000 to 3,000 of those posts were one-liners (lots of duplicates in them, of course). With very, very few exceptions, they are all gone.

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,613

    Re: Handy One Liners

    Hi Rick,

    It was on the Code Cage Forum where I particularly became aware of your one-line solutions, but sadly that too is now defunct.

    Pete

  13. #13
    Forum Contributor Gregor y's Avatar
    Join Date
    10-24-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2010 32-Bit
    Posts
    226

    Re: Handy One Liners

    Quote Originally Posted by Rick Rothstein View Post
    You can do these with regular Excel formulas...

    Left Trim (normally entered)
    ====================================
    =MID(A1,FIND(LEFT(TRIM(A1)),A1),LEN(A1))

    Right Trim (normally entered)
    ====================================
    =LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1)),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),"")))))

    Right Trim (array entered)
    ====================================
    =LEFT(A1,MAX((MID(A1&REPT(" ",99),ROW($1:$99),1)<>" ")*ROW($1:$99)))
    If I was on 365, I'd be so tempted to try and turn these into Lambda functions.

  14. #14
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,231

    Re: Handy One Liners

    Quote Originally Posted by Pete_UK View Post
    Hi Rick,
    It was on the Code Cage Forum where I particularly became aware of your one-line solutions, but sadly that too is now defunct.
    I used to think that once posted on the web, it would be there forever. That perception changed when Microsoft closed their newsgroup servers oh so many years ago and an unbelievably large number of posting were lost forever. Since then, I have seen many other sites close down, like Code Cage did, also taking with them large amounts of postings containing extremely useful postings that also become lost forever. The real shame is "newbies" to whatever field can no longer benefit from these now lost posting as they, of course, will not show up in any online searches any more.

  15. #15
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010, 2019
    Posts
    2,231

    Re: Handy One Liners

    Quote Originally Posted by Gregor y View Post
    If I was on 365, I'd be so tempted to try and turn these into Lambda functions.
    I have not been able to fool around with Lambdas yet as I am having trouble getting 365 to load on my computer... I install it, it appears to go through the correct procedure but my existing copy of 2019 always remains afterwards, every time. When I can find time, I'll have to contact Microsoft to see if someone can help me fix whatever is blocking the install.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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