Two different (but related) tricks for working in Excel

I work with Excel every day. I don’t do a whole lot of crazy stuff with it. It’s generally mostly cut and paste type stuff.

Excel...erate your anal-retentivenessOkay… maybe this is pushing it a touch.

But there are THREE functions that I cannot do without and highly recommend others learn – especially if you do lists.

ONE:

CONCATENATE

This allows you to combine two fields of data into one… like First and Last names.  Assuming your first names are in column A and lastnames are in column B, in column C you will do the following

FORMULA:  =concatenate(A1,” “,B1)

After you do that, it will show “Firstname Lastname”. Click and drag the bottom right corner of that cell. The rest of the names will populate. Now, right-click, COPY. Now, right-click again, PASTE SPECIAL. You will get a pop-up, click VALUES and then OK. You’re column will now have all the names ‘concatenated’.

You’re welcome. 

If you need the values to be in the format of Lastname, First – you can do this just as easily. Assuming column A are your last names and column B your first, you can use the formula  =concatenate(A1,”, “,B1). Make sure you have that middle bit as comma-space between the quotes. Alternatively, you need Lastname, First and column A is first name, column B is lastname, then you would do  =concatenate(B1,”, “,A1) [obviously]

TWO:

“REVERSE CONCATENATE”
aka “LEFT” and “RIGHT”

Assuming that you have a list of names in Column A, what you’re going to do is in column B/C use the following formulaes:

Column B:  =LEFT(A1,FIND(” “,A1))
This will put all of your first names into column B. As with concatenate, you will need to drag the bottom corner down, copy and paste special (values) for this to work.

Column C: =RIGHT(A1,LEN(A1)-FIND(” “,A1))
This will put the lastnames into column C. Once again… drag, copy, paste special and TADAA!!

You can thank me in the comment section below.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s