# Off Topic > Tips and Tutorials >  >  Handy One Liners

## Gregor y

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

----------


## mexindian

I have a few:




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

----------


## Gregor y

Here's a new one I picked up recently from another user



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

----------


## Gregor y

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

----------


## AliGW

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

----------


## Gregor y

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.

----------


## Gregor y

add r/l-trim as a cell function




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

----------


## Pete_UK

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

----------


## Rick Rothstein

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

----------


## Rick Rothstein

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

----------


## Rick Rothstein

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.

----------


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

Pete

----------


## Gregor y

> 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)),"")))))
> ...



If I was on 365, I'd be so tempted to try and turn these into Lambda functions.

----------


## Rick Rothstein

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

----------


## Rick Rothstein

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

----------


## fatkinglet

cool, thank you

----------

