A neat excel tip

As an account administrator, I often need to take chunks of information from excel cells and merge them with other cells. For example, if I receive a spreadsheet with a few hundred first names and last names and need to create users based on first initial last name, I could spend a few hours cuting and pasting the first initial to the last name. Or, I can use a few fairly simple formulas to shorten the process to a few seconds. Let's use the following as an example:

The first step is to use the CONCATENATE argument to combine cells A and B. This formula is an excelent way to combine cells of almost any type. In this example I have placed =CONCATENATE(A2,B2) into cell C2. This effectively combines the text of cells A and B. For more on this formula please follow this link: //office.microsoft.com/en-us/help/HP100625621033.aspx

Once you have combined your first name and last name you will want to add the LEN function to a macro. You will want to select Tools --> Macro --> Record new macro. Immediately stop the recording. This will leave you with a "blank" macro. Again select Tools --> Macro --> Macros. Select the macro you just recorded and click edit.

Paste the following into the module that opens:

Sub <name of macro>()
'
' <name> Macro
' Macro recorded 5/27/2008 by <name>
' Keyboard Shortcut: Ctrl+<Letter of your choosing>

'
Dim cell As Range

For Each cell In Range("A1:A600")
If Len(cell) > 1 Then
cell = Right(cell, Len(cell) - 1)
End If
Next cell

End Sub

Close the editor, place your cursor in Cell D2 and run the macro. The result will be the cell below:

As you can see, the r characrter has been stripped from the First Name and has been properly concatenated to create a first initial last name user ID.

So let's break down the macro a bit (undelined, italic and red to define what each part of the macro does):

Dim cell As Range (selects the cell range as defined below)

For Each cell In Range("A1:A600") (defines the range)
If Len(cell) > 1 Then (states that if the length of the cell is greater than 1)
cell = Right(cell, Len(cell) - 1) (command removes all but one character starting from the right)

And that is how easy it is to remove portions of a cell! For more info on the Len function visit:

//office.microsoft.com/en-us/excel/HP100625691033.aspx