How do you reverse a name in Excel ?

This tip is very useful if you have a list of names in "Surname FirstName" format and you want to reverse that quickly.

Well, in Excel, there are a set of useful string manipulation functions you can use for this: LEN, FIND, MID, LEFT, RIGHT

• LEN - Returns the number of characters in a value
• FIND - Returns the position of a value in another value
• MID - Extracts a value based on a starting position and length
• LEFT - takes the X left most characters from a value
• RIGHT - takes the X right most characters from a value ... where X is a number With the cell containing the name in B3.

First, we find the space.

=FIND(" ",B3,1)

Once we know the position of the space, we can reconstruct the name to reverse it by taking everything to the right of the space and adding everything to the left.

Extract Second Name

=LEFT(B3,FIND(" ",B3,1))

Extract First Name

=RIGHT(B3,LEN(B3)-FIND(" ",B3,1))

Here's how it looks in Excel: Download sheet to practise how to reverse a name in excel

Video Training on how to reverse a name:

How do you find out whether a cell contains a certain string ?

- In VBA, it's easy.. use the INSTR function, and test whether this is greater than 0.

In Excel, you need to use both the LEN and the SUBSTITUTE functions.

=LEN(SUBSTITUTE(StringCell,StringToLookForCell,"",1))<>LEN(StringToLookForCell)

Let's see how this works.

- In plain English, all this is saying is:

• If I replace a string in another string with nothing.. is the length of the new string the same.
• If YES, then nothing was replaced, and the first string is not within the second.
• If NO, then something was replaced, and the first string IS within the second.

Here's our data:

Download sheet to practise finding out whether a cell contains a string

Training Video on whether cell contains a string:

Here’s an Excel tip on the LEN function.

Generally speaking, LEN returns the amount of characters that a cell displays.

However, lets look at a few examples and exceptions:

1. Normal text:

Here, the text “Nick” has 4 characters, so LEN returns 4. Nothing complicated there

2. Date Formula:

We have entered =TODAY() as the date formula.

The LEN function returns 5. But “16-Feb-09” has 9 characters I hear you say.

What’s happening ?

Well, for dates, Excel actually stores them as numbers so “16-Feb-09” is actually 39860, and the LEN of that is 5.

3. NOW() formatted as a date:

Here, the NOW() function is used, and LEN returns 16. This again reflects how NOW() is represented in Excel. NOW() is also actually a number: 39860.4762944444, and despite being formatted as a date, the internal representation remains the same.

4. £4,000.00

Excel looks at this as a number, and returns 4 – the 4 digits of 4000.

5. “ £699.00” 

Here, I have pasted a number from my internet bank statement. Note, LEN is returning 8 when you might expect 3. The trouble here is that when you copy and paste data from the web, it often contains extra characters. These need to be removed for Excel to understand the numbers. Here, the LEN function thinks “ £699.00” is a string, and counts all the characters including the one at the beginning. NOTE: LEN can be used to quickly analyse data to eyeball if there are any problems with it like extra spaces or odd characters as it will return a larger number than expected.

6. “=Nick”

“=Nick” returns “Nick”, and that has 4 characters.

7. “=1/0” - an error

Errors are not handled well with the LEN, and it just returns the same error.

8. ="Nick"="Excel Expert"

Well, unfortunately for me, Excel returns FALSE... this has 5 characters.

Download a spreadsheet to practice the LEN function in Excel

Training Video - LEN function

This is one of the most useful Excel tips that you can use in emails or presentations– How to Capture a Screen Shot of your Excel.

There is a quick and easy way of capturing an Excel screen shot without buying commercial software.

Step1: Launch PAINT by going: Start => All Programs => Accessories => Paint

Step2: Go back to Excel, and press ALT and PRTSC ... on my keyboard, the PRTSC button is to the right of F12. This takes a copy of your Excel window.

NOTE – if you press JUST the PRTSC button, it will take a copy of your entire screen.

Step3: Select Paint, and press CTRL + V

Step4: Now you can hone in on interesting part that you actually wanted to capture. Press the square in paint:

Step4: Drag it around the interesting part of your screen shot.

Step5: Press CTRL and C to copy this to the clipboard.

Step6: Press CTRL and N to create a new picture in paint, and press CTRL + V to paste back into paint

From here you can save this as a JPEG or a bitmap or whatever you want, and insert into a mail, or powerpoint.

NOTE: If you don’t actually want to save the screen shot, you can paste directly into outlook after Step5

Training Video - Capture Screen Shot