Monday, 7 August 2017

Trimming text strings in Excel

If we are importing data into MS Excel, particularly from text files, we sometimes need to trim our imported text. Luckily there are some surprisingly easy ways to do that, using formulas.

If, as per the example from Extend Office shown below, our text strings have the SAME beginning, we can use Excel's right function. We can easily remove the FIRST four characters from A1 by entering the formula =RIGHT(A1, LEN(A1)-4) in B1, and hitting enter (14 July 2014):

This formula is also easy to amend. If we want to remove more or fewer characters, we simply increase or decrease the trim number. If we want to remove characters from the END of a string, we replace the RIGHT function with the LEFT (and don't ask me why these two formulas appear to be named backwards. I have no idea!).

The use of this function is so simple. It makes editing easy. Thanks, Extend Office, for sharing this!


Sam

No comments :

Post a Comment