Copy Meeting Attendees from Outlook

Open the Outlook Meeting Request from your Calendar

Click into the To field (1)

Press CTRL + A to select all the names

Press CTRL + C to copy

Select a cell where the copied names will be pasted – in this example it is A1. The names will be pasted across row 1.

Click the Paste button (1). Excel recognises that what is on the Clipboard is a block of text and so offers the choice of options.

Select Use Text Import Wizard (2)

Ensure that the settings for step 1 of the Text Import Wizard dialog box are as above (these are the default settings therefore you shouldn’t need to change anything).

Click Next

Ensure that the settings for step 2 of the Text Import Wizard dialog box are as above. You will probably need to remove the tick from one or more of the Delimiter check boxes (1) , tick Other and type a semi colon (;) into the delimiter character box (because each entry in the text string is separated by a semi colon).

Click Next (2)

Ensure that the settings for step 3 of the Text Import Wizard dialog box are as above – you shouldn’t need to change anything.

Click Finish.

The copied names are pasted into A1:J1 because there were 10 entries in the Outlook Meeting Request, each entry separated by a semi-colon

Each cell contains the name and email address in the format Mike Thomas (mike.thomas@theexceltrainer.co.uk) – this is how the data was displayed in Outlook.

The requirement from my customer was to display the names down a column. To achieve this, A1:J1 must be copied but instead of using Paste, use the Paste > Transpose command. This switches the data from being across a row to being down a column.

TIP: Transpose can also be used to switch a column of data to a row

Select A1:J1 and select the Copy command

The data is to be pasted, down column A, starting at A3.

Select cell A3

Click the arrow at the bottom of the Paste button (1) and click the Transpose button (2)

Note: In Excel 2007 and Excel 2011 for Mac, when you click the arrow at the bottom of the Paste button, Excel displays a menu rather than a set of icons. Select the Transpose option from the menu.

In the screenshot above, rows 1 and 2 have been deleted so that the block of data starts in A1.

B1:B10 contain formulas that extract the name from each cell in column A

The formula is B1 is: =TRIM(LEFT(A1,FIND("(",A1)-2))

The LEFT function extracts a given number of characters from a text string, starting at the left.

It requires 2 arguments –

1. The cell that contains the string
2. The number of characters to be extracted

For example if A1 contained the string The Excel Trainer and B1 contained =LEFT(A1,5), B1 would display The E which represents the first 5 characters of the cell entry (spaces count as characters).

In our example spreadsheet, we cannot use an actual value to specify how many charatcers to extract because each name contains a different number of characters.

The number of characters to display (the second argument of the LEFT function) is dependent on the position of the opening bracket before the email address.

To find the postion, within the string, of the opening bracket, use the FIND function. It requires 2 arguments –

1. The character to find (which must be in double quotation marks)
2. The cell to look in

There is an optional third argument, which is the starting character. However, if this is omitted, the search begins at the start of the string.

Cell B1 contains the function =FIND("(",A1) which means locate the character position of the first occurrence of the ( in cell A1

In this example, 13 indicates that the ( is the 13th character in cell A1

At this stage, to extract the name from A1, you could, in B1, enter =LEFT(A1,FIND("(",A1))

This means "extract a number of characters from A1, starting at the left. The number of characters to extract is equal to the position of the ( character in A1"

As you can see from the screenshot above, the number returned by the FIND function means that the bracket and the preceding space are included in the string. To exclude these 2 characters, we need to amend the second argument so that the value is always 2 less than the value returned by the position of the (

In B1 you could enter: =LEFT(A1,FIND("(",A1)-2)

This means "extract a number of characters from A1, starting at the left. The number of characters to extract is equal to the position of the ( character in A1 minus 2"

Look closely at the screenshot above. There is a space at the beginning of each cell in B2:B10. This is because in the original string that was copied from Outlook, there was a semi-colon followed by a space between each entry:

Alan Cummin (alan.cummin@theexceltrainer.co.uk); Bobby Stevenson (bobby.stevenson@theexceltrainer.co.uk);

When the text was pasted in (using the Text Import Wizard) the semi-colon character was used as the separator, therefore, everything AFTER the semi-colon up until the next semi-colon is treated as a string to be imported into each cell. There is no space at the beginning of A1 because Alan Cummin was the first item in the imported string.

The TRIM function is used to remove extra spaces from the beginning and end of a string. It requires a single argument – the cell that contains the string to be "trimmed". For example, =TRIM(A1) would remove spaces from the beginning and end of the string in cell A1.

The formula to use in B1 is: =TRIM(LEFT(A1,FIND("(",A1)-2))

This means retrieve a number of characters from A1. the number of characters to retrieve is equal to 2 less than the position of the bracket in A1. If there are any spaces at the start or the end of the resulting string, remove them.