May 30, 2004

Concatenate Concentrate

The CONCATENATE function is used to join text or numbers (known as Strings. It can join up to 30 strings although I suppose you could nest two or more CONCATENATE functions inside one another, but I have more important things to do, such as writing the remainder of this post.

I'll give you 2 examples.

Example 1 (Joining 2 names together)
In range B3:B12 I have a list of first names and in range C3:C12 I have a list of last names. Concatenating makes joining these names together a snap. In cell D3 I enter =CONCATENATE(B3," ",C3) and drag down to cell D12 where the formula will become =CONCATENATE(B12," ",C12)

Note the apostrophe marks, they are used to include spaces or text. Cell references (such as B3) or numbers do not use them. (I could also ampersands to concatenate as in =B3&" "&C3 but I've found after getting used to spelling CONCATENATE, it's easier for me to enter several strings into a formula quickly)

Example 2 (Making an email address)
To do this I am going to use a combination of 3 formulas, CONCATENATE, LOWER and LEFT. Let's start with LEFT.

It seems to me that many email addresses use the first letter of the first name and then all of the last name combined before the @ mark. To get the first letter of Tom in cell B3 I use =LEFT(B3,1). For more letters I can just increase the number after B3, these letters will always be read from the left side, hence the name LEFT

Another simple function, it is used to convert all text to Lower Case so that TSmith will appear as tsmith. (The UPPER function is used for Upper Case)

I should say that I don't really need to use this function as either upper and lower case is still okay for email addresses. I just want to make it easier to recognize an email address as opposed to normal text.

Refer to the above. Now lets look at the entire formula,


This will give me tsmith@andrewsexceltips.com (you'll need to use Hyperlinks to make them 'clickable' email addresses, use Ctrl and K to bring forth the dialog box)

Now that we have a list of email addresses, let's make a directory using a Combo Box. Right click any toolbar and then tick the Forms toolbar.

Drag the Combo Box to your preferred location and then right click it to select the Format Control dialog box.

Your Input Range will be D3:D12. Push the little arrow button on the right side and drag across these cells, the $ marks will appear automatically to make this an 'absolute' reference (look at the Help files to understand if you are not sure what this means)

Next up is your Cell Link. I usually hide these under my Combo Boxes so they don't stand out. You can see that I am using cell D14.

Finally we have Drop Down Lines, I've increased to 10 from a default number of 8 so that my entire list of email addresses can be seen.

When you use the Combo Box, the Cell Link will change. If you select the fourth name from the top (Jane Williams), the value in cell D14 will become 4 (1 for first, 2 for second, 3 for third, etc)

All I need is some way to reference the actual addresses. I've chosen the INDEX function to do this.


The range F3:F12 contains the email addresses, D14 is the Cell Link that will give me the vertical (row) reference as above and 1 is all that is needed for the horizontal reference as we are just dealing with one column.

Simple once you know how.

Comments: Post a Comment

This page is powered by Blogger. Isn't yours?