We’re going to show you how to quickly use keyboard shortcuts to align text both vertically and horizontally within cells in Microsoft Excel (the screenshots used are in Excel 2010, but the relevant sections of the Ribbon and how keyboard shortcuts work with them are exactly the same as in Excel 2007).
You may have noticed that if you press the Alt key (Alt stands for Alternate) lots of letters appear over the Ribbon (the standard toolbar at the top of Excel). Here’s what it looks like:
Our text alignment shortcuts are in the Home tab of the Ribbon, so after pressing Alt we press H:
Once you’ve pressed H you’ll see a whole new batch of letters appear above parts of the Home tab (if you weren’t on the Home tab to start with it will appear). In this example we’re going to Left Align, so you’ll now need to press AL:
And that’s it! You’ve learned to left align by pressing Alt HAL.
All the other text alignment shortcuts follow the same pattern – they start Alt HA and then the letter for where you want to align.
I will leave you with a quick summary of all of the basic alignment keyboard shortcuts, may they serve you well:
Keyboard Shortcuts to Align Text Top, Middle, Bottom
These four functions are your essentials for manipulating text in Excel. Let’s take a closer look…
As the name suggests, LEFT snips you a section of text starting from the left hand side. Our example in the picture above shows there are two parts; first you state which text you’re using, in this case the contents of cell B5; second you specify how many letters you want, in this case we chose 3 returning “XLC”.
RIGHT does exactly the same as LEFT but – yep you guessed it! – starting from the right hand side. So our example shows us taking the last 5 letters from cell B5 giving “Libre”.
MID takes things up a notch. It’s going to give us a number of characters from the middle of a text string, but we also need to tell it where to start. So the example above looks 3 characters along in B5, then gives you the next 3 characters “Cal”. If you changed it to =MID(B5,3,4) it would return “Cali”, whereas making it =MID(B5,4,3) would give “ali”.
FIND checks for one text string within another one (e.g. here we look for “Libre” within “XLCalibre”) and tells us how many characters along it appears (in this case 5). You can tell it how many characters in to start (start_num), but don’t worry about that for now, you can leave it out if you want. There are two good uses for FIND that spring to mind:
1. Is the text there? Say you want to check whether someone has used a company e-mail address or not. When the e-mail address is in cell A1, you could try this:
If our friend Al is using his XLCalibre e-mail address, email@example.com, this formula will return 3, as the text string “@xlcalibre.com” starts from the third character. If it can’t find the string, it will return a #VALUE! error.
2. Combine FIND with other functions to manipulate text.
So we have our list of e-mail addresses starting in A1, and we know they will all be in the format firstname.lastname@example.org. We want to get all of the first names. We could nest the FIND in a LEFT function like this:
For our example of “email@example.com” this will return “al” Can you see how it works? If you omit the -1 at the end it gives “al@”.
You could go further and nest this in a PROPER function to get the capitalisation right:
The above picture gives a very quick lesson in four different functions. Here’s what they do:
Remember proper nouns from English class? Names of things, you know, stuff that deserves to start with a capital letter. This gives those words what they deserve. It’s particularly useful if you have a long list of names or address that have been entered in different ways, with all capitals, no capitals, or just plain shoddy capitalisation. This makes the first letter of each word a capital, and the rest lower case (of course it doesn’t work on words like XLCalibre (or McDonalds) where you need more than one capital).
This makes all letters in the specified text upper case. It’s that simple.
Guess what this does – it makes it all lower case.
Just to throw you off guard, this tells you how many characters the text contains (LEN stands for length). XLCalibre is 9 characters long. Why is this useful? Well, the truth is on its not always that useful, but when combined with other functions it can be mighty handy. Trust me, if you’re going to learn more Excel functions you will want this in your repertoire.
So there you have it, an introduction to four text functions within the space of 5 minutes.
Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):
Sorting by Last Name
I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.
The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.
I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.
My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.
I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.
Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to firstname.lastname@example.org.