# Handy One Liners

1. ## 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.``

2. ## Re: Handy One Liners

I have a few:

``Please Login or Register  to view this content.``

3. ## 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. ## 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.``

5. ## Re: Handy One Liners

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

6. ## 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. ## Re: Handy One Liners

add r/l-trim as a cell function

``Please Login or Register  to view this content.``

8. ## 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. ## Re: Handy One Liners

Originally Posted by Gregor y
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. ## Re: Handy One Liners

Originally Posted by Pete_UK
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.``

11. ## 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. ## 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. ## Re: Handy One Liners

Originally Posted by Rick Rothstein
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. ## Re: Handy One Liners

Originally Posted by Pete_UK
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. ## Re: Handy One Liners

Originally Posted by Gregor y
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.

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

#### 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